"Aggregation" by hour when data may be much longer or much shorter I have duration data which looks like this:

deviceid                          StartRange            EndRange            Duration    eventname   value
57a6f2499aaaeb1580f1ca36    2016-08-18 13:37:02.020 2016-08-18 13:38:22.020 80          Engine  2
57a6f2499aaaeb1580f1ca36    2016-08-18 13:38:22.020 2016-08-18 14:14:06.380 2144        Engine  1
57a6f2499aaaeb1580f1ca36    2016-08-18 14:14:06.380 2016-08-18 14:15:26.380 80          Engine  2
57a6f2499aaaeb1580f1ca36    2016-08-18 14:15:26.380 2016-08-18 14:16:06.380 40          Engine  1
57a6f2499aaaeb1580f1ca36    2016-08-18 14:16:06.380 2016-08-19 00:00:00.000 35034       Engine  2
57a6f2499aaaeb1580f1ca36    2015-08-18 15:02:41.840 2015-08-18 15:04:01.840 80          Engine  2
57a6f2499aaaeb1580f1ca36    2015-08-18 15:04:01.840 2015-08-18 15:04:41.840 40          Engine  1
57a6f2499aaaeb1580f1ca36    2015-08-18 15:04:41.840 2016-08-18 13:37:02.020 31617141    Engine  2


(I basically log the data every time a state changes, so for each point in time there can be only 1 value.)

On this data I need to allow users to get the total duration per value on any given range and on any given interval (minutely, hourly, daily... yearly).

Narrowing the range is not a problem, however the interval I can't figure out.

The problem is that 1 row can be turned into X datapoints in that query but how to open the data and then "bucket it" and aggregate it?
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.