Find out of sequence data in a column of text

I have a table in SQL Server that tracks the working status changes of sales orders.

When the order is first entered it is automatically in a status of NEW.

When the order is populated with product and is ready to ship, it gets put into a status of READY by a human processor.

Our shipping department then opens a form that shows all orders in a READY status and runs a picklist report. At that time the orders are put into a PICKED status programmatically.

Now, here's the problem. Sometimes, for reasons unknown, the status get set to PICKED when it shouldn't be by something that is automated, or sometimes by accident by a user.

The PICKED status should only be preceded by READY or PARTIALSHIP status's, never by anything else.

I need to search through the table to find occurrences of the status being changed to PICKED that are not preceded by either READY or PARTIALSHIP.

I can't seem to get my head wrapped around how to go about this.

Here is an example of the data I am dealing with.

sro_num    seq  stat_code
-------------------------
SO00053427  1   NEW
SO00053427  2   READY
SO00053427  3   PICKED
SO00053427  4   SHIPPED
SO00053427  5   INVOICED
SO00053441  1   NEW
SO00053441  2   INV HOLD
SO00053441  3   PICKED
SO00053441  4   PARTIALSHP
SO00053441  5   BILLMGRHLD
SO00053441  6   INV HOLD
SO00053441  7   PARTIALSHP
SO00053441  8   PARTIALINV
SO00053441  9   BILLMGRHLD
SO00053441  10  INV HOLD
SO00053441  11  READY
SO00053441  12  PICKED
SO00053441  13  SHIPPED
SO00053441  14  INVOICED

I have searched for ideas on how to deal with sequences, but all I find are solutions that deal with numbered lists. Not text.

Any help would be very appreciated.

You could use a CTE to capture the previous values and THEN filter on PICKED.

WITH CTE AS
(  
    SELECT *
        , LAG(stat_code, 1) OVER (PARTITION BY sro_num ORDER BY seq) AS Prev_Stat 
    FROM Example
)
SELECT * 
FROM CTE 
WHERE stat_code = 'PICKED'

fiddle

You can then add criteria to filter on the bad prev_stats

AND Prev_Stat NOT IN ('READY','PARTIALSHIP')

Alternate subquery solution as suggested by Dale K.

SELECT *
FROM 
(  
    SELECT *
        , LAG(stat_code, 1) OVER (PARTITION BY sro_num ORDER BY seq) AS Prev_Stat 
    FROM Example
) t 
WHERE stat_code = 'PICKED' 
  AND Prev_Stat NOT IN ('READY', 'PARTIALSHIP')