Facebook Sign in | Join
Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

Solution to TSQL Challenge 31 - Managing multiple overlapping date intervals By leszek_g

select
	td.deptname Department
	,trt.restypename ResourceType
	,cast(sum(case when SumCounted.ym='2010.01' then SumCounted.sumcn2 * teh.workhours / SumCounted.sumcn1 else 0 end) as decimal(9,2)) Jan
	,cast(sum(case when SumCounted.ym='2010.02' then SumCounted.sumcn2 * teh.workhours / SumCounted.sumcn1 else 0 end) as decimal(9,2)) Feb
	,cast(sum(case when SumCounted.ym='2010.03' then SumCounted.sumcn2 * teh.workhours / SumCounted.sumcn1 else 0 end) as decimal(9,2)) Mar
	,cast(sum(case when SumCounted.ym='2010.01' then SumCounted.sumcn2 * teh.workhours / SumCounted.sumcn1 else 0 end) as decimal(9,2))
		+cast(sum(case when SumCounted.ym='2010.02' then SumCounted.sumcn2 * teh.workhours / SumCounted.sumcn1 else 0 end) as decimal(9,2))
		+cast(sum(case when SumCounted.ym='2010.03' then SumCounted.sumcn2 * teh.workhours / SumCounted.sumcn1 else 0 end) as decimal(9,2)) 'Q1-2010'
from
(
	select 
		Counted.ym
		,Counted.deptid
		,Counted.restypeid
		,Counted.empid
		,sum(Counted.cn1) sumcn1
		,sum(Counted.cn2) sumcn2
	from
	(
		select
			WorkDays.ym
			,WorkDays.deptid
			,WorkDays.restypeid
			,WorkDays.empid
			,count(empid) over (partition by WorkDays.empid,WorkDays.ym) cn1
			,count(empid) over (partition by WorkDays.empid,WorkDays.ym,WorkDays.restypeid,WorkDays.deptid) cn2
		from
		(
			select
				trta.empid
				,trta.restypeid
				,tda.deptid
				,convert(char(7),dateadd(d,N,'2010-01-01'),102) ym
			from tsqlc_Tally l 
			inner join TC31_ResourceTypeAssoc trta
				on dateadd(d,N,'2010-01-01') between trta.resfrom and isnull(trta.resuntil,'2010-04-01')
			inner join TC31_DepartmentAssoc tda 
				on trta.empid = tda.empid and dateadd(d,N,'2010-01-01') between tda.deptfrom and isnull(tda.deptuntil,'2010-04-01')
			left join TC31_Vacation tv
				on tv.empid = tda.empid and dateadd(d,N,'2010-01-01') between tv.vacfrom and tv.vacuntil
			where l.N < 90 and (N+6)%7 > 1 
				and dateadd(d,N,'2010-01-01') not IN (select holdate from TC31_Holiday)
				and tv.empid is null
		) WorkDays
	) Counted
	group by Counted.ym,Counted.empid,Counted.deptid,Counted.restypeid
) SumCounted
join TC31_EstimatedHours teh on teh.empid = SumCounted.empid and convert(char(7),teh.workmonth ,102) = SumCounted.ym
join TC31_Department td on td.deptid = SumCounted.deptid
join TC31_ResourceType trt on trt.restypeid = SumCounted.restypeid
group by td.deptname,trt.restypename
order by td.deptname,trt.restypename

Performance stats of the above solution

Sr# StartTime                 Reads      Writes         CPU Duration 
--- ------------------- ----------- ----------- ----------- -------- 
  1 Sep 23 2010 12:48PM      526091          45        2761    6.653
  2 Sep 23 2010 12:49PM      525317          17        2667    4.834
  3 Sep 23 2010 12:50PM      525319          15        2605    4.592
  4 Sep 23 2010 12:52PM      525319          15        2527    5.460
  5 Sep 23 2010 12:53PM      525319          15        2746    5.270

Copyright © Rivera Informatic Private Ltd.