set schema jemshdp82;

-- Civil types
SELECT YEAR(HCGPL.CONVDATE(AXADDT)) AS FILE_YEAR, AXBDCD AS CASE_TYPE, COUNT(AXCRCD) AS CASES_FILED FROM CCDCSRP
WHERE AXDUST='V' AND AXADDT>=1110101
GROUP BY YEAR(HCGPL.CONVDATE(AXADDT)), AXBDCD;

-- Criminal types
set schema jemshdp82;

WITH EDUCATION_OFFENSES (OFFENSE_CODE, EFFECTIVE_DATE) AS 
(SELECT APB9CD, APJDDT FROM CCAOFRP WHERE (APCCCD LIKE '%ED%' OR APSWCE LIKE '%ED%')),
CASES (CASE_NUMBER, FILE_YEAR, OFFENSE_CODE, OFF_EFF_DATE, CALC_CASE_TYPE) AS
(SELECT AXCRCD, YEAR(HCGPL.CONVDATE(AXADDT)), BBB9CD, BBJDDT, 
(CASE WHEN OFFENSE_CODE IS NULL THEN AXBDCD ELSE 'ED' END) FROM CCDCSRP
LEFT JOIN CCECOCP ON AXCRCD=BBCRCD
LEFT JOIN EDUCATION_OFFENSES ON OFFENSE_CODE=BBB9CD AND EFFECTIVE_DATE=BBJDDT
WHERE (AXDUST='C' OR AXDUST='T') AND AXADDT>=1110101)
SELECT FILE_YEAR AS FILE_YEAR, CALC_CASE_TYPE AS CASE_TYPE, COUNT(DISTINCT CASE_NUMBER) AS CASES_FILED FROM CASES
GROUP BY FILE_YEAR, CALC_CASE_TYPE;