WITH
SUBTYPES (CASE_TYPE, CASE_SUBTYPE, DESCRIPTION) AS
(SELECT BCBDCD, BCFZCD, BCB3TX FROM JEMSHDP31.CCBSTRP WHERE BCBDCD IN ('CV', 'SC', 'FD', 'EV')),
JP2_COMBINED (COURT, CASE_TYPE, CASE_SUBTYPE, TOTAL) AS
(
SELECT 'JP 3-1' AS COURT, CASE_TYPE, CASE_SUBTYPE, COUNT(AXCRCD) AS TOTAL FROM SUBTYPES LEFT JOIN JEMSHDP31.CCDCSRP ON 
AXBDCD=CASE_TYPE AND AXFZCD=CASE_SUBTYPE
GROUP BY CASE_TYPE, CASE_SUBTYPE
UNION
SELECT 'JP 3-2' AS COURT, CASE_TYPE, CASE_SUBTYPE, COUNT(AXCRCD) AS TOTAL FROM SUBTYPES LEFT JOIN JEMSHDP32.CCDCSRP ON 
AXBDCD=CASE_TYPE AND AXFZCD=CASE_SUBTYPE
GROUP BY CASE_TYPE, CASE_SUBTYPE
UNION
SELECT 'JP 4-1' AS COURT, CASE_TYPE, CASE_SUBTYPE, COUNT(AXCRCD) AS TOTAL FROM SUBTYPES LEFT JOIN JEMSHDP41.CCDCSRP ON 
AXBDCD=CASE_TYPE AND AXFZCD=CASE_SUBTYPE
GROUP BY CASE_TYPE, CASE_SUBTYPE
UNION
SELECT 'JP 4-2' AS COURT, CASE_TYPE, CASE_SUBTYPE, COUNT(AXCRCD) AS TOTAL FROM SUBTYPES LEFT JOIN JEMSHDP42.CCDCSRP ON 
AXBDCD=CASE_TYPE AND AXFZCD=CASE_SUBTYPE
GROUP BY CASE_TYPE, CASE_SUBTYPE
UNION
SELECT 'JP 7-1' AS COURT, CASE_TYPE, CASE_SUBTYPE, COUNT(AXCRCD) AS TOTAL FROM SUBTYPES LEFT JOIN JEMSHDP71.CCDCSRP ON 
AXBDCD=CASE_TYPE AND AXFZCD=CASE_SUBTYPE
GROUP BY CASE_TYPE, CASE_SUBTYPE
UNION
SELECT 'JP 7-2' AS COURT, CASE_TYPE, CASE_SUBTYPE, COUNT(AXCRCD) AS TOTAL FROM SUBTYPES LEFT JOIN JEMSHDP72.CCDCSRP ON 
AXBDCD=CASE_TYPE AND AXFZCD=CASE_SUBTYPE
GROUP BY CASE_TYPE, CASE_SUBTYPE
UNION
SELECT 'JP 8-1' AS COURT, CASE_TYPE, CASE_SUBTYPE, COUNT(AXCRCD) AS TOTAL FROM SUBTYPES LEFT JOIN JEMSHDP81.CCDCSRP ON 
AXBDCD=CASE_TYPE AND AXFZCD=CASE_SUBTYPE
GROUP BY CASE_TYPE, CASE_SUBTYPE
UNION
SELECT 'JP 8-2' AS COURT, CASE_TYPE, CASE_SUBTYPE, COUNT(AXCRCD) AS TOTAL FROM SUBTYPES LEFT JOIN JEMSHDP82.CCDCSRP ON 
AXBDCD=CASE_TYPE AND AXFZCD=CASE_SUBTYPE
GROUP BY CASE_TYPE, CASE_SUBTYPE
)
SELECT CASE_TYPE, CASE_SUBTYPE, 
sum(CASE WHEN COURT='JP 3-1' THEN TOTAL ELSE 0 END) AS "JP 3-1", 
sum(CASE WHEN COURT='JP 3-2' THEN TOTAL ELSE 0 END) AS "JP 3-2",
sum(CASE WHEN COURT='JP 4-1' THEN TOTAL ELSE 0 END) AS "JP 4-1", 
sum(CASE WHEN COURT='JP 4-2' THEN TOTAL ELSE 0 END) AS "JP 4-2",
sum(CASE WHEN COURT='JP 7-1' THEN TOTAL ELSE 0 END) AS "JP 7-1", 
sum(CASE WHEN COURT='JP 7-2' THEN TOTAL ELSE 0 END) AS "JP 7-2",
sum(CASE WHEN COURT='JP 8-1' THEN TOTAL ELSE 0 END) AS "JP 8-1", 
sum(CASE WHEN COURT='JP 8-2' THEN TOTAL ELSE 0 END) AS "JP 8-2"
FROM JP2_COMBINED
GROUP BY CASE_TYPE, CASE_SUBTYPE