lead keep and case

Question

*Edit

I have the below table:

Table W

No    action_dt              type    status
1      5/6/2008 10:55:11 PM  CREATE  APPROVED
1      1/3/2013 9:52:01 AM   UPDATE  APPROVED
1      1/3/2013 9:57:26 AM   UPDATE  APPROVED
1      4/21/2015 2:19:04 PM  UPDATE  APPROVED
1    10/27/2016 10:40:19 AM  UPDATE  APPROVED
1    2/19/2018 4:18:18 PM    UPDATE  APPROVED
1    11/13/2018 4:13:38 PM   UPDATE  NEW
1    11/15/2018 9:44:55 AM   UPDATE  APPROVED
1    7/17/2019 2:21:52 PM    UPDATE  PENDING
1    7/17/2019 2:21:52 PM    UPDATE  NEW
1   7/17/2019 2:47:31 PM     UPDATE  APPROVED

Exprected Results:

No  Action_dt              type     status   start_dt               End_dt
1   5/6/2008 10:55:11 PM   CREATE   APPROVED 5/6/2008 10:55:11 PM   5/6/2008 10:55:11 PM
1   1/3/2013 9:52:01 AM    UPDATE   APPROVED 1/3/2013 9:52:01 AM    1/3/2013 9:52:01 AM
1   1/3/2013 9:57:26 AM    UPDATE   APPROVED 1/3/2013 9:57:26 AM    1/3/2013 9:57:26 AM
1   4/21/2015 2:19:04 PM   UPDATE   APPROVED 4/21/2015 2:19:04 PM   4/21/2015 2:19:04 PM
1   10/27/2016 10:40:19 AM UPDATE   APPROVED 10/27/2016 10:40:19 AM 10/27/2016 10:40:19 AM
1   2/19/2018 4:18:18 PM   UPDATE   APPROVED 2/19/2018 4:18:18 PM   2/19/2018 4:18:18 PM
1   11/13/2018 4:13:38 PM   UPDATE  NEW      11/13/2018 4:13:38 PM  
1   11/15/2018 9:44:55 AM   UPDATE  APPROVED 11/15/2018 9:44:55 AM  11/15/2018 9:44:55 AM
1   7/17/2019 2:21:52 PM    UPDATE  PENDING     
1   7/17/2019 2:21:52 PM    UPDATE  NEW      7/17/2019 2:21:52 PM   
1   7/17/2019 2:47:31 PM    UPDATE  APPROVED 7/17/2019 2:47:31 PM   7/17/2019 2:47:31 PM

Now I need to fill in the missing start_dt and end_dt

row 9 Update Pending is NULL I need to take the first above row when request type = create or update and status = new

end_dt row 7,9,10

Take the below first date when request type = create or update and status code = approved.

How can one do this? Perhaps using Lead/Lag with Keep in the case expression?

For the missing data. Expected output would be (add this into the above expected results)

       Start_dt                   End_dt
row 7                             11/15/2018 9:44:55 AM
row 9  11/13/2018 4:13:38 PM       7/17/2019 2:47:31 PM
row 10                            7/17/2019 2:47:31 PM

SQL:

select *
,CASE
--WHEN W.REQUEST_TYPE IN ('CREATE','UPDATE') AND W.STATUS_CD IN ('APPROVED','DENIED') THEN W.ACTION_DT
WHEN W.REQUEST_TYPE IN ('CREATE','UPDATE') AND W.STATUS_CD IN ('NEW','APPROVED')  THEN W.ACTION_DT
--WHEN W.REQUEST_TYPE IN ('CREATE','UPDATE') AND W.STATUS_CD IN ('PENDING')  THEN W.ACTION_DT
ELSE NULL
END REQUEST_START_DT

,CASE
WHEN W.REQUEST_TYPE IN ('CREATE','UPDATE') AND W.STATUS_CD IN ('APPROVED','DENIED') THEN W.ACTION_DT
--WHEN W.REQUEST_TYPE IN ('CREATE','UPDATE') AND W.STATUS_CD IN ('PENDING') THEN LEAD((W.ACTION_DT) KEEP (DENSE_RANK ORDER BY W.STATUS_CD) OVER (PARTITION BY ORG_NO) end)
ELSE NULL
END REQUEST_END_DT
FROM APEXIM.CRD_ORG_WORK W
WHERE 1=1
AND W.NO = 1
from W

There doesn't appear to be a question here. Please edit it and change it so that you're asking a question. What is it you need help with? (Do you get an error? Does it not work the way you want/expect? Something else?) — Boneist, Jul 23 '19 at 13:02
What is your expected output? What dates should rows 7, 9 & 10 have? — MT0, Jul 23 '19 at 13:10
@MT0 added additional expected out results table. for rows 7,9 and 10 — John, Jul 23 '19 at 13:16
I would imagine I would use lead/lag in my case but getting errors. This is in my sql that's posted. — John, Jul 23 '19 at 13:17
@John What you say are the expected results don't appear to be the results you expect to get. Please update that set of data with the start and end dates you're actually trying to get. — Boneist, Jul 23 '19 at 13:30

score 2 · Accepted Answer · answered Jul 23 '19 at 13:31

