I am using SQL to detect a known state change in a table. Please see the columns below where the state moves from GOOD, BAD and UNDEFINED.

Now see the column below where the state skips the BAD state and goes directly to UNDEFINED.

How can i detect the date on which the state entered UNDEFINED from GOOD. i.e my result should give me the two rows with date 19/08/2022 and 18/08/2022 capturing the change.

CodePudding user response：

You can use LAG() and LEAD() to peek at the previous or next values. The, finding the rows is trivial.

For example:

select *
from (
  select t.*,
    lag(status) over (order by date) as prev_status,
    lead(status) over (order by date) as next_status
  from t
) x
where status = 'UNDEFINED' and prev_status = 'GOOD'
   or status = 'GOOD' and next_status = 'UNDEFINED'

CodePudding user response：

If your MySql version doesn't support window functions and provided you don't have gaps, you can try the following with correlated exists criteria:

select * 
from t
where exists (
  select * from  t t2
  where t2.row = t.row 
    and (t.status='GOOD' and t2.status = 'UNDEFINED' and DateDiff(t2.date, t.date ) = 1)
    or (t.status='UNDEFINED' and t2.status = 'GOOD' and DateDiff(t.date, t2.date ) = 1)
);