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

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

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)

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