So say I have the following:
Timestamp | Event_Name |
---|---|
10:47:00 | RESUME |
10:37:52 | SUSPEND |
10:26:20 | RESUME |
09:48:27 | SUSPEND |
09:39:13 | RESUME |
09:15:38 | SUSPEND |
09:11:42 | RESUME |
I want to be able to pair them as such:
Suspend | Resume |
---|---|
null | 10:47:00 |
10:37:52 | 10:26:20 |
09:48:27 | 09:39:13 |
09:15:38 | 09:11:42 |
If each pair had another field to group on I would use a listagg window function but I can’t. Lag function doesn’t work either.
Advertisement
Answer
As having a DBMS containing window function, you can use the following query
WITH t AS ( SELECT CASE WHEN Event_Name = 'SUSPEND' THEN Timestamp END AS Suspend, CASE WHEN Event_Name = 'RESUME' THEN Timestamp END AS Resume, SUM(CASE WHEN Event_Name = 'SUSPEND' THEN 1 ELSE 0 END) OVER(PARTITION BY Event_Name ORDER BY Timestamp) AS rn_suspend, SUM(CASE WHEN Event_Name = 'RESUME' THEN 1 ELSE 0 END) OVER(PARTITION BY Event_Name ORDER BY Timestamp) AS rn_resume FROM t ) SELECT s.suspend, r.resume FROM (SELECT * FROM t WHERE resume IS NOT NULL) AS r FULL JOIN (SELECT * FROM t WHERE suspend IS NOT NULL) AS s ON s.rn_suspend = r.rn_resume ORDER BY r.rn_resume DESC, s.rn_suspend DESC
where SUM
window function is used to count the each switch for the Event_Name