CODEWITHSUNDEEP
sqlstate-machine
Andy Bas
Andy Bas

Reputation: 217

Determine records which held particular "state" on a given date

I have a state machine architecture, where a record will have many state transitions, the one with the greatest sort_key column being the current state. My problem is to determine which records held a particular state (or states) for a given date.

Example data:

items table
id 
1

item_transitions table
id   item_id   created_at    to_state     sort_key
1      1          05/10      "state_a"       1
2      1          05/12      "state_b"       2
3      1          05/15      "state_a"       3
4      1          05/16      "state_b"       4

Problem:

Determine all records from items table which held state "state_a" on date 05/15. This should obviously return the item in the example data, but if you query with date "05/16", it should not.

I presume I'll be using a LEFT OUTER JOIN to join the items_transitions table to itself and narrow down the possibilities until I have something to query on that will give me the items that I need. Perhaps I am overlooking something much simpler.

Upvotes: 1

Views: 64

Answers (1)

Martin K.
Martin K.

Reputation: 1060

Your question rephrased means "give me all items which have been changed to state_a on 05/15 or before and have not changed to another state afterwards. Please note that for the example it added 2001 as year to get a valid date. If your "created_at" column is not a datetime i strongly suggest to change it.

So first you can retrieve the last sort_key for all items before the threshold date:

SELECT item_id,max(sort_key) last_change_sort_key
FROM item_transistions it
WHERE created_at<='05/15/2001'
GROUP BY item_id

Next step is to join this result back to the item_transitions table to see to which state the item was switched at this specific sort_key:

SELECT *
FROM item_transistions it
   JOIN (SELECT item_id,max(sort_key) last_change_sort_key
         FROM item_transistions it
         WHERE created_at<='05/15/2001'
         GROUP BY item_id) tmp ON it.item_id=tmp.item_id AND it.sort_key=tmp.last_change_sort_key

Finally you only want those who switched to 'state_a' so just add a condition:

SELECT DISTINCT it.item_id
FROM item_transistions it
   JOIN (SELECT item_id,max(sort_key) last_change_sort_key
         FROM item_transistions it
         WHERE created_at<='05/15/2001'
         GROUP BY item_id) tmp ON it.item_id=tmp.item_id AND it.sort_key=tmp.last_change_sort_key
WHERE it.to_state='state_a'

You did not mention which DBMS you use but i think this query should work with the most common ones.

Upvotes: 1

Related Questions