"Aggregation" by hour when data may be much longer or much shorter
One idea is to start with a list of hours. Consider a table of numbers and then convert it to a list of hours using dateadd. Like this but with hour instead of days: https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function/1378788#1378788

Then join your table of hours to your point table ON h.thehour &gt; dateadd(hour,-1,r.StartRange) AND h.thehour &lt; r.EndRange

Now you can work out the number of seconds (or however granular you want it) within each hour using datediff with CASE, taking the smaller bounds of the hour and range. Something like:

SUM(datediff(second, 
    CASE WHEN h.thehour &gt; r.StartRange 
         THEN h.thehour
         ELSE r.StartRange
    END,
    CASE WHEN dateadd(hour,1,h.thehour) &lt; r.EndRange
         THEN dateadd(hour,1,h.thehour)
         ELSE r.EndRange
    END
))


You could also just use a table of numbers and consider the first hour is zero, but then you'll need to convert it to hours before doing the aggregate, so it'll be easier to get your hourly blocks first.