SQL Server – Aggregate data by minute over multiple days

Context

I’m using Microsoft SQL Server 2016.

There is a database table “Raw_data”, that contains the status of a machine, together with it’s starting time. There are several machines and each one writes it’s status to the database multiple times per minute.

To reduce the data volume I’m trying to aggregate the data into 1-Minute chunks to save it for further analysis. Due to a capacity constraint, I want to execute this transition-logic every few minutes (e.g. scheduled SQL Server Agent Job), delete the raw data and just keep the aggregated data.

To simplify the example, let’s assume “Raw_data” looks something like this:

╔════╦════════════╦════════╦═════════════════════╗
║ id ║ fk_machine ║ status ║     created_at      ║
╠════╬════════════╬════════╬═════════════════════╣
║  1 ║       2222 ║      0 ║ 2020-08-19 22:15:00 ║
║  2 ║       2222 ║      3 ║ 2020-08-19 22:15:30 ║
║  3 ║       2222 ║      5 ║ 2020-08-19 23:07:00 ║
║  4 ║       2222 ║      1 ║ 2020-08-20 00:20:00 ║
║  5 ║       2222 ║      0 ║ 2020-08-20 00:45:00 ║
║  6 ║       2222 ║      5 ║ 2020-08-20 02:20:00 ║
╚════╩════════════╩════════╩═════════════════════╝

Also there are database tables “Dim_date” and “Dim_time”, that look something like that:

╔══════════╦══════════════╗
║ datekey  ║ date_iso8601 ║
╠══════════╬══════════════╣
║ 20200101 ║ 2020-01-01   ║
║ 20200102 ║ 2020-01-02   ║
║ ...      ║ ...          ║
║ 20351231 ║ 2035-12-31   ║
╚══════════╩══════════════╝

╔═════════╦══════════╦═════════════════╗
║ timekey ║ time_iso ║ min_lower_bound ║
╠═════════╬══════════╬═════════════════╣
║ 1       ║ 00:00:01 ║ 00:00:00        ║
║ 2       ║ 00:00:02 ║ 00:00:00        ║
║ ...     ║ ...      ║ ...             ║
║ 80345   ║ 08:03:45 ║ 08:03:00        ║
║ ...     ║ ...      ║ ...             ║
║ 134504  ║ 13:45:04 ║ 13:45:00        ║
║ 134505  ║ 14:45:05 ║ 13:45:00        ║
║ ...     ║ ...      ║ ...             ║
║ 235959  ║ 23:59:59 ║ 23:59:59        ║
╚═════════╩══════════╩═════════════════╝

The result should look like this:

╔══════════════╦═════════════════╦════════════╦════════╦═══════════════╗
║ date_iso8601 ║ min_lower_bound ║ fk_machine ║ status ║ total_seconds ║
╠══════════════╬═════════════════╬════════════╬════════╬═══════════════╣
║ 2020-08-19   ║ 22:15:00        ║ 2222       ║ 0      ║ 30            ║
║ 2020-08-19   ║ 20:15:00        ║ 2222       ║ 3      ║ 30            ║
║ 2020-08-19   ║ 20:16:00        ║ 2222       ║ 3      ║ 60            ║
║ 2020-08-19   ║ 20:17:00        ║ 2222       ║ 3      ║ 60            ║
║ ...          ║ ...             ║ ...        ║ ...    ║ ...           ║
║ 2020-08-19   ║ 23:06:00        ║ 2222       ║ 3      ║ 60            ║
║ 2020-08-19   ║ 23:07:00        ║ 2222       ║ 5      ║ 60            ║
║ 2020-08-19   ║ 23:08:00        ║ 2222       ║ 5      ║ 60            ║
║ ...          ║ ...             ║ ...        ║ ...    ║ ...           ║
║ 2020-08-20   ║ 00:19:00        ║ 2222       ║ 5      ║ 60            ║
║ 2020-08-20   ║ 00:20:00        ║ 2222       ║ 1      ║ 60            ║
║ 2020-08-20   ║ 00:21:00        ║ 2222       ║ 1      ║ 60            ║
║ ...          ║ ...             ║ ...        ║ ...    ║ ...           ║
║ 2020-08-20   ║ 00:44:00        ║ 2222       ║ 1      ║ 60            ║
║ 2020-08-20   ║ 00:45:00        ║ 2222       ║ 0      ║ 60            ║
╚══════════════╩═════════════════╩════════════╩════════╩═══════════════╝

---

Attempt

To calculate the duration of each status per minute I used an CTE and LEAD to fetch the starting date and time from the next status in the database table, then joined with the dimension tables and aggregated the result.

