CODEWITHSUNDEEP
sqlsql-serverdatetimegroup-by
Matt
Matt

Reputation: 179

SQL SERVER Group Datetimestamps by custom period

I have interval data (Value) each 60 minutes for the last 3 years. The datatype for the timestamp is datetimeoffset(7). The timestamp for each interval represents the end of the time period.

For example, the Value 323.459 at timestamp 2016-12-31 07:00:00.0000000 +01:00 is the period from 2016-12-31 06:00:00.0000000 +01:00 to 2016-12-31 07:00:00.0000000 +01:00. Here is more sample data:

DateTime                             Value
2016-12-31 06:00:00.0000000 +01:00   314.5300000000
2016-12-31 07:00:00.0000000 +01:00   323.4590000000
2016-12-31 08:00:00.0000000 +01:00   314.3110000000
2016-12-31 09:00:00.0000000 +01:00   611.7280000000
2016-12-31 10:00:00.0000000 +01:00   507.1300000000
2016-12-31 11:00:00.0000000 +01:00   533.3160000000
2016-12-31 12:00:00.0000000 +01:00   524.4510000000
2016-12-31 13:00:00.0000000 +01:00   507.0540000000
2016-12-31 14:00:00.0000000 +01:00   498.2340000000
2016-12-31 15:00:00.0000000 +01:00   507.0230000000
2016-12-31 16:00:00.0000000 +01:00   498.1710000000
2016-12-31 17:00:00.0000000 +01:00   506.9680000000
2016-12-31 18:00:00.0000000 +01:00   506.9730000000
2016-12-31 19:00:00.0000000 +01:00   507.0090000000
2016-12-31 20:00:00.0000000 +01:00   498.2780000000
2016-12-31 21:00:00.0000000 +01:00   463.2640000000
2016-12-31 22:00:00.0000000 +01:00   507.0490000000
2016-12-31 23:00:00.0000000 +01:00   454.7290000000
2017-01-01 00:00:00.0000000 +01:00   419.7010000000
2017-01-01 01:00:00.0000000 +01:00   628.9020000000
2017-01-01 02:00:00.0000000 +01:00   541.9210000000
2017-01-01 03:00:00.0000000 +01:00   533.1670000000
2017-01-01 04:00:00.0000000 +01:00   515.5930000000
2017-01-01 05:00:00.0000000 +01:00   454.7160000000
2017-01-01 06:00:00.0000000 +01:00   445.8340000000

I'd like to know the total from each month. So the January 2017 period would use these dates:
2017-01-01 06:00:00.0000000 +01:00 to 2017-02-01 06:00:00.0000000 +01:00

And February 2017 is bounded by these dates:
2017-02-01 06:00:00.0000000 +01:00 to 2017-03-01 06:00:00.0000000 +01:00

Those are examples. I want to know the totals for all the months in my data set. How can I write a query to get totals for the Value column grouped by month?

Upvotes: 1

Views: 72

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

Just subtract 6 hours and do the calculation:

SELECT YEAR(dts_start) AS [Year],
       MONTH(dts_start) AS [Month],
       SUM([Value]) AS [Total]
FROM data CROSS APPLY
     (VALUES (DATEADD(hour, -6, datetimestamp)) as v(dts_start)
GROUP BY YEAR(dts_start), MONTH(dts_start)
ORDER BY [Year], [Month];

Upvotes: 3

Related Questions