CODEWITHSUNDEEP
sqlsql-servert-sql
Rise_against
Rise_against

Reputation: 1060

T-SQL to determine "out of sequence" records

I am using SQL server and I need to determine records that are "out of sequence" from a table.

I'll explain through an example. I have the following table structure:

OrderNumber OrderStatus EventDateTime
0001522989  22          2014-04-14 05:49:25.4414243
0001522989  26          2014-04-14 05:51:16.7047485
0001522989  23          2014-04-14 05:51:17.8602798
0001522990  23          2014-04-14 05:51:19.9603575
0001522990  24          2014-04-14 05:52:06.5803494
0001522990  24          2014-04-14 05:52:06.5803494

Now I need to produce a list of OrderNumbers that were sent "out of order". So in this example, the list will contain only one value: "0001522989".

Order 0001522990 was sent in the correct sequence (first status 23, then status 24 and then again status 24 (this doesn't count as "out of sequence")).

Order 0001522989 was not sent in the correct sequence (first status 22, then status 26 and then status 23).

Any idea on how I can accomplish this?

I added the possibility of an order to send out the same status twice in a row (this shouldn't count as "out of sequence")

Thanks in advance.

Upvotes: 5

Views: 2127

Answers (2)

SWeko
SWeko

Reputation: 30902

If the question boils down to:

Find all invoice numbers where there is a lower status number with a higher date

you can do:

select OrderNumber 
from Orders o1
where exists (select * from Orders o2 
              where o2.OrderNumber = o1.OrderNumber
                and o2.OrderStatus >= o1.OrderStatus 
                and o2.EventDateTime < o1.EventDateTime)

Upvotes: 0

MatBailie
MatBailie

Reputation: 86735

In SQL Server 2008 onwards...

SELECT
  OrderNumber
FROM
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY OrderNumber ORDER BY OrderStatus, EventDateTime  )   AS sequenceCorrect,
    ROW_NUMBER() OVER (PARTITION BY OrderNumber ORDER BY              EventDateTime)   AS sequenceActual
  FROM
    yourTable
)
  AS yourTableSequenced
WHERE
  sequenceCorrect <> sequenceActual
GROUP BY
  OrderNumber
ORDER BY
  OrderNumber


EDIT : Oops, I forgot the WHERE clause, should work now ;)

Upvotes: 6

Related Questions