SET SCHEMA JEMSHDP21;

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')),

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 ),

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)

SELECT SUBSTR(CHAR(REFERRAL_DATE, ISO), 1, 7) AS MONTH_YEAR, 
COUNT(*) AS NUMBER_REFERRED, AVG(AGE_AT_REFERRAL) AS AVERAGE_AGE
FROM POST_JUDGMENT_REFERRALS_EXTRA
GROUP BY SUBSTR(CHAR(REFERRAL_DATE, ISO), 1, 7)
ORDER BY SUBSTR(CHAR(REFERRAL_DATE, ISO), 1, 7) ASC;



SET SCHEMA JEMSHDP21;
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')), 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 ), 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) SELECT SUBSTR(CHAR(REFERRAL_DATE, ISO), 1, 7) AS MONTH_YEAR,  COUNT(*) AS NUMBER_REFERRED, AVG(AGE_AT_REFERRAL) AS AVERAGE_AGE FROM POST_JUDGMENT_REFERRALS_EXTRA GROUP BY SUBSTR(CHAR(REFERRAL_DATE, ISO), 1, 7) ORDER BY SUBSTR(CHAR(REFERRAL_DATE, ISO), 1, 7) ASC