vw_LCUComparison.sql

create view vw_LCUComparison
with schemabinding
as
select		s.[Date], s.[Time], s.[SiteName]
			,e.WebAppCount
			,e.lcus [rule-eval-lcus]
			,n.lcus [new-conn-lcus]
			,a.lcus [active-conn-lcus]
			,b.lcus [bandwidth-lcus]
			,(select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (e.lcus)) as value(v)) as [max-lcus]
			,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (e.lcus)) as value(v)) = e.lcus then 1 else 0 end as [rule-eval-used]
			,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (e.lcus)) as value(v)) = n.lcus then 1 else 0 end as [new-conn-used]
			,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (e.lcus)) as value(v)) = a.lcus then 1 else 0 end as [active-conn-used]
			,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (e.lcus)) as value(v)) = b.lcus then 1 else 0 end as [bandwidth-used]
from			dbo.ProxySiteRequestCounts		s
	inner join	dbo.ProxySiteNewConnections		n	on	s.[Date] = n.[Date] and s.[Time] = n.[Time] and s.[SiteName] = n.[SiteName]
	inner join	dbo.ProxySiteActiveConnections	a	on	s.[Date] = a.[Date] and s.[Time] = a.[Time] and s.[SiteName] = a.[SiteName]
	inner join	dbo.ProxySiteBandwidth			b	on	s.[Date] = b.[Date] and s.[Time] = b.[Time] and s.[SiteName] = b.[SiteName]
	inner join	dbo.ProxySiteRuleEvaluations	e	on	s.[Date] = e.[Date] and s.[Time] = e.[Time] and s.[SiteName] = e.[SiteName]
go