I have the below table: I need to add a column that captured the request process.
Two option. "Manual" or "Automated"
I will capture the "Manual" else is "Automated"
I have the below sql that captured "Manual" but it only writes to the row where all conditions are true. I need this value for all the rows in a grouping
Criteria is in case expression below in sql
NO ACTION_DT REQUEST_TYPE STATUS_CD ACTION_USER REQUEST_START_DT REQUEST_END_DT REQUEST RNK_GRP_REQUEST
803 5/6/2008 11:07:28 PM CREATE APPROVED CH 5/6/2008 11:07:28 PM 5/6/2008 11:07:28 PM 1 1
803 1/3/2013 10:02:04 AM UPDATE APPROVED TNO 1/3/2013 10:02:04 AM 1/3/2013 10:02:04 AM 2 1
803 4/21/2015 2:20:27 PM UPDATE APPROVED TINO 4/21/2015 2:20:27 PM 4/21/2015 2:20:27 PM 3 1
803 8/1/2017 3:25:01 PM UPDATE PENDING CAD7383 8/1/2017 3:25:01 PM 8/2/2017 9:34:16 AM 4 1
803 8/2/2017 9:34:16 AM UPDATE APPROVED TINO 8/1/2017 3:25:01 PM 8/2/2017 9:34:16 AM 4 2
803 11/13/2018 4:12:36 PM UPDATE NEW TLN 11/13/2018 4:12:36 PM 11/15/2018 9:02:07 AM 5 1
803 11/15/2018 9:02:07 AM UPDATE APPROVED TLN 11/13/2018 4:12:36 PM 11/15/2018 9:02:07 AM 5 2
803 7/15/2019 9:07:32 AM UPDATE NEW WJH 7/15/2019 9:07:32 AM 7/15/2019 9:07:32 AM 6 1
803 7/15/2019 9:07:32 AM UPDATE PENDING WJH 7/15/2019 9:07:32 AM 7/15/2019 9:07:32 AM 6 2
803 7/16/2019 9:23:43 AM UPDATE NEW WJH 7/16/2019 9:23:43 AM 7/16/2019 2:35:47 PM 7 1
803 7/16/2019 2:31:20 PM UPDATE PENDING DXL 7/16/2019 9:23:43 AM 7/16/2019 2:35:47 PM 7 2
803 7/16/2019 2:35:47 PM UPDATE APPROVED DXL 7/16/2019 9:23:43 AM 7/16/2019 2:35:47 PM 7 3
Expected Results: (new column far right) (capturing manual only for now)
NO ACTION_DT REQUEST_TYPE STATUS_CD ACTION_USER REQUEST_START_DT REQUEST_END_DT REQUEST RNK_GRP_REQUEST REQUEST
803 5/6/2008 11:07:28 PM CREATE APPROVED CH 5/6/2008 11:07:28 PM 5/6/2008 11:07:28 PM 1 1
803 1/3/2013 10:02:04 AM UPDATE APPROVED TNO 1/3/2013 10:02:04 AM 1/3/2013 10:02:04 AM 2 1
803 4/21/2015 2:20:27 PM UPDATE APPROVED TINO 4/21/2015 2:20:27 PM 4/21/2015 2:20:27 PM 3 1
803 8/1/2017 3:25:01 PM UPDATE PENDING CAD7383 8/1/2017 3:25:01 PM 8/2/2017 9:34:16 AM 4 1
803 8/2/2017 9:34:16 AM UPDATE APPROVED TINO 8/1/2017 3:25:01 PM 8/2/2017 9:34:16 AM 4 2
803 11/13/2018 4:12:36 PM UPDATE NEW TLN 11/13/2018 4:12:36 PM 11/15/2018 9:02:07 AM 5 1
803 11/15/2018 9:02:07 AM UPDATE APPROVED TLN 11/13/2018 4:12:36 PM 11/15/2018 9:02:07 AM 5 2
803 7/15/2019 9:07:32 AM UPDATE NEW WJH 7/15/2019 9:07:32 AM 7/15/2019 9:07:32 AM 6 1
803 7/15/2019 9:07:32 AM UPDATE PENDING WJH 7/15/2019 9:07:32 AM 7/15/2019 9:07:32 AM 6 2
803 7/16/2019 9:23:43 AM UPDATE NEW WJH 7/16/2019 9:23:43 AM 7/16/2019 2:35:47 PM 7 1 MANUAL
803 7/16/2019 2:31:20 PM UPDATE PENDING DXL 7/16/2019 9:23:43 AM 7/16/2019 2:35:47 PM 7 2 MANUAL
803 7/16/2019 2:35:47 PM UPDATE APPROVED DXL 7/16/2019 9:23:43 AM 7/16/2019 2:35:47 PM 7 3 MANUAL
Current SQL (below)...(focus on last three rows)
How can I get value "manual" for request #7?
request
Manual
(null) --should be Manual
(null) --should be Manual
SQL:
select *
,CASE
WHEN FIRST_VALUE(STATUS_CD) OVER (PARTITION BY NO,REQUEST ORDER BY ACTION_DT)IN ('NEW')
AND COUNT(REQUEST) OVER (PARTITION BY NO,REQUEST ORDER BY REQUEST) > 1
AND LEAD(ACTION_USER) OVER (PARTITION BY NO,REQUEST ORDER BY ACTION_DT) <> ACTION_USER
THEN 'MANUAL' --OVER (PARTITION BY NO,REQUEST ORDER BY ACTION_DT)
ELSE NULL
END REQUEST_PROCESS
from A
Rather than using lead to compare with the next user, you could count how many different users are in the partition/group:
select a.*,
CASE
WHEN FIRST_VALUE(STATUS_CD) OVER (PARTITION BY NO, REQUEST ORDER BY ACTION_DT) = 'NEW'
AND COUNT(REQUEST) OVER (PARTITION BY NO, REQUEST) > 1
AND COUNT(DISTINCT ACTION_USER) OVER (PARTITION BY NO, REQUEST) > 1
THEN 'MANUAL'
ELSE NULL
END AS REQUEST_PROCESS
from A;
NO ACTION_DT REQUES STATUS_C ACTION_ REQUEST_START_DT REQUEST_END_DT REQUEST RNK_GRP_REQUEST REQUES
---------- ------------------- ------ -------- ------- ------------------- ------------------- ---------- --------------- ------
803 2008-05-06 23:07:28 CREATE APPROVED CH 2008-05-06 23:07:28 2008-05-06 23:07:28 1 1
803 2013-01-03 10:02:04 UPDATE APPROVED TNO 2013-01-03 10:02:04 2013-01-03 10:02:04 2 1
803 2015-04-21 14:20:27 UPDATE APPROVED TINO 2015-04-21 14:20:27 2015-04-21 14:20:27 3 1
803 2017-08-01 15:25:01 UPDATE PENDING CAD7383 2017-08-01 15:25:01 2017-08-02 09:34:16 4 1
803 2017-08-02 09:34:16 UPDATE APPROVED TINO 2017-08-01 15:25:01 2017-08-02 09:34:16 4 2
803 2018-11-13 16:12:36 UPDATE NEW TLN 2018-11-13 16:12:36 2018-11-15 09:02:07 5 1
803 2018-11-15 09:02:07 UPDATE APPROVED TLN 2018-11-13 16:12:36 2018-11-15 09:02:07 5 2
803 2019-07-15 09:07:32 UPDATE NEW WJH 2019-07-15 09:07:32 2019-07-15 09:07:32 6 1
803 2019-07-15 09:07:32 UPDATE PENDING WJH 2019-07-15 09:07:32 2019-07-15 09:07:32 6 2
803 2019-07-16 09:23:43 UPDATE NEW WJH 2019-07-16 09:23:43 2019-07-16 14:35:47 7 1 MANUAL
803 2019-07-16 14:31:20 UPDATE PENDING DXL 2019-07-16 09:23:43 2019-07-16 14:35:47 7 2 MANUAL
803 2019-07-16 14:35:47 UPDATE APPROVED DXL 2019-07-16 09:23:43 2019-07-16 14:35:47 7 3 MANUAL
12 rows selected.
I'm not sure you even need the COUNT_REQUEST) check, as that has to be more than one if the user count is.