Use LAG( CASE WHEN <your matches> THEN action_dt END ) (or LEAD) with the IGNORE NULLS option to skip the rows that do not match the CASE statement.

Oracle Setup:

CREATE TABLE w ( no, action_dt, request_type, status ) AS
SELECT 1, DATE '2008-05-06' + INTERVAL '22:55:11' HOUR TO SECOND, 'CREATE', 'APPROVED' FROM DUAL UNION ALL
SELECT 1, DATE '2013-01-03' + INTERVAL '09:52:01' HOUR TO SECOND, 'UPDATE', 'APPROVED' FROM DUAL UNION ALL
SELECT 1, DATE '2013-01-03' + INTERVAL '09:57:26' HOUR TO SECOND, 'UPDATE', 'APPROVED' FROM DUAL UNION ALL
SELECT 1, DATE '2015-04-21' + INTERVAL '14:19:04' HOUR TO SECOND, 'UPDATE', 'APPROVED' FROM DUAL UNION ALL
SELECT 1, DATE '2016-10-27' + INTERVAL '10:40:19' HOUR TO SECOND, 'UPDATE', 'APPROVED' FROM DUAL UNION ALL
SELECT 1, DATE '2018-02-19' + INTERVAL '16:18:18' HOUR TO SECOND, 'UPDATE', 'APPROVED' FROM DUAL UNION ALL
SELECT 1, DATE '2018-11-13' + INTERVAL '16:13:38' HOUR TO SECOND, 'UPDATE', 'NEW'      FROM DUAL UNION ALL
SELECT 1, DATE '2018-11-15' + INTERVAL '09:44:55' HOUR TO SECOND, 'UPDATE', 'APPROVED' FROM DUAL UNION ALL
SELECT 1, DATE '2019-07-17' + INTERVAL '14:21:52' HOUR TO SECOND, 'UPDATE', 'PENDING'  FROM DUAL UNION ALL
SELECT 1, DATE '2019-07-17' + INTERVAL '14:21:52' HOUR TO SECOND, 'UPDATE', 'NEW'      FROM DUAL UNION ALL
SELECT 1, DATE '2019-07-17' + INTERVAL '14:47:31' HOUR TO SECOND, 'UPDATE', 'APPROVED' FROM DUAL

Query:

SELECT w.*,
       CASE
       WHEN request_type IN ( 'CREATE', 'UPDATE' ) AND status IN ( 'NEW', 'APPROVED' )
       THEN action_dt
       WHEN request_type IN ( 'CREATE', 'UPDATE' ) AND status = 'PENDING'
       THEN LAG( CASE WHEN request_type IN ( 'CREATE', 'UPDATE' ) AND status = 'NEW' THEN action_dt END )
              IGNORE NULLS OVER ( PARTITION BY no ORDER BY action_dt )
       ELSE NULL
       END AS start_dt,
       CASE
       WHEN request_type IN ( 'CREATE', 'UPDATE' ) AND status IN ( 'NEW', 'APPROVED' )
       THEN action_dt
       WHEN request_type IN ( 'CREATE', 'UPDATE' ) AND status = 'PENDING'
       THEN LEAD( CASE WHEN request_type IN ( 'CREATE', 'UPDATE' ) AND status = 'APPROVED' THEN action_dt END )
              IGNORE NULLS OVER ( PARTITION BY no ORDER BY action_dt )
       ELSE NULL
       END AS end_dt
FROM   w

Output:

NO | ACTION_DT           | REQUEST_TYPE | STATUS   | START_DT            | END_DT             
-: | :------------------ | :----------- | :------- | :------------------ | :------------------
 1 | 2008-05-06 22:55:11 | CREATE       | APPROVED | 2008-05-06 22:55:11 | 2008-05-06 22:55:11
 1 | 2013-01-03 09:52:01 | UPDATE       | APPROVED | 2013-01-03 09:52:01 | 2013-01-03 09:52:01
 1 | 2013-01-03 09:57:26 | UPDATE       | APPROVED | 2013-01-03 09:57:26 | 2013-01-03 09:57:26
 1 | 2015-04-21 14:19:04 | UPDATE       | APPROVED | 2015-04-21 14:19:04 | 2015-04-21 14:19:04
 1 | 2016-10-27 10:40:19 | UPDATE       | APPROVED | 2016-10-27 10:40:19 | 2016-10-27 10:40:19
 1 | 2018-02-19 16:18:18 | UPDATE       | APPROVED | 2018-02-19 16:18:18 | 2018-02-19 16:18:18
 1 | 2018-11-13 16:13:38 | UPDATE       | NEW      | 2018-11-13 16:13:38 | 2018-11-13 16:13:38
 1 | 2018-11-15 09:44:55 | UPDATE       | APPROVED | 2018-11-15 09:44:55 | 2018-11-15 09:44:55
 1 | 2019-07-17 14:21:52 | UPDATE       | PENDING  | 2018-11-13 16:13:38 | 2019-07-17 14:47:31
 1 | 2019-07-17 14:21:52 | UPDATE       | NEW      | 2019-07-17 14:21:52 | 2019-07-17 14:21:52
 1 | 2019-07-17 14:47:31 | UPDATE       | APPROVED | 2019-07-17 14:47:31 | 2019-07-17 14:47:31

db<>fiddle here

lead keep and case

1 Answers1