CODEWITHSUNDEEP
sql-server
beliskna
beliskna

Reputation: 185

Grouping Data by DateTime Range

I am using SQL Server 2012 Express and I would like to take the following data and group it into 24-periods between 22:00 one day and 22:00 the next day. The dates and times could be anything and there could be thousands of records returned over a span of 5 years. The raw data was generated from:

Time BETWEEN '2017-08-01 22:00'AND '2017-08-03 22:00'

The data is below:

Time                       Duration
-----------------------------------
2017-08-01 22:00:22.000      4
2017-08-01 23:00:40.000     18
2017-08-02 05:14:57.000      3
2017-08-02 05:17:47.000      4
2017-08-02 21:18:22.000    156
2017-08-02 23:18:23.000      3
2017-08-03 06:18:35.000     13
2017-08-03 11:21:30.000      9
2017-08-03 13:23:43.000      3
2017-08-03 23:24:02.000      1

The expected results are as below:

StartTime           EndTime            TotalDuration
----------------------------------------------------
2017-08-01 22:00    2017-08-02 22:00    185
2017-08-02 22:00    2017-08-03 22:00     28
2017-08-03 22:00    2017-08-04 22:00      1

Can anyone point me in the right direction?

Thanks.

Upvotes: 1

Views: 48

Answers (1)

Martin Smith
Martin Smith

Reputation: 453910

You can use

SELECT StartTime = DATEADD(HOUR, -2, Date),
       EndTime = DATEADD(HOUR, 22, Date),
       TotalDuration = SUM(Duration)
FROM   YourTable
       CROSS APPLY (VALUES(CAST(CAST(DATEADD(HOUR, 2, Time) AS DATE)AS DATETIME))) V(Date)
GROUP  BY Date

Upvotes: 1

Related Questions