Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE W ( NO, ACTION_DT, REQUEST_TYPE, STATUS_CD ) AS
SELECT 34, DATE '2007-12-05' + INTERVAL '18:57:04' HOUR TO SECOND, 'CREATE', 'APPROVED' FROM DUAL UNION ALL
SELECT 34, DATE '2012-01-10' + INTERVAL '14:53:49' HOUR TO SECOND, 'DELETE', 'PENDING'  FROM DUAL UNION ALL
SELECT 34, DATE '2012-01-10' + INTERVAL '15:00:00' HOUR TO SECOND, 'DELETE', 'DENIED'   FROM DUAL UNION ALL
SELECT 34, DATE '2013-01-07' + INTERVAL '09:42:33' HOUR TO SECOND, 'DELETE', 'APPROVED' FROM DUAL
4 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT W.*,
       COUNT( CASE WHEN STATUS_CD IN ( 'APPROVED', 'DENIED' ) THEN 1 END )
         OVER ( PARTITION BY NO ORDER BY ACTION_DT )
         + CASE WHEN STATUS_CD IN ( 'APPROVED', 'DENIED' ) THEN 0 ELSE 1 END
         AS grp
FROM   w


NO
ACTION_DT
REQUEST_TYPE
STATUS_CD
GRP


34
2007-12-05 18:57:04
CREATE
APPROVED
1

34
2012-01-10 14:53:49
DELETE
PENDING
2

34
2012-01-10 15:00:00
DELETE
DENIED
2

34
2013-01-07 09:42:33
DELETE
APPROVED
3


SELECT no,
       action_dt,
       request_type,
       status_cd,
       MIN( action_dt ) OVER ( PARTITION BY no, grp ) AS request_start_dt
FROM   (
  SELECT W.*,
         COUNT( CASE WHEN STATUS_CD IN ( 'APPROVED', 'DENIED' ) THEN 1 END )
           OVER ( PARTITION BY NO ORDER BY ACTION_DT )
           + CASE WHEN STATUS_CD IN ( 'APPROVED', 'DENIED' ) THEN 0 ELSE 1 END
           AS grp
  FROM   w
)
ORDER BY action_dt


NO
ACTION_DT
REQUEST_TYPE
STATUS_CD
REQUEST_START_DT


34
2007-12-05 18:57:04
CREATE
APPROVED
2007-12-05 18:57:04

34
2012-01-10 14:53:49
DELETE
PENDING
2012-01-10 14:53:49

34
2012-01-10 15:00:00
DELETE
DENIED
2012-01-10 14:53:49

34
2013-01-07 09:42:33
DELETE
APPROVED
2013-01-07 09:42:33

NO	ACTION_DT	REQUEST_TYPE	STATUS_CD	GRP
34	2007-12-05 18:57:04	CREATE	APPROVED	1
34	2012-01-10 14:53:49	DELETE	PENDING	2
34	2012-01-10 15:00:00	DELETE	DENIED	2
34	2013-01-07 09:42:33	DELETE	APPROVED	3