SQL – % Breakout by Hour

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:

    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.

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