SET SCHEMA JEMSHDP11;

WITH 
-- This is a list of all cost codes that have an allocation to a non-county agency.
NON_COUNTY_COST_CODES (COST_CODE, COST_DESC, COST_FINE, NON_COUNTY_PERCENT) AS 
(SELECT AIBPCD, AIARTX, AICJST, TXGMNF FROM CCAFERP LEFT JOIN CCBSCCP ON TXBPCD=AIBPCD WHERE TXGMNF IS NOT NULL
UNION
SELECT AIBPCD, AIARTX, AICJST, B2IDNF FROM CCAFERP LEFT JOIN CCBSFNP ON B2BPCD=AIBPCD WHERE B2IDNF IS NOT NULL),

-- This is a list of all cost codes that do not have an allocation to a non-county agency.
COUNTY_COST_CODES (COST_CODE, COST_DESC, COST_FINE, NON_COUNTY_PERCENT) AS 
(SELECT AIBPCD, AIARTX, AICJST, 0 FROM CCAFERP WHERE AIBPCD NOT IN (SELECT COST_CODE FROM NON_COUNTY_COST_CODES)),

-- This is a synthesis of the two tables above.
ALL_COST_CODES (COST_CODE, COST_DESC, COST_FINE, NON_COUNTY_PERCENT) AS 
(SELECT * FROM NON_COUNTY_COST_CODES UNION SELECT * FROM COUNTY_COST_CODES),

-- This table contains every citation number written in the filed date range
-- along with the case numbers.
CITATIONS_WRITTEN (CITATION_NUMBER, CASE_NUMBER) AS
(SELECT DISTINCT BBBOCE, BBCRCD FROM CCECOCP LEFT JOIN CCDCSRP ON AXCRCD=BBCRCD WHERE
-- PARM: Filed Date Range
(AXADDT>=1090101 AND AXADDT<=1091231)
-- PARM: Offense Date Range
--(AXGODT>=1090101 AND AXGODT<=1090131)
-- PARM: Agency
AND (AXBTCD IN ('01', '02', '03', '04', '05', '06', '07', '08'))
-- PARM: Case Type
AND (AXBDCD IN ('TR'))
),

-- This table is a subset of the CITATIONS_WRITTEN table.  You can narrow down the set of citations/cases
-- here.
MATCHING_CASES (CITATION_NUMBER, CASE_NUMBER) AS
(SELECT CITATION_NUMBER, CASE_NUMBER FROM CITATIONS_WRITTEN
LEFT JOIN CCECOCP ON CITATION_NUMBER=BBBOCE AND CASE_NUMBER=BBCRCD
LEFT JOIN CCDCSRP ON CASE_NUMBER=AXCRCD WHERE
-- PARM: Disposed (BBA1DT!=0) / Non-Disposed (BBA1DT=0) / All (BBA1DT!=0 AND BBA1DT=0)
(BBA1DT!=0)
-- PARM: Offense List
AND (BBB9CD IN ('820104', '820164', '821132'))
),

-- This table contains all non-voided payments on the matching citations/cases list.
PAYMENTS (CITATION_NUMBER, CASE_NUMBER, RECEIPT_DATE, RECEIPT_NUMBER, RECEIPT_SEQ_NBR, PAYMENT_AMOUNT) AS
(SELECT CITATION_NUMBER, CASE_NUMBER, BEA6DT, BEDANB, BEC9NB, BEAMVA FROM MATCHING_CASES LEFT JOIN CCFTRCP ON CASE_NUMBER=BECRCD
WHERE BED3DT=0
-- PARM: Monetary (BEDANB!=0) / Non-Monetary (BEDANB=0)/ All (BEDANB!=0 && BEDANB=0)
AND (BEDANB!=0)
),

