set schema jemshdp82;

WITH
ARRESTING_AGENCIES (AGENCY_CODE, AGENCY_NAME) AS (SELECT AKBTCD, AKAWTX FROM CCAAARP),
CASES (CASE_NUMBER, FILE_DATE, ARRESTING_AGENCY, OFFENSE_CODE) AS
(SELECT AXCRCD, AXADDT, AXBTCD, BBB9CD FROM CCDCSRP LEFT JOIN CCECOCP ON AXCRCD=BBCRCD
WHERE AXADDT>=1090101 AND AXADDT<=1091231 AND BBB9CD='530006')
SELECT AGENCY_CODE, AGENCY_NAME, COUNT(CASE_NUMBER) AS FAILURE_TO_ATTEND 
FROM ARRESTING_AGENCIES
LEFT JOIN CASES ON ARRESTING_AGENCY=AGENCY_CODE
GROUP BY AGENCY_CODE, AGENCY_NAME
ORDER BY AGENCY_CODE, AGENCY_NAME;