CODEWITHSUNDEEP
sqlsql-servergroup-by
devSuper
devSuper

Reputation: 395

Sql Server 2012 - Group data by varying timeslots

I have some data to analyze which is at half hour granularity, but would like to group it by 2, 3, 6, 12 hour and 2 days and 1 week to make some more meaningful comparisons. 

|DateTime | Value |
|01 Jan 2013 00:00 | 1 |
|01 Jan 2013 00:30 | 1 |
|01 Jan 2013 01:00 | 1 |
|01 Jan 2013 01:30 | 1 |
|01 Jan 2013 02:00 | 2 |
|01 Jan 2013 02:30 | 2 |
|01 Jan 2013 03:00 | 2 |
|01 Jan 2013 03:30 | 2 |

Eg. 2 hour grouped result will be

|DateTime | Value |
|01 Jan 2013 00:00 | 4 |
|01 Jan 2013 02:00 | 8 |

To get the 2 hourly grouped result, I thought of this code - 

CASE
WHEN DatePart(HOUR,DateTime)%2 = 0 THEN
    CAST(CAST(DatePart(HOUR,DateTime) AS varchar) + '':00'' AS TIME)
ELSE
    CAST(CAST(DATEPART(HOUR,DateTime) As Int) - 1 AS varchar) + '':00'' END Time

This seems to work alright, but I cant think of using this to generalize to 3, 6, 12 hours. I can for 6, 12 hours just use case statements and achieve result but is there any way to generalize so that I can achieve 2,3,6,12 hour granularity and also 2 days and a week level granularity? By generalize, I mean I could pass on a variable with desired granularity to the same query rather than having to constitute different queries with different case statements.

Is this possible? Please provide some pointers.

Thanks a lot!

Upvotes: 1

Views: 249

Answers (1)

Laurence
Laurence

Reputation: 10976

I think you can use

Declare @Resolution int = 3 -- resolution in hours

Select
    DateAdd(Hour, 
        DateDiff(Hour, 0, datetime) / @Resolution * @Resolution, -- integer arithmetic
        0) as bucket,
    Sum(values)
From
    table
Group By
    DateAdd(Hour, 
        DateDiff(Hour, 0, datetime) / @Resolution * @Resolution, -- integer arithmetic
        0)
Order By
    bucket

This calculates the number of hours since a known fixed date, rounds down to the resolution size you're interested in, then adds them back on to the fixed date.

It will miss buckets out, though if you have no data in them

Example Fiddle

Upvotes: 1

Related Questions