SQL Lag/Lead with condition-CodePudding

I have data from programmable controlers, for which I need a query. The data is

Timestamp          Sensor State
2020-01-01 10:00   'S1'   'ON'
2020-01-01 10:05   'S1'   'OFF'
2020-01-01 10:06   'S1'   'OFF'
2020-01-01 10:06   'S2'   'ON'
2020-01-01 10:10   'S2'   'OFF'
2020-01-01 10:11   'S1'   'OFF'
2020-01-01 10:12   'S2'   'OFF'
2020-01-01 10:15   'S1'   'ON'
2020-01-01 10:18   'S1'   'OFF'
2020-01-01 10:19   'S1'   'OFF'
2020-01-01 10:19   'S2'   'OFF'
2020-01-01 10:26   'S1'   'OFF'

What I need, is a query which gives me a result like

ON                 OFF              Sensor State
2020-01-01 10:00   2020-01-01 10:05 'S1'   'ON'
2020-01-01 10:06   2020-01-01 10:10 'S1'   'ON'
2020-01-01 10:15   2020-01-01 10:18 'S1'   'ON'

So I need the timestamp for Switch "ON" and the first corresponding Switch "OFF" I thought i could use the LEAD() OVER() function, but I did not manage it. I hope you understand my problem.

Regards Markus

CodePudding user response：

I assume the second line of your results is supposed to be S2?

You were on the right track with LEAD, this should get you there:

CREATE TABLE #data
(
    [Timestamp] DATETIME,
    [sensor]    VARCHAR(2),
    [State]     VARCHAR(3)
);

INSERT INTO #data
VALUES
('2020-01-01 10:00', 'S1', 'ON'),
('2020-01-01 10:05', 'S1', 'OFF'),
('2020-01-01 10:06', 'S1', 'OFF'),
('2020-01-01 10:06', 'S2', 'ON'),
('2020-01-01 10:10', 'S2', 'OFF'),
('2020-01-01 10:11', 'S1', 'OFF'),
('2020-01-01 10:12', 'S2', 'OFF'),
('2020-01-01 10:15', 'S1', 'ON'),
('2020-01-01 10:18', 'S1', 'OFF'),
('2020-01-01 10:19', 'S1', 'OFF'),
('2020-01-01 10:19', 'S2', 'OFF'),
('2020-01-01 10:26', 'S1', 'OFF');

WITH d_on AS
(
    SELECT  d.sensor, d.[Timestamp] 
    FROM    #data d 
    WHERE   d.State = 'ON'
),
next_on AS
(
    SELECT  d_on.sensor,
            d_on.Timestamp,
            LEAD(d_on.Timestamp, 1, '99991231') OVER (PARTITION BY d_on.sensor ORDER BY d_on.Timestamp) AS next_on
    FROM    d_on
)
SELECT      next_on.sensor,
            next_on.Timestamp AS [ON],
            a.[OFF]
FROM        next_on
            OUTER APPLY
            (
                SELECT  MIN([Timestamp]) AS [OFF]
                FROM    #data d_off
                WHERE   d_off.State = 'OFF'
                        AND d_off.sensor = next_on.sensor
                        AND d_off.Timestamp BETWEEN next_on.Timestamp AND next_on.next_on
            ) a
ORDER BY    [ON];

CodePudding user response：

For your particular dataset, there are no instances where there are multiple ON states, so you can just use a simple LEAD calculation.

SELECT
  [ON] = Timestamp,
  [OFF] = NextTS,
  sensor,
  State
FROM (
    SELECT *,
      NextTS = LEAD(Timestamp) OVER (PARTITION BY sensor ORDER BY Timestamp)
    FROM #data
) t
WHERE State = 'ON';

If there could be multiple ON states, and you want the next OFF for each, you can use a gaps-and-islands technique:

WITH Islands AS (
    SELECT *,
      IsStart = CASE WHEN State = 'OFF' AND LAG(State) OVER (PARTITION BY sensor ORDER BY Timestamp) = 'OFF' THEN 1 END
    FROM #data
),
Groupings AS (
    SELECT *,
      GroupId = COUNT(IsStart) OVER (PARTITION BY sensor ORDER BY Timestamp ROWS UNBOUNDED PRECEDING)
    FROM Islands
),
NextValues AS (
    SELECT *,
      [OFF] = MAX(CASE WHEN state = 'OFF' THEN Timestamp END) OVER (PARTITION BY sensor, GroupId)
    FROM Groupings
)
SELECT
  [ON] = Timestamp,
  [OFF],
  sensor,
  State
FROM NextValues
WHERE State = 'ON';

db<>fiddle