# 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)
``````
-
This provided a nice framework for the answer, but I am still having a little trouble understanding how this works. When I tried to change the offset from a 1 minute interval to a 5 minute interval I still get a 1 minute interval with the whole result starting at a different beginning point. This is very likely because I am misunderstanding how this works. Could you provide one more sample showing an interval other then 1 minute? Thanks... – JoeGeeky May 10 '12 at 12:36

I'm a little confused by the 3rd example there, is this supposed to be 1325? Based off the requirements of capturing the sum of the snapshotvalues, the below query should get what you're after.

``````select dateadd(minute,1,convert(varchar(20),capturetime)), sum(snapshotvalue)
from snapshotdata sd