FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE events_tbl (
  event_at DATETIME,
  id INTEGER,
  event_log_id INTEGER
);

INSERT INTO events_tbl
  (event_at, id, event_log_id)
VALUES
  ('2024-01-17 10:22:20.000', '1', '1'),
  ('2024-01-17 11:40:04.000', '2', '2'),
  ('2024-01-18 11:18:50.000', '3', '3'),
  ('2024-01-18 11:18:51.000', '4', '4'),
  ('2024-01-18 11:18:52.000', '5', '5'),
  ('2024-01-18 11:39:03.000', '6', '6'),
  ('2024-01-18 14:17:48.000', '7', '7'),
  ('2024-01-19 18:01:03.000', '8', '8'),
  ('2024-01-19 20:22:01.000', '9', '9'),
  ('2024-01-19 22:39:03.000', '10', '10'),
  ('2024-01-20 01:01:01.000', '11', '11'),
  ('2024-01-21 00:01:01.000', '12', '12');
 
WITH CTE_EVENTS_BASE AS
  (SELECT A.EVENT_AT,
          A.ID,
          A.EVENT_LOG_ID,
          ROW_NUMBER() OVER (
                             ORDER BY A.EVENT_AT) AS ROW_NUM
   FROM EVENTS_TBL A),
     RECUR_CTE_EVENTS AS
  (SELECT A.EVENT_AT,
          A.ID,
          A.EVENT_LOG_ID,
          A.ROW_NUM,
          CAST(0 AS DECIMAL(22, 5)) AS HOURS_SINCE_LAST_EVENT,
          CAST(0 AS DECIMAL(22, 5)) AS RS_HOURS_SINCE_LAST_EVENT,
   		  1 AS EVENT_COUNT,
   1 AS EVENT_GROUP_NUM
   FROM CTE_EVENTS_BASE A
   WHERE A.ROW_NUM = 1
   UNION ALL SELECT A.EVENT_AT,
                    A.ID,
                    A.EVENT_LOG_ID,
                    A.ROW_NUM,
                    CAST(DATEDIFF(SECOND, B.EVENT_AT, A.EVENT_AT) / 3600.00 AS DECIMAL(22, 5)) AS HOURS_SINCE_LAST_EVENT,
                    CAST(CASE
                        WHEN CAST(DATEDIFF(SECOND, B.EVENT_AT, A.EVENT_AT) / 3600.00 AS DECIMAL(22, 5)) + B.RS_HOURS_SINCE_LAST_EVENT >= 24 THEN 0.00000
                        ELSE CAST(DATEDIFF(SECOND, B.EVENT_AT, A.EVENT_AT) / 3600.00 AS DECIMAL(22, 5)) + B.RS_HOURS_SINCE_LAST_EVENT
                    END AS DECIMAL(22,5)) AS RS_HOURS_LAST_REQUEST,
   					CASE
                        WHEN CAST(DATEDIFF(SECOND, B.EVENT_AT, A.EVENT_AT) / 3600.00 AS DECIMAL(22, 5)) + B.RS_HOURS_SINCE_LAST_EVENT >= 24 THEN 1
                        ELSE b.EVENT_COUNT + 1
                    END AS EVENT_COUNT,
   					CASE
                        WHEN CAST(DATEDIFF(SECOND, B.EVENT_AT, A.EVENT_AT) / 3600.00 AS DECIMAL(22, 5)) + B.RS_HOURS_SINCE_LAST_EVENT >= 24 THEN b.EVENT_GROUP_NUM + 1
                        ELSE b.EVENT_GROUP_NUM
                    END AS EVENT_GROUP_NUM
   					
   FROM CTE_EVENTS_BASE A
   INNER JOIN RECUR_CTE_EVENTS B ON A.ROW_NUM = B.ROW_NUM + 1)

SELECT MIN(a.EVENT_AT) AS EVENT_AT, COUNT(a.ID) AS EVENT_COUNT
FROM RECUR_CTE_EVENTS a
GROUP BY a.EVENT_GROUP_NUM;

EVENT_AT EVENT_COUNT
----------------------- -----------
2024-01-17 10:22:20.000 2
2024-01-18 11:18:50.000 5
2024-01-19 18:01:03.000 4
2024-01-21 00:01:01.000 1