Hello AskTOM,
this is referring to "KISS series on Analytics: 17 The LAG / LEAD clauses":
https://youtu.be/r7AM-1qX7Vs The testcase for that can be found in
https://livesql.oracle.com/apex/livesql/file/content_CZUCT0MCOQZMJM7TI553HC8S9.html I have sent a tweet to @connor_mc_d while AskTOM was closed for new questions, but got no response there.
The final query in the livesql includes does not meet expectatation. The FROM_DATE and TO_DATE there don't return the correct interval for each status.
I assume that in the base table ORDERS the column STATUS_DATE is getting filled with the timestamp, when the current status actually was recognized in the workflow or order processing. A status then should be existing until its indicated in ORDERS to be replaced first by another new or already previously existing STATUS.
So the first status "New" started 03-JAN-16 and was changed to status "Inventory Check" on 04-JAN-16, which got replaced by "Awaiting Signoff" on 09-JAN-16. This status gets changed on 12-JAN-16 into status "In Warehouse".
Therefore I expect the first three rows of the result to be:
ORDER_ID STATUS FROM_DATE TO_DATE
11700 New 03-JAN-16 04-JAN-16
11700 Inventory Check 04-JAN-16 09-JAN-16
11700 Awaiting Signoff 09-JAN-16 12-JAN-16
Instead however the final query in the livesql returns as first three rows:
ORDER_ID STATUS FROM_DATE TO_DATE
11700 New - 03-JAN-16
11700 Inventory Check 03-JAN-16 08-JAN-16
11700 Awaiting Signoff 08-JAN-16 11-JAN-16
So I wonder what would be the best query to get the expected and in my point of view correct result also for FROM_DATE and TO_DATE?
Regards,
Bernhard
I suppose its a question of interpretation, but you could do something like this
SQL> select
2 order_id,
3 status,
4 nvl(1+lag(status_date) over (partition by order_id order by status_date),status_date) from_date,
5 1+status_date to_date
6 from (
7 select
8 order_id,
9 status_date,
10 status,
11 lag(status) over (partition by order_id order by status_date) lag_status,
12 lead(status) over (partition by order_id order by status_date) lead_status
13 from ORDERS
14 )
15 where lag_status is null
16 or lead_status is null
17 or lead_status <> status
18 order by 1,3 nulls first;
ORDER_ID STATUS FROM_DATE TO_DATE
---------- -------------------- --------- ---------
11700 New 03-JAN-16 04-JAN-16
11700 Inventory Check 04-JAN-16 09-JAN-16
11700 Awaiting Signoff 09-JAN-16 12-JAN-16
11700 In Warehouse 12-JAN-16 15-JAN-16
11700 Awaiting Signoff 15-JAN-16 17-JAN-16
11700 Payment Pending 17-JAN-16 19-JAN-16
11700 Awaiting Signoff 19-JAN-16 21-JAN-16
11700 Delivery 21-JAN-16 23-JAN-16