SET SCHEMA JEMSHDP52;

WITH
NON_CPF_WARRANT_FILINGS (FILING_CODE) AS (SELECT AWCNCD FROM CCADCRP 
WHERE AWSTCE IN ('ACW', 'CAP', 'BFA', 'AJW', 'JCW', 'WCW')),

CPF_WARRANT_FILINGS (FILING_CODE) AS (SELECT AWCNCD FROM CCADCRP 
WHERE AWSTCE IN ('CPF', 'ACP')),

STATUSES (STATUS_CODE, FINALIZED) AS (SELECT BTE8CD, BTFCSU FROM CCASCRP),

WARRANT_CASES (CASE_NUMBER) AS (SELECT A3CRCD FROM CCECDCP 
WHERE A3CNCD IN (SELECT * FROM NON_CPF_WARRANT_FILINGS) AND A3EHDT=0),

CASES_WITH_EVENTS (CASE_NUMBER, EVENT_CODE, EVENT_DATE) AS (SELECT CASE_NUMBER, A1CDCD, A1AODT FROM
WARRANT_CASES LEFT JOIN CCECSCP ON CASE_NUMBER=A1CRCD 
WHERE A1BVDT=0 AND HCGPL.CONVDATE(A1AODT)>=CURRENT_DATE),

CASES_WITH_NEXT_EVENT (CASE_NUMBER, NEXT_EVENT_DATE, NEXT_EVENT_CODE) AS 
(SELECT WARRANT_CASES.CASE_NUMBER, MIN(EVENT_DATE), EVENT_CODE FROM WARRANT_CASES 
LEFT JOIN CASES_WITH_EVENTS ON WARRANT_CASES.CASE_NUMBER=CASES_WITH_EVENTS.CASE_NUMBER
GROUP BY WARRANT_CASES.CASE_NUMBER, EVENT_DATE, EVENT_CODE),

CASES_WITH_STATUSES (CASE_NUMBER, NEXT_EVENT_DATE, NEXT_EVENT_CODE, FINALIZED) AS
(SELECT CASE_NUMBER, NEXT_EVENT_DATE, NEXT_EVENT_CODE, FINALIZED FROM CASES_WITH_NEXT_EVENT
LEFT JOIN CCDCSRP ON CASE_NUMBER=AXCRCD
LEFT JOIN STATUSES ON AXE8CD=STATUS_CODE)

SELECT NEXT_EVENT_CODE, ASA6TX AS NEXT_EVENT_DESC, 
SUM(CASE FINALIZED WHEN 'Y' THEN 1 ELSE 0 END) AS FINALIZED,
SUM(CASE FINALIZED WHEN 'Y' THEN 0 ELSE 1 END) AS NON_FINALIZED
FROM CASES_WITH_STATUSES
LEFT JOIN CCAEVRP ON NEXT_EVENT_CODE=ASCDCD
GROUP BY NEXT_EVENT_CODE, ASA6TX
ORDER BY COUNT(DISTINCT CASE_NUMBER) DESC;