WITH CTE_MACHINE_STATES(START_DATEKEY, 
                        START_TIMEKEY, 
                        FK_MACHINE, 
                        END_DATEKEY, 
                        END_TIMEKEY)
     AS (SELECT CAST(CONVERT(CHAR(8), CREATED_AT, 112) AS INT), -- ISO: yyyymmdd
                CONVERT(INT, REPLACE(CONVERT(CHAR(8), READING_TIME, 108), ':', '')), 
                FK_MACHINE, 
                STATUS, 
                CAST(CONVERT(CHAR(8), LEAD(CREATED_AT, 1) OVER(PARTITION BY FK_MACHINE
                ORDER BY CREATED_AT), 112) AS INT),
                CONVERT(INT, REPLACE(CONVERT(CHAR(8), LEAD(CREATED_AT, 1) OVER(PARTITION BY FK_MACHINE
                ORDER BY CREATED_AT), 108), ':', ''))
         FROM RAW_DATA)
     SELECT DATE_ISO8601, 
            MIN_LOWER_BOUND, 
            FK_MACHINE, 
            STATUS, 
            SUM(1) AS TOTAL_SECONDS -- Duration
     FROM CTE_MACHINE_STATES
     CROSS JOIN DIM_DATE
     CROSS JOIN DIM_TIME
     WHERE TIMEKEY >= START_TIMEKEY AND 
           TIMEKEY < END_TIMEKEY AND 
           END_TIMEKEY IS NOT NULL AND -- last entry per machine and status
           DATEKEY BETWEEN START_DATEKEY AND END_DATEKEY
     GROUP BY FK_MACHINE, 
              STATUS, 
              DATE_ISO8610, 
              MIN_LOWER_BOUND
     ORDER BY DATE_ISO8610, 
              MIN_LOWER_BOUND;

The Problem

If the status lasts past midnight it won’t be aggregated correctly. For example the status at id = 3 in “Raw_data” starts at 23:07 and ends on 00:20 the next day. Here, timekey is greater than end_timekey, so the status get’s excluded from the resulting table by the filter TIMEKEY < END_TIMEKEY. I haven’t come up with a solution on how to change the join-condition to include such long-lasting states, but get the expected result.

PS: I already wrote, that normally status-updates are happening every several seconds. Thus, the problem only occurs in edge cases, e.g. if a machine get’s turned off.

---

Solution

Unfortunately I did not receive an answer on how to get the expected result using the date- and time dimension tables. But dnoeth’s approach using a recursive CTE is good, so I went with it:

WITH cte_outer AS (
    SELECT fk_machine,
           status,
           created_at,
           DATEADD(minute, DATEDIFF(minute, '2000', created_at), '2000') AS min_lower_bound, --truncates seconds from start time
           LEAD(created_at) OVER(PARTITION BY fk_machine ORDER BY created_at) AS end_time
    FROM raw_data
),
    cte_recursive AS (
        SELECT fk_machine,
               status,
               min_lower_bound,
               end_time,
               CASE
                 WHEN end_time > DATEADD(minute, 1, min_lower_bound)
                 THEN DATEDIFF(s, created_at, DATEADD(minute, 1, min_lower_bound))
                 ELSE DATEDIFF(s, created_at, end_time)
               END AS total_seconds
        FROM cte_outer

        UNION ALL

        SELECT fk_machine,
               status,
               DATEADD(minute, 1, min_lower_bound), -- next time segment (minute)
               end_time,
               CASE
                 WHEN end_time >= DATEADD(minute, 2, min_lower_bound)
                 THEN 60
                 ELSE DATEDIFF(s, DATEADD(minute, 1, min_lower_bound), end_time)
               END
        FROM cte_recursive
        WHERE end_time > DATEADD(minute, 1, min_lower_bound)
)
SELECT min_lower_bound,
       fk_machine,
       status,
       total_seconds
FROM cte_recursive
ORDER BY  fk_machine, 
          min_lower_bound

Answer

This is a use-case for a recursive CTE, increasing created_at by one minute per recursion:

with cte as 
 (
   select fk_machine
     ,status  
     ,start_minute
     ,end_time
     ,case
        when end_time > dateadd(minute, 1,start_minute)
        then datediff(s, created_at, dateadd(minute, 1,start_minute)) 
        else datediff(s, created_at, end_time )
      end as seconds
   from
    (
      select fk_machine
        ,status
        ,created_at 
        ,dateadd(minute, datediff(minute, 0, created_at), 0) as start_minute
        ,lead(created_at)
         over (PARTITION BY fk_machine
               order by created_at) as end_time
      from tab
    ) as dt
 
   union all
 
   select fk_machine
     ,status
     ,dateadd(minute, 1,start_minute)
     ,end_time
     ,case
        when end_time >= dateadd(minute, 2,start_minute)
        then 60
        else datediff(s, dateadd(minute, 1,start_minute), end_time)
      end
    from cte
    where end_time > dateadd(minute, 1,start_minute)
 )
select * from cte
order by 1,3,4;

See fiddle