TSQL check if specific rows sequence exists

Question

I have a table containing fields:

History_ID   ORDER_ID   Previous_STATUS_ID   Next_STATUS_ID
0            2          null                 1
1            2          1                    2
2            2          2                    3
3            1          null                 1
4            2          3                    4
5            3          null                 2
6            2          4                    1
7            2          1                    2
8            2          2                    5
9            7          3                    4

10 4 6 2 11 9 3 5

It contains my order ID and ID of status. Basically table contains history of STATUS for every order.

I would like to check if a specific order has a specific order sequence.

Example: if order id 4 had states 2,4,3,1 then return true

Statuses should be one after other so after 2 must be 4, after 4 is 3 and after that 1.

Those sequences give me information (which will be displayed in my report) about flow of specific order.

I need to check for that kind of sequences:

• 1,2,3
• 1,4,3,2
• 4,2
• (1 or 2 or 3 or 4),5,3 - one of those 4 then 5 and then 3

I don't have idea how to start with that kind of query :/

EDIT:
My final report is a table containing information about orders like so:

Orders type  Count      ...
Profile1     145        ...
Profile2     217        ...
Profile3     12         ...
Profile4     2          ...

I have about 800k+ orders and my report must be done every day, so instead doing report for whole table (checking those STATES and aggregating informations from other tables) I have decided to create a report table:

Order_ID   Profile   Order_date   Customer_profile     ...
1          5        '2008-01-03'  2
4          1        '2009-04-10'  3
3          7        '2011-11-03'  1
4523       2        '2012-11-03'  5

So that I can create a night job that will add new orders to this table with my information and I will just do a simple select from that table, to avoid aggregation. First insert will be big, but in next night it will only add new orders.

For example look at order with ID = 2
Next_STATUS_ID ale like so: 1,2,3,4,1,2,5... I want to check if this order has history change like 1,2,5, so my function (select statement) should repeat 1 if it finds those ID's in that order.

Answer 1

I think this can be solved using a recursive CTE:

with change_tree as 
(
  SELECT order_id,
         previous_status_id, 
         next_status_id,
         cast(next_status_id as varchar(max)) as status_path
  FROM status_change
  WHERE previous_status_id = 1
    AND order_id = 2

  UNION ALL 

  SELECT sc.order_id,
         sc.previous_status_id,
         sc.next_status_id,
         ct.status_path + ',' + cast(sc.next_status_id as varchar(max))
  FROM status_change sc 
    JOIN change_tree ct ON ct.next_status_id = sc.previous_status_id AND ct.order_id = sc.order_id
)
SELECT *
FROM change_tree
WHERE status_path = '2,3,5';

What this essentially does is to (recursively) concatenate all values of next_status_id to a single string that you can compare to.

From your sample data it's not clear to me which row marks the "beginning" of an order status change history. You will need to adjust the where in the first part of the union to select the correct "starting" row.

Answer 2

I might approach it this way:

1. Create a stored procedure to contain the sequence testing

   a. Select the rows by order ID into a local table @variable in the order of history ID ascending with an IDENTITY column with seed 1 and increment 1

   b. What you do next depends on how complicated/fancy/maintainable you want this to be. A quick and dirty solution would be to build a comma-separated list of the IDs from the table variable, in order, and compare them against some hard-coded sequence strings.

   There are several techniques for building comma-delimited strings from multiple rows.

   c. For a fancier solution, you could create a new table (something like "HistoryTestSequence") that looks something like this (to illustrate your 1,4,3,2 test):

   TestNumber | SequenceOrder | CompareValue
   -----------+---------------+-------------
            1 |             1 |            1
            1 |             2 |            4
            1 |             3 |            3
            1 |             4 |            2
   

   You could then try to JOIN to this from the table variable, matching the identity column to the SequenceOrder column and the history ID to the CompareValue column, WHERE the TestNumber = 1. If the COUNT() of the rows returned after the JOIN equals the COUNT(*) FROM HistoryTestSequence WHERE TestNumber = 1, then you have matched your sequence. You just have to repeat the test for each set of possible sequences, which you could do in a loop, or manually.

If I had some more time I'd love to put together an example, but I'll let you give it a shot for now. The latter example is probably overkill.

Lastly, just keep in mind that testing against identity columns is dangerous -- IDs created by sequences on one server might have different ones on another server (such as if you had production and UAT SQL server instances that are a little out of sync).