rej_trans.sql

WITH trans AS (

--trans AS (
  SELECT
    WI_Hist_SQN
    , WI_ID
    , Revision_No
    , Old_Status_Num as Prior_Status_Key
    , LAG(Create_TS, 1) OVER (PARTITION BY WI_ID ORDER BY Create_TS ASC) AS PreviousTime
    , New_Status_Num AS Status_Key
    , Create_TS 
    , LEAD(New_Status_Num, 1) OVER (PARTITION BY WI_ID ORDER BY Create_TS ASC) AS Next_Status_Key
    , LEAD(Create_TS, 1) OVER (PARTITION BY WI_ID ORDER BY Create_TS ASC) AS Next_Status_Start_TS
    , Create_User_ID
  FROM
    CACS.Workitem_History),
    
    tr as
(
    SELECT 
        distinct(TASK_STATUS)
      ,	WI_ID
      --,	task_status
      ,	max(Rejection_Type) as REJECTION_TYPE
      ,	MAX(RETURN_FLAG) as WI_QA_RETURN_FLAG 
      ,	SUM(RETURN_FLAG) as TOTAL_QA_RETURNS
      --,	ROUND(sum(elapsed_time_to_next)/60 ,2) as ELAPSED --sql server logic
      , ROUND(SUM(ELAPSED_NEXT), 2)  as ELAPSED
      ,	MAX(next_status_start_ts) as LAST_MODIFIED
      , MAX(return_time) as RETURN_TIME

    FROM
    (
         SELECT
            WT.WI_ID,
            WT.next_status_start_ts,
            Wt.create_ts,
            WT.PreviousTime,
            ((86400 * (TRUNC(Next_Status_Start_TS) - TRUNC(Create_TS)) + --Day Difference converted to seconds
            TO_NUMBER(TO_CHAR(Next_Status_Start_TS,'sssss')) - TO_NUMBER(TO_CHAR(Create_TS,'sssss')) --TS converted to seconds
            )/60) Elapsed_Next
            --WT.*
            /*
            ,WI_HIST_SQN
            ,WI_ID
            ,REVISION_NO
            ,OLD_STATUS_NUM
            ,NEW_STATUS_NUM
            ,CREATE_USER_ID
            ,CREATE_TS
            ,UPDATE_USER_ID
            ,UPDATE_TS
            ,LAG(CREATE_TS) OVER (PARTITION BY WI_ID ORDER BY WI_HIST_SQN) as PreviousTime
            */
            -- Above were fields selected from original oracle wi history query - Need lag ts to get previous time
           ,	CASE
              WHEN status_key = 1164 or (prior_status_key = 3860 and status_key = 610 and next_status_key = 1169) then 'Processing'
              WHEN status_key in (4325, 4331, 4319) and next_status_key in (1165, 4320, 4322, 4326, 4328, 4332, 4334) then 'QA'
              ELSE NULL
            END AS Task_Status
          ,	CASE
              WHEN next_status_key = 1165 and status_key != 1164 THEN 1 ELSE 0
            END AS RETURN_FLAG
          , 	( SELECT
              max(
              CASE
                WHEN	   (Prior_Status_Key NOT IN (1302, 1164) AND Status_Key = 1170 AND Next_Status_Key IS NULL)
                    OR (Status_Key = 4422 AND Next_Status_Key = 1170)
                    OR (Prior_Status_Key = 4426 AND status_key = 4422 AND next_status_key IS NULL)
                    OR (Prior_Status_Key = 4422 AND status_key = 4423 AND next_status_key IS NULL)
                    OR (Prior_Status_Key = 3857 AND status_key = 3860 AND next_status_key IS NULL)
                    THEN 'Auto/Bundle Reject'
                WHEN (Status_Key = 1163 AND Next_Status_Key = 1170) THEN 'Reject from Queue'
                WHEN	(Status_Key = 1164 AND Next_Status_Key = 1170)
                    OR (Prior_Status_Key = 1163 AND status_key = 1164 AND next_status_key IS NULL)
                    THEN 'Process Rejection'
                WHEN (prior_status_key IS NULL AND status_key = 1170 AND next_status_key IS NULL) THEN 'Unknown Rejection'
                ELSE NULL
               END ) AS Rejection_Type 
               FROM trans WTT
               WHERE WTT.WI_ID = WT.WI_ID) AS REJECTION_TYPE
          ,	 ( SELECT MAX(CASE WHEN next_status_key = 1165 THEN next_status_start_ts end) as next_status_start_ts
               FROM trans WTT
               WHERE WTT.WI_ID = WT.WI_ID) AS RETURN_TIME 

         FROM trans WT 

         --WHERE WT.workflow_id = 27

         ) hist
    WHERE TASK_STATUS is NOT NULL or Rejection_Type is not null
    GROUP BY WI_ID, task_status)
    
   select * from tr