SET SCHEMA JEMSHDP21;

WITH
-- GET A TABLE OF ALL COSTS AND THE "COST TYPE" (COST, FINE, BOND/DEP, REFUND).
COSTS (COST_CODE, COST_TYPE) AS (SELECT AIBPCD, AICJST FROM CCAFERP),

-- GET A TABLE OF THE FILINGS THAT ARE WARRANTS, BASED ON THE "TYPE OF WARRANT" FIELD.
WARRANT_FILINGS (FILING_CODE, FILING_DESC, WARRANT_TYPE) AS 
(SELECT AWCNCD, AWBBTX, AWSTCE FROM CCADCRP WHERE AWSTCE IN ('ACW', 'CAP', 'BFA', 'CPF', 'ACP', 'AJW', 'JCW', 'WCW')),

-- GET A LIST OF CASES (WITH PID, PSN, AND PSSN) WITH UNCOMPLETED WARRANTS.
WARRANT_CASES (CASE_NUMBER, PERSON_ID, PSN, PSSN, FILING_CODE, FILING_DESC, WARRANT_TYPE, WARRANT_DATE, DAYS_AGO) AS 
(SELECT A3CRCD, A5EKNC, A5B1NB, A5DONB, A3CNCD, FILING_DESC, WARRANT_TYPE, HCGPL.CONVDATE(A3AQDT), DAYS(CURRENT_DATE) - DAYS(HCGPL.CONVDATE(A3AQDT)) FROM CCECDCP 
LEFT JOIN CCEPTCP ON A3CRCD=A5CRCD
LEFT JOIN WARRANT_FILINGS ON A3CNCD=FILING_CODE
WHERE A3CNCD IN (SELECT FILING_CODE FROM WARRANT_FILINGS) AND A3EHDT=0 AND A5E5ST='D'),

-- GET A LIST OF THE CASES IN THE WARRANT_CASES LIST, THIS TIME WITH OBLIGATION DATA FOR THE DEFENDANT.
CASES_WITH_OBLIGATIONS (CASE_NUMBER, PERSON_ID, PSN, PSSN, FILING_CODE, FILING_DESC, WARRANT_TYPE, WARRANT_DATE, DAYS_AGO, OBL_CODE, OBL_PRI, OBL_PRI_SEQ, OBL_SEQ) AS
(SELECT CASE_NUMBER, PERSON_ID, PSN, PSSN, FILING_CODE, FILING_DESC, WARRANT_TYPE, WARRANT_DATE, DAYS_AGO, FTRSCE, FTNISU, FTKQNC, FTKENC FROM WARRANT_CASES
LEFT JOIN CCEOBCP ON CASE_NUMBER=FTCRCD AND PSN=FTB1NB AND PSSN=FTDONB),

-- COMBINE THE TWO LISTS, THIS TIME COLLECTING DATA ON ASSESSMENTS AND DISBURSEMENTS.
CASES_WITH_COSTS
(CASE_NUMBER, PERSON_ID, FILING_CODE, FILING_DESC, WARRANT_TYPE, WARRANT_DATE, DAYS_AGO, FINES_ASSESSED, FINES_DISBURSED, 
COSTS_ASSESSED, COSTS_DISBURSED, BOND_DEP_ASSESSED, BOND_DEP_DISBURSED, REFUNDS_ASSESSED, REFUNDS_DISBURSED) AS
(SELECT CASE_NUMBER, PERSON_ID, FILING_CODE, FILING_DESC, WARRANT_TYPE, WARRANT_DATE, DAYS_AGO, 
SUM(CASE WHEN COST_TYPE='F' THEN CACIVA ELSE 0 END),
SUM(CASE WHEN COST_TYPE='F' THEN CACJVA ELSE 0 END),
SUM(CASE WHEN COST_TYPE='C' THEN CACIVA ELSE 0 END),
SUM(CASE WHEN COST_TYPE='C' THEN CACJVA ELSE 0 END),
SUM(CASE WHEN COST_TYPE='D' THEN CACIVA ELSE 0 END),
SUM(CASE WHEN COST_TYPE='D' THEN CACJVA ELSE 0 END),
SUM(CASE WHEN COST_TYPE='W' THEN CACIVA ELSE 0 END),
SUM(CASE WHEN COST_TYPE='W' THEN CACJVA ELSE 0 END)
FROM CASES_WITH_OBLIGATIONS 
LEFT JOIN CCECBCP ON CASE_NUMBER=CACRCD AND OBL_PRI=CANISU AND OBL_PRI_SEQ=CAKQNC AND OBL_SEQ=CAKENC
LEFT JOIN COSTS ON CABPCD=COST_CODE
GROUP BY CASE_NUMBER, PERSON_ID, FILING_CODE, FILING_DESC, WARRANT_TYPE, WARRANT_DATE, DAYS_AGO)

-- This statement will list all open warrants along with Person ID, Filing Code, and assessment/disbursement info.
--SELECT * FROM CASES_WITH_COSTS;

