no    action_dt          request   status
 801      7/17/2019 2:21:52  update    new
 801      7/17/2019 2:21:52  update    pending
 801      7/17/2019 2:27:31  update    approved

no    action_dt          request   status   start_dt           end_dt
 801      7/17/2019 2:21:52  update    new      7/17/2019 2:21:52  7/17/2019 2:27:31
 801      7/17/2019 2:21:52  update    pending  7/17/2019 2:21:52  7/17/2019 2:27:31
 801      7/17/2019 2:27:31  update    approved 7/17/2019 2:21:52  7/17/2019 2:27:31

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')     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 FIRST ORDER BY W.STATUS_CD) OVER (PARTITION BY NO)
 ELSE NULL
 END REQUEST_END_DT
from W