Find next record where status field is different from current

Question

I have a table that is used to log events. Two types specifically : ON and OFF.

There are sometimes overlapping log entries as there can be 2 simultaneous devices logging. This is not crucial, as the end report should give a [mostly] correct overview of ON -> OFF periods.

Below is a sample, with the 3rd column just for illustration: It does not exist.

ActionTaken    ID   ID_of_next_OFF
Switched ON    1    3
Switched ON    2    6
Switched OFF   3    
Switched ON    4    7
Switched ON    5    8
Switched OFF   6    
Switched OFF   7    
Switched OFF   8    
Switched On    9    10
Switched OFF   10   
Switched On    11   12
Switched OFF   12

Given the first two columns, how can I calculate the third?

This does not work:

SELECT actionTaken, Id, LEAD(Id) 
OVER (PARTITION BY ActionTaken ORDER BY ID) nextConn 
FROM dbo.Events

as it bases the ID_of_Next on the next matching actionTaken value, instead of the next alternate.

score 1 · Answer 1 · answered Jul 03 '19 at 14:49

something like this should get you there.

Below I've used 2 CTE's to split the off and on data and then provide a ranking item for first switch on first switch off then I've used a union query to match those up

declare @Events table (
    ActionTaken nvarchar(25),
    ID int
);

insert @Events
values

--ActionTaken    ID   ID_of_next_OFF
('Switched ON' ,   1), -- 3
('Switched ON' ,   2),-- 6
('Switched OFF',   3),    
('Switched ON' ,   4),--    7
('Switched ON' ,   5),--    8
    ('Switched OFF',   6),    
    ('Switched OFF',   7),    
    ('Switched OFF',   8),    
    ('Switched On' ,   9),--    10
    ('Switched OFF',   10),   
    ('Switched On' ,   11),--   12
    ('Switched OFF',   12);

    with onrank as (
    select row_number()over(order by id) ranking, * from @Events where ActionTaken like '%ON')
    , offrank as (
    select row_number()over(order by id) ranking, * from @Events where ActionTaken like '%OFF')

    select o.ActionTaken, o.ID, case when o.ranking=f.ranking then cast(f.id as nvarchar(3)) end as Id_next_off
    from onrank o inner join offrank f on o.ranking=f.ranking
    union
    select ActionTaken, ID, '' from offrank
    order by o.ID;

Andrei Odegov · Accepted Answer · 2019-07-03T15:24:43.237

You are on the right way. All you need is the LEFT JOIN of the 'Switched ON' part with the 'Switched OFF' part on equal row numbers.

with Events as (
  select 'Switched ON' as ActionTaken, 1 as ID union all -- 3
  select 'Switched ON', 2 union all -- 6
  select 'Switched OFF', 3 union all
  select 'Switched ON', 4 union all -- 7
  select 'Switched ON', 5 union all -- 8
  select 'Switched OFF', 6 union all
  select 'Switched OFF', 7 union all
  select 'Switched OFF', 8 union all
  select 'Switched On', 9 union all -- 10
  select 'Switched OFF', 10 union all
  select 'Switched On', 11 union all -- 12
  select 'Switched OFF', 12
), E as (
  select
    *, row_number() over(partition by ActionTaken order by ID) as rn
  from Events
)
select
  a.ActionTaken, a.ID, b.ID
from E as a
left join E as b
  on a.ActionTaken = 'Switched ON' and
     b.ActionTaken = 'Switched OFF' and
     a.rn = b.rn
order by a.ID, a.ActionTaken;

Output:

+--------------+----+------+
| ActionTaken  | ID |  ID  |
+--------------+----+------+
| Switched ON  |  1 | 3    |
| Switched ON  |  2 | 6    |
| Switched OFF |  3 | NULL |
| Switched ON  |  4 | 7    |
| Switched ON  |  5 | 8    |
| Switched OFF |  6 | NULL |
| Switched OFF |  7 | NULL |
| Switched OFF |  8 | NULL |
| Switched On  |  9 | 10   |
| Switched OFF | 10 | NULL |
| Switched On  | 11 | 12   |
| Switched OFF | 12 | NULL |
+--------------+----+------+

Test it online with SQL Fiddle.

I move the accepted answer to yours as it was easier to implement, and to add more fields to the ranking. — callisto, Jul 11 '19 at 10:46

Find next record where status field is different from current

2 Answers2

Linked