Sum of values each hour at half past (that hour) I need to aggregate the "sum" of values of a table with TS, Value columns of each half past hour (of 60 minutes), i.e. 00:30, 01:30, 02:30, etc

Sample data:

Val TS
1   2019-08-12 00:00:00.013
3   2019-08-12 00:10:00.013
2   2019-08-12 00:20:00.013
2   2019-08-12 00:30:00.013
0   2019-08-12 00:40:00.013
0   2019-08-12 00:50:00.013
1   2019-08-12 01:00:00.013
7   2019-08-12 01:10:00.013
0   2019-08-12 01:20:00.013
1   2019-08-12 01:30:00.013
2   2019-08-12 01:40:00.013
0   2019-08-12 01:50:00.013
2   2019-08-12 02:00:00.013
0   2019-08-12 02:10:00.013
0   2019-08-12 02:20:00.013
0   2019-08-12 02:30:00.013
1   2019-08-12 02:40:00.013
0   2019-08-12 02:50:00.013
4   2019-08-12 03:00:00.013
3   2019-08-12 03:20:00.013
1   2019-08-12 03:30:00.013



We should get this:

Val TS
10  2019-08-12 00:30:00
5   2019-08-12 01:30:00
8   2019-08-12 02:30:00


Can someone advise on this?

Thank you,
Given the data:

CREATE TABLE #Demo 
(
    Val integer NOT NULL, 
    TS datetime2(3) NOT NULL
);

INSERT #Demo 
    (Val, TS)
VALUES
(1, '2019-08-12 00:00:00.013'),
(3, '2019-08-12 00:10:00.013'),
(2, '2019-08-12 00:20:00.013'),
(2, '2019-08-12 00:30:00.013'),
(0, '2019-08-12 00:40:00.013'),
(0, '2019-08-12 00:50:00.013'),
(1, '2019-08-12 01:00:00.013'),
(7, '2019-08-12 01:10:00.013'),
(0, '2019-08-12 01:20:00.013'),
(1, '2019-08-12 01:30:00.013'),
(2, '2019-08-12 01:40:00.013'),
(0, '2019-08-12 01:50:00.013'),
(2, '2019-08-12 02:00:00.013'),
(0, '2019-08-12 02:10:00.013'),
(0, '2019-08-12 02:20:00.013'),
(0, '2019-08-12 02:30:00.013'),
(1, '2019-08-12 02:40:00.013'),
(0, '2019-08-12 02:50:00.013'),
(4, '2019-08-12 03:00:00.013'),
(3, '2019-08-12 03:20:00.013'),
(1, '2019-08-12 03:30:00.013');


One approach is:


Calculate the number of minutes from a fixed date (with no time component)
Add 30 minutes to each time to align half-hour slots to hours
Round to the hour (divide then multiply minutes by 60 using integer math)
Subtract the 30 minutes
Add the minutes to the same fixed date


This is shown in the code below:

SELECT
    TS = CONVERT(char(19), Timeslot.TS, 120),
    Val = SUM(D.Val)
FROM #Demo AS D
CROSS JOIN (VALUES(CONVERT(datetime2(3), '20190101', 112))) AS Base(FixedDate)
CROSS APPLY(VALUES((DATEDIFF(MINUTE, Base.FixedDate, D.TS) + 30) / 60 * 60)) AS Slot(InMins)
CROSS APPLY(VALUES(DATEADD(MINUTE, Slot.InMins - 30, Base.FixedDate))) AS Timeslot(TS)
GROUP BY
    Timeslot.TS
ORDER BY
    Timeslot.TS;


Output:

╔═════════════════════╦═════╗
║         TS          ║ Val ║
╠═════════════════════╬═════╣
║ 2019-08-11 23:30:00 ║   6 ║
║ 2019-08-12 00:30:00 ║  10 ║
║ 2019-08-12 01:30:00 ║   5 ║
║ 2019-08-12 02:30:00 ║   8 ║
║ 2019-08-12 03:30:00 ║   1 ║
╚═════════════════════╩═════╝


db&lt;>fiddle demo

This produces a couple of extra periods compared with the output shown in the question, but it nevertheless looks correct to me.



If you prefer the (somewhat slow) FORMAT function, the following is equivalent:

SELECT
    TS = Slot.TS,
    Val = SUM(D.Val)
FROM #Demo AS D
CROSS APPLY
(
    VALUES
    (
        -- Remove 30 minutes
        DATEADD(MINUTE, -30, 
            CONVERT(datetime2(3),
                FORMAT(
                    -- Add 30 minutes
                    DATEADD(MINUTE, 30, D.TS), 
                    -- Truncate to hour
                    N'yyyy-MM-dd HH:00:00', 
                    N'en-us'
                ), 120
            )
        )
    )
) AS Slot (TS)
GROUP BY
    Slot.TS
ORDER BY
    Slot.TS;