title: lead keep and case
body: <p>*Edit</p>
  
  <p>I have the below table:</p>
  
  <p>Table W</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Exprected Results:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Now I need to fill in the missing start_dt and end_dt</p>
  
  <p>row 9 Update Pending is NULL I need to take the first above row when request type = create or update and status = new</p>
  
  <p>end_dt row 7,9,10</p>
  
  <p>Take the below first date when request type = create or update and status code = approved. </p>
  
  <p>How can one do this? Perhaps using Lead/Lag with Keep in the case expression?</p>
  
  <p>For the missing data. Expected output would be (add this into the above expected results)</p>
  
  <pre><code>       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
  </code></pre>
  
  <p>SQL:</p>
  
  <pre><code>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
  </code></pre>