This seems really simple but I can’t figure it out, working in SQL Server. I have time series data and I want a column to track the state of ON/OFF events for each row, so when there’s an ON event then the Desired Output column will have a 1 for each subsequent event until there is an OFF event. Please see example below. Thanks so much!
TimeStamp | Event | Desired Output |
---|---|---|
1:01 | ON | 1 |
1:02 | OFF | 0 |
1:04 | other | 0 |
1:05 | other | 0 |
1:06 | ON | 1 |
1:10 | other | 1 |
1:12 | other | 1 |
1:14 | OFF | 0 |
1:15 | other | 0 |
Advertisement
Answer
x
WITH
flagged AS
(
SELECT
timestamp,
CASE WHEN event = 'ON' THEN 1
WHEN event = 'OFF' THEN 0 END AS state
FROM
#table
),
cumulative AS
(
SELECT
*,
COUNT(state) OVER (ORDER BY timestamp) AS state_group
FROM
flagged
)
SELECT
timestamp,
MAX(state) OVER (PARTITION BY state_group) AS persisted_state
FROM
cumulative