Pfthb

I apologise for the vague question. I have been trying to wrap my head around this query for some time now. A table I have to work with in my company shows the shipment id (id), status codes, the manual values typed in for those statuses and the timestamp. The thing is a bit of a mess. The table contains a lot of unnecessary columns but will break it down below.

SELECT
 id,
 stat_code,
 event_date,
 insert_date
FROM
 table

The above code returns multiple same stat_code(s) but with different insert_date(s). I was requested to perform an analysis on only the last manual inputs for each id so my next table looked like this:

WITH
last_status AS (SELECT
 id,
 stat_code,
 event_date,
 insert_date
 ROW_NUMBER() OVER (PARTITION BY id, stat_code ORDER BY insert_date DESC) RN
FROM
 table)

SELECT
 id,
 stat_1.event_date
FROM
 table2
 LEFT JOIN last_status stat_1 ON table2.id=stat_1.id AND stat_1.stat_code='stat 1' AND stat_1.RN=1

This allows me to add as many statuses I need to the second SELECT statement by recycling the last_status table always selecting RN to be 1.
For a different project I need the last status which was inserted before a different status (which can also be inserted multiple times. The agreement was to compare against the first insertion).
Lets say the event_date for stat_1 is an estimated date when a shipment should arrive to the destination and the event_date for stat_2 is the actual arrival date. My company wants to see how accurate was the estimated value against the actual value. There were some instances where stat_1 was updated after stat_2 but we want to compare only the event_date for stat_1 for which the insert_date <= event_date for first inserted stat_2. This is what I've done so far

WITH
first_status AS (SELECT
 id,
 stat_code,
 event_date,
 insert_date,
 ROW_NUMBER() OVER (PARTITION BY id, stat_code ORDER BY insert_date ASC) RN
FROM
 table),

last_stat_1 AS (SELECT * FROM (SELECT
 t.id,
 t.event_date,
 ROW_NUMBER() OVER (PARTITION BY t.id, t.stat_code ORDER BY t.insert_date DESC) RN
FROM
 table t
 LEFT JOIN first_status stat_2 ON t.id=stat_2.id and stat_2.stat_code='stat 2' AND RN=1
WHERE
 t.stat_code='stat 1' and (t.insert_date < stat_2.event_date OR stat_2.event_date IS NULL) WHERE RN=1)

SELECT
 id,
 stat_1.event_date
FROM
 table2
 LEFT JOIN last_stat_1 stat_1 ON table2.id=stat_1.id

This approach is not that clean nor performing well. If I need to add a different status, then I would have to write another table. I would like to have a way to either write a function somehow to perform the following calculation so whenever a new status needs to be mapped into my report I could just recycle the function last_before(). How do I create such a function to get the below result?

SELECT
 id,
 last_before(stat_1,stat_2) last_stat_1
FROM

Or somehow get the first table and only allow 'stat 1' (s) - all of them which were inserted before the first 'stat 2' (and rules for other statuses).
So in the below example I would like to not see row 3 and 4 because they were inserted on the 15-aug but the event_date for stat 2 was the 14-aug.

id stat_code event_date insert_date
x stat 8 12-AUG-16 16-SEP-16
x stat 7 18-AUG-16 18-AUG-16
x stat 1 14-AUG-16 15-AUG-16
x stat 1 15-AUG-16 15-AUG-16
x stat 2 14-AUG-16 15-AUG-16
x stat 6 08-AUG-16 10-AUG-16
x stat 5 09-AUG-16 10-AUG-16
x stat 4 11-AUG-16 04-AUG-16
x stat 3 23-AUG-16 04-AUG-16
x stat 1 15-AUG-16 04-AUG-16
x stat 1 13-AUG-16 02-AUG-16
x stat 4 09-AUG-16 02-AUG-16
x stat 3 21-AUG-16 02-AUG-16