I’m trying to write a query that will break out what percentage of time is utilized within a hour given a time range.
Sample data:
x
declare @date table(id int, Start_time time, End_time time)
Insert Into @date(id, Start_time, End_time) values
(0, '09:15', '17:15')
, (1, '10:45', '16:30')
, (2, '08:05', '17:45')
, (3, '07:00', '15:00')
, (4, '07:30', '8:30')
Looking to get output like this:
Any help would greatly be appreciated.
Advertisement
Answer
By using an ad-hoc tally table to create every minute of the day. This assumes you are processing to the minute. Then it becomes a small matter of the conditional aggregation.
Example
;with cte as (
Select A.ID
,Hrs = datepart(hour,T)
,Cnt = convert(decimal(10,2),sum(1.0) / 60)
From @date A
Join (
Select Top (1440) T=dateadd(MINUTE,-1+Row_Number() Over (Order By (Select NULL)),0) From master..spt_values n1,master..spt_values n2
) B on T >=convert(datetime,Start_Time) and t<convert(datetime,End_Time)
Group By A.ID,datepart(hour,T)
)
Select ID
,h3 = sum(case when Hrs=3 then Cnt else 0 end)
,h4 = sum(case when Hrs=4 then Cnt else 0 end)
,h5 = sum(case when Hrs=5 then Cnt else 0 end)
,h6 = sum(case when Hrs=6 then Cnt else 0 end)
,h7 = sum(case when Hrs=7 then Cnt else 0 end)
,h8 = sum(case when Hrs=8 then Cnt else 0 end)
,h9 = sum(case when Hrs=9 then Cnt else 0 end)
,h10 = sum(case when Hrs=10 then Cnt else 0 end)
,h11 = sum(case when Hrs=11 then Cnt else 0 end)
,h12 = sum(case when Hrs=12 then Cnt else 0 end)
,h13 = sum(case when Hrs=13 then Cnt else 0 end)
,h14 = sum(case when Hrs=14 then Cnt else 0 end)
,h15 = sum(case when Hrs=15 then Cnt else 0 end)
,h16 = sum(case when Hrs=16 then Cnt else 0 end)
,h17 = sum(case when Hrs=17 then Cnt else 0 end)
,h18 = sum(case when Hrs=18 then Cnt else 0 end)
From cte
Group By ID
Returns