-- This statement will summarize data on warrants by type.
--SELECT WARRANT_TYPE, WARRANT_TYPE, COUNT(*) AS TOTAL_WARRANTS, COUNT(DISTINCT CASE_NUMBER) AS TOTAL_CASES, 
--COUNT(DISTINCT PERSON_ID) AS UNIQUE_PIDS,
--SUM(CASE WHEN DAYS_AGO BETWEEN 0 AND 30 THEN 1 ELSE 0 END) AS C_0_TO_30DAYS,
--SUM(CASE WHEN DAYS_AGO BETWEEN 31 AND 60 THEN 1 ELSE 0 END) AS C_31DAYS_TO_60DAYS,
--SUM(CASE WHEN DAYS_AGO BETWEEN 61 AND 90 THEN 1 ELSE 0 END) AS C_61DAYS_TO_90DAYS,
--SUM(CASE WHEN DAYS_AGO BETWEEN 91 AND 180 THEN 1 ELSE 0 END) AS C_91DAYS_TO_180DAYS,
--SUM(CASE WHEN DAYS_AGO BETWEEN 181 AND 365 THEN 1 ELSE 0 END) AS C_180DAYS_TO_1YR,
--SUM(CASE WHEN DAYS_AGO BETWEEN 366 AND 730 THEN 1 ELSE 0 END) AS C_1YR_TO_2YRS,
--SUM(CASE WHEN DAYS_AGO BETWEEN 731 AND 1095 THEN 1 ELSE 0 END) AS C_2YRS_TO_3YRS,
--SUM(CASE WHEN DAYS_AGO BETWEEN 1096 AND 1460 THEN 1 ELSE 0 END) AS C_3YRS_TO_4YRS,
--SUM(CASE WHEN DAYS_AGO BETWEEN 1461 AND 1825 THEN 1 ELSE 0 END) AS C_4YRS_TO_5YRS,
--SUM(CASE WHEN DAYS_AGO BETWEEN 1826 AND 9999 THEN 1 ELSE 0 END) AS C_5YRS_AND_OLDER,
--SUM(FINES_ASSESSED) AS FINES_ASSESSED,
--SUM(FINES_DISBURSED) AS FINES_DISBURSED,
--SUM(FINES_ASSESSED - FINES_DISBURSED) AS FINES_DUE,
--SUM(COSTS_ASSESSED) AS COSTS_ASSESSED,
--SUM(COSTS_DISBURSED) AS COSTS_DISBURSED,
--SUM(COSTS_ASSESSED - COSTS_DISBURSED) AS COSTS_DUE,
--SUM(BOND_DEP_ASSESSED) AS BOND_DEP_ASSESSED,
--SUM(BOND_DEP_DISBURSED) AS BOND_DEP_DISBURSED,
--SUM(REFUNDS_ASSESSED) AS REFUNDS_ASSESSED,
--SUM(REFUNDS_DISBURSED) AS REFUNDS_DISBURSED
--FROM CASES_WITH_COSTS
--GROUP BY WARRANT_TYPE;

-- This statement will summarize data on warrants by filing code.
SELECT FILING_CODE, FILING_DESC, COUNT(*) AS TOTAL_WARRANTS, COUNT(DISTINCT CASE_NUMBER) AS TOTAL_CASES, 
COUNT(DISTINCT PERSON_ID) AS UNIQUE_PIDS,
SUM(CASE WHEN DAYS_AGO BETWEEN 0 AND 30 THEN 1 ELSE 0 END) AS C_0_TO_30DAYS,
SUM(CASE WHEN DAYS_AGO BETWEEN 31 AND 60 THEN 1 ELSE 0 END) AS C_31DAYS_TO_60DAYS,
SUM(CASE WHEN DAYS_AGO BETWEEN 61 AND 90 THEN 1 ELSE 0 END) AS C_61DAYS_TO_90DAYS,
SUM(CASE WHEN DAYS_AGO BETWEEN 91 AND 180 THEN 1 ELSE 0 END) AS C_91DAYS_TO_180DAYS,
SUM(CASE WHEN DAYS_AGO BETWEEN 181 AND 365 THEN 1 ELSE 0 END) AS C_180DAYS_TO_1YR,
SUM(CASE WHEN DAYS_AGO BETWEEN 366 AND 730 THEN 1 ELSE 0 END) AS C_1YR_TO_2YRS,
SUM(CASE WHEN DAYS_AGO BETWEEN 731 AND 1095 THEN 1 ELSE 0 END) AS C_2YRS_TO_3YRS,
SUM(CASE WHEN DAYS_AGO BETWEEN 1096 AND 1460 THEN 1 ELSE 0 END) AS C_3YRS_TO_4YRS,
SUM(CASE WHEN DAYS_AGO BETWEEN 1461 AND 1825 THEN 1 ELSE 0 END) AS C_4YRS_TO_5YRS,
SUM(CASE WHEN DAYS_AGO BETWEEN 1826 AND 9999 THEN 1 ELSE 0 END) AS C_5YRS_AND_OLDER,
SUM(FINES_ASSESSED) AS FINES_ASSESSED,
SUM(FINES_DISBURSED) AS FINES_DISBURSED,
SUM(FINES_ASSESSED - FINES_DISBURSED) AS FINES_DUE,
SUM(COSTS_ASSESSED) AS COSTS_ASSESSED,
SUM(COSTS_DISBURSED) AS COSTS_DISBURSED,
SUM(COSTS_ASSESSED - COSTS_DISBURSED) AS COSTS_DUE,
SUM(BOND_DEP_ASSESSED) AS BOND_DEP_ASSESSED,
SUM(BOND_DEP_DISBURSED) AS BOND_DEP_DISBURSED,
SUM(REFUNDS_ASSESSED) AS REFUNDS_ASSESSED,
SUM(REFUNDS_DISBURSED) AS REFUNDS_DISBURSED
FROM CASES_WITH_COSTS
GROUP BY FILING_CODE, FILING_DESC;