-- This table contains all non-voided disbursements on the matching citations/cases/payments list.
-- Also join to the ALL_COST_CODES table and get the percentages that will be allocated to county/non-county.
DISBURSEMENTS (CITATION_NUMBER, CASE_NUMBER, RECEIPT_DATE, RECEIPT_NUMBER, RECEIPT_SEQ_NBR, PAYMENT_AMOUNT, COST_CODE, DISB_DATE, DISB_AMT, COST_FINE, COUNTY_PERCENT, NON_COUNTY_PERCENT) AS
(SELECT CITATION_NUMBER, CASE_NUMBER, RECEIPT_DATE, RECEIPT_NUMBER, RECEIPT_SEQ_NBR, PAYMENT_AMOUNT, C2V4CD, C2GQDT, C2DSVA, COST_FINE, (CASE WHEN (NON_COUNTY_PERCENT > 0.00) THEN 1 - (NON_COUNTY_PERCENT * .01) ELSE 1 END), (CASE WHEN (NON_COUNTY_PERCENT > 0.00) THEN (NON_COUNTY_PERCENT * .01) ELSE 0 END) FROM PAYMENTS 
LEFT JOIN CCFCDCP ON RECEIPT_DATE=C2A6DT AND RECEIPT_NUMBER=C2DANB AND RECEIPT_SEQ_NBR=C2C9NB
LEFT JOIN ALL_COST_CODES ON C2V4CD=COST_CODE
WHERE C2H1DT=0),

-- This table takes the data in the DISBURSEMENTS table and adds new columns to calculate how much money
-- goes to county and non-county based on the disbursement amount and percentages.
DISBURSEMENTS_CALCULATED (CITATION_NUMBER, CASE_NUMBER, RECEIPT_DATE, RECEIPT_NUMBER, RECEIPT_SEQ_NBR, PAYMENT_AMOUNT, COST_CODE, DISB_DATE, DISB_AMT, COST_FINE, COUNTY_PERCENT, NON_COUNTY_PERCENT, COUNTY, NON_COUNTY) AS
(SELECT DISBURSEMENTS.*, (DISB_AMT * COUNTY_PERCENT), (DISB_AMT * NON_COUNTY_PERCENT) FROM DISBURSEMENTS),

-- This table calculates individual columns for cost and fines, county and non-county, along with refunds
-- and bond/deposits.  To populate the columns, I use the cost/fine field along with the county / non-county field.
TOTALS (CITATION_NUMBER, CASE_NUMBER, TOTAL_REVENUE, COUNTY_COSTS, NON_COUNTY_COSTS, COUNTY_FINES, NON_COUNTY_FINES, REFUNDED, BOND_DEPOSIT) AS
(SELECT CITATION_NUMBER, CASE_NUMBER, SUM(DISB_AMT), SUM(CASE COST_FINE WHEN 'C' THEN COUNTY ELSE 0 END), SUM(CASE COST_FINE WHEN 'C' THEN NON_COUNTY ELSE 0 END),
SUM(CASE COST_FINE WHEN 'F' THEN COUNTY ELSE 0 END), SUM(CASE COST_FINE WHEN 'F' THEN NON_COUNTY ELSE 0 END),
SUM(CASE COST_FINE WHEN 'W' THEN (COUNTY + NON_COUNTY) ELSE 0 END), SUM(CASE COST_FINE WHEN 'D' THEN (COUNTY + NON_COUNTY) ELSE 0 END)
FROM DISBURSEMENTS_CALCULATED GROUP BY CITATION_NUMBER, CASE_NUMBER)

-- This calculates the total revenue figures on all cases.
SELECT 
(SELECT COUNT(DISTINCT CITATION_NUMBER) FROM CITATIONS_WRITTEN) AS TOTAL_CITATIONS, 
(SELECT COUNT(DISTINCT CASE_NUMBER) FROM CITATIONS_WRITTEN) AS TOTAL_CASES,
(SELECT COUNT(DISTINCT CASE_NUMBER) FROM MATCHING_CASES) AS TOTAL_MATCHING_CASES,
SUM(TOTAL_REVENUE) AS TOTAL_REVENUE, 
SUM(COUNTY_COSTS) AS COUNTY_COSTS, 
SUM(NON_COUNTY_COSTS) AS NON_COUNTY_COSTS,
SUM(COUNTY_FINES) AS COUNTY_FINES, 
SUM(NON_COUNTY_FINES) AS NON_COUNTY_FINES
SUM(REFUNDED) AS REFUNDED,
SUM(BOND_DEPOSIT) AS BOND_DEPOSIT
FROM TOTALS;