SET SCHEMA JEMSHDP41;

WITH

EVENTS_TO_IGNORE (EVENT_CODE) AS (SELECT ASCDCD FROM CCAEVRP 
WHERE ASA6TX LIKE 'PENDING%' OR ASA6TX LIKE '%REVIEW%' OR ASCDCD IN ('TIME', 'HOLD', 'DPSFTA DUE')),

PRE_JUDGMENT_REFERRALS (CASE_NUMBER, REFERRAL_DATE) AS 
(SELECT BYCRCD, MIN(BYDMDU) FROM CCFCRPP LEFT JOIN CCECOCP ON BYCRCD=BBCRCD 
WHERE BYX8CF IN ('NAM') AND BBB9CD NOT IN ('820772', '820746') 
GROUP BY BYCRCD HAVING MIN(BYDMDU)>=1051001 AND MIN(BYDMDU)<=1071231 ),

POST_JUDGMENT_REFERRALS (CASE_NUMBER, REFERRAL_DATE) AS 
(SELECT BYCRCD, MIN(BYDMDU) FROM CCFCRPP WHERE BYX8CF IN ('DJ1') 
GROUP BY BYCRCD HAVING MIN(BYDMDU)>=1051001 AND MIN(BYDMDU)<=1071231 ),

PRE_JUDGMENT_REFERRALS_EVENT (CASE_NUMBER, MISSED_APPEARANCE_DATE) AS 
(SELECT CASE_NUMBER, MAX(A1AODT) FROM PRE_JUDGMENT_REFERRALS 
LEFT JOIN CCECSCP ON CASE_NUMBER=A1CRCD 
WHERE A1OBCD IN ('FTA', 'NS', 'VPA') AND A1CDCD NOT IN (SELECT * FROM EVENTS_TO_IGNORE) 
AND A1AODT < REFERRAL_DATE GROUP BY CASE_NUMBER HAVING MAX(A1AODT)!=0),

PRE_JUDGMENT_REFERRALS_EXTRA (CASE_NUMBER, MISSED_APPEARANCE_DATE, REFERRAL_DATE, AGE_AT_REFERRAL) AS 
(SELECT PRE_JUDGMENT_REFERRALS_EVENT.CASE_NUMBER, HCGPL.CONVDATE(MISSED_APPEARANCE_DATE), HCGPL.CONVDATE(REFERRAL_DATE), 
DAYS(HCGPL.CONVDATE(REFERRAL_DATE)) - DAYS(HCGPL.CONVDATE(MISSED_APPEARANCE_DATE)) 
FROM PRE_JUDGMENT_REFERRALS_EVENT LEFT JOIN PRE_JUDGMENT_REFERRALS 
ON PRE_JUDGMENT_REFERRALS_EVENT.CASE_NUMBER = PRE_JUDGMENT_REFERRALS.CASE_NUMBER)

POST_JUDGMENT_REFERRALS_EXTRA (CASE_NUMBER, JUDGMENT_DATE, REFERRAL_DATE, AGE_AT_REFERRAL) AS 
(SELECT CASE_NUMBER, HCGPL.CONVDATE(BBKNDT), HCGPL.CONVDATE(REFERRAL_DATE), 
DAYS(HCGPL.CONVDATE(REFERRAL_DATE)) - DAYS(HCGPL.CONVDATE(BBKNDT)) 
FROM POST_JUDGMENT_REFERRALS LEFT JOIN CCECOCP ON CASE_NUMBER=BBCRCD),

PRE_JUDGMENT_TOTALS (TOTAL_REFERRED, C_0_TO_30_DAYS, C_31_TO_60_DAYS, C_61_TO_90_DAYS, 
C_91_TO_180_DAYS, C_181_TO_364_DAYS, C_1_TO_2_YEARS, C_2_TO_3_YEARS, 
C_3_TO_4_YEARS, C_4_TO_5_YEARS, MORE_THAN_5_YEARS) AS 
(SELECT SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 0 AND 9999 THEN 1 ELSE 0 END), 
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 0 AND 30 THEN 1 ELSE 0 END), 
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 31 AND 60 THEN 1 ELSE 0 END), 
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 61 AND 90 THEN 1 ELSE 0 END), 
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 91 AND 180 THEN 1 ELSE 0 END), 
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 181 AND 364 THEN 1 ELSE 0 END), 
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 365 AND 729 THEN 1 ELSE 0 END), 
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 730 AND 1094 THEN 1 ELSE 0 END), 
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 1095 AND 1459 THEN 1 ELSE 0 END), 
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 1460 AND 1824 THEN 1 ELSE 0 END), 
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 1825 AND 9999 THEN 1 ELSE 0 END) 
FROM PRE_JUDGMENT_REFERRALS_EXTRA),

POST_JUDGMENT_TOTALS (TOTAL_REFERRED, C_0_TO_30_DAYS, C_31_TO_60_DAYS, C_61_TO_90_DAYS,
C_91_TO_180_DAYS, C_181_TO_364_DAYS, C_1_TO_2_YEARS, C_2_TO_3_YEARS,
C_3_TO_4_YEARS, C_4_TO_5_YEARS, MORE_THAN_5_YEARS) AS
(SELECT SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 0 AND 9999 THEN 1 ELSE 0 END),
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 0 AND 30 THEN 1 ELSE 0 END),
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 31 AND 60 THEN 1 ELSE 0 END),
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 61 AND 90 THEN 1 ELSE 0 END),
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 91 AND 180 THEN 1 ELSE 0 END),
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 181 AND 364 THEN 1 ELSE 0 END),
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 365 AND 729 THEN 1 ELSE 0 END),
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 730 AND 1094 THEN 1 ELSE 0 END),
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 1095 AND 1459 THEN 1 ELSE 0 END),
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 1460 AND 1824 THEN 1 ELSE 0 END),
SUM(CASE WHEN AGE_AT_REFERRAL BETWEEN 1825 AND 9999 THEN 1 ELSE 0 END)
FROM POST_JUDGMENT_REFERRALS_EXTRA)

SELECT 'PRE' AS TYPE, PRE_JUDGMENT_TOTALS.* FROM PRE_JUDGMENT_TOTALS
UNION
SELECT 'POST' AS TYPE, POST_JUDGMENT_TOTALS.* FROM POST_JUDGMENT_TOTALS;