0

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,

5

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<>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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.