Grouping records based on intervals of time I have a table with the following schema, and I need to define a query that can group data based on intervals of time (Ex. records per minute) and then provide the sum of the changes to the SnapShotValue since the previous group. At present, the SnapShotValue always increments so I only need the sum of differences. Can anyone help with a SQL Server T-SQL query that might do this? I am open to changing the schema, but this is what I currently have.

Schema

CaptureTime   (datetime)
SnapShotValue (int)


Sample data

1 Jan 2012 00:00:00,   100
1 Jan 2012 00:00:30,   125
1 Jan 2012 00:01:00,   200
1 Jan 2012 00:01:30,   300
1 Jan 2012 00:02:15,   400
1 Jan 2012 00:02:30,   425
1 Jan 2012 00:02:59,   500


Desired Query Result

1 Jan 2012 00:01:00,   225 -- Sum of all captured data changes up to the 1 minute mark
1 Jan 2012 00:02:00,   500 -- Sum of all captured data changes up to the 2 minute mark
1 Jan 2012 00:03:00,   125 -- Sum of all captured data changes up to the 3 minute mark
select dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0),
       sum(SnapShotValue)
from YourTable
group by dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0)


SE-Data

datediff(minute, 0, CaptureTime) gives you the number of minutes since 1900-01-01T00:00:00.   

dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0) adds the number of minutes since 1900-01-01T00:00:00 to 1900-01-01T00:00:00 ending up with a datetime with only minutes. 

The 1+ is there because you wanted the next minute.

To do the same with a 5 minute interval you need to do some calculations. Divide the minutes with 5 and multiply with 5 gives you the minutes rounded down to a 5 minute precision. This works because the result of an integer division in SQL Server is an integer.

dateadd(minute, 5 + (datediff(minute, 0, CaptureTime) / 5) * 5, 0)