Find Next Record Where Status Field Is Different From Current

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

Solution 1:

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, 1as ID unionall-- 3select'Switched ON', 2unionall-- 6select'Switched OFF', 3unionallselect'Switched ON', 4unionall-- 7select'Switched ON', 5unionall-- 8select'Switched OFF', 6unionallselect'Switched OFF', 7unionallselect'Switched OFF', 8unionallselect'Switched On', 9unionall-- 10select'Switched OFF', 10unionallselect'Switched On', 11unionall-- 12select'Switched OFF', 12
), E as (
  select*, row_number() over(partitionby ActionTaken orderby ID) as rn
  from Events
)
select
  a.ActionTaken, a.ID, b.ID
from E as a
leftjoin E as b
  on a.ActionTaken ='Switched ON'and
     b.ActionTaken ='Switched OFF'and
     a.rn = b.rn
orderby 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.

Solution 2:

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@Eventstable (
    ActionTaken nvarchar(25),
    ID int
);

insert@Eventsvalues--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 (
    selectrow_number()over(orderby id) ranking, *from@Eventswhere ActionTaken like'%ON')
    , offrank as (
    selectrow_number()over(orderby id) ranking, *from@Eventswhere ActionTaken like'%OFF')

    select o.ActionTaken, o.ID, casewhen o.ranking=f.ranking thencast(f.id as nvarchar(3)) endas Id_next_off
    from onrank o innerjoin offrank f on o.ranking=f.ranking
    unionselect ActionTaken, ID, ''from offrank
    orderby o.ID;