I have a table with a series of timelines that are normalized starting from 00:00:00.00000. I want to summate them sequentially based on my order_key value.
Sample Data:
id order_key start_time end_time activity_type
1 1 00:00:00.00000 00:00:00.01000 A
1 1 00:00:00.01000 00:00:00.02000 B
1 1 00:00:00.02000 00:00:00.03000 C
1 1 00:00:00.03000 00:00:00.03500 A
1 2 00:00:00.00000 00:00:00.01500 A
1 2 00:00:00.01500 00:00:00.04500 B
1 3 00:00:00.00000 00:00:00.05500 B
Desired Output:
id start_time end_time activity_type
1 00:00:00.00000 00:00:00.01000 A
1 00:00:00.01000 00:00:00.02000 B
1 00:00:00.02000 00:00:00.03000 C
1 00:00:00.03000 00:00:00.03500 A
1 00:00:00.03500 00:00:00.05000 A
1 00:00:00.05000 00:00:00.08000 B
1 00:00:00.08000 00:00:00.13500 B
My Attempt:
SELECT
id
, -- CASE WHEN new order_key THEN LAG(end_time) OVER (PARTITION BY id ORDER BY snap_view_index, start_time) ELSE start_time END AS start_time
, -- CASE WHEN new order_key THEN TIME_ADD(LAG(end_time), INTERVAL TIME_DIFF(end_time, start_time, MILLISECOND) MILLISECOND) ELSE TIME_ADD(start_time, INTERVAL TIME_DIFF(end_time, start_time, MILLISECOND) MILLISECOND)
, activity_type
FROM my_table;
