clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 648577 fiddles created (15301 in the last week).

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');
21 rows affected
 hidden batch(es)


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;
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
 hidden batch(es)