Pull apart F460 Summary totals

create_f460_schedule_a.sql

-- Monetary Contributions Received
CREATE TABLE f460_schedule_a AS
SELECT 
    cvr."FILING_ID" as filing_id,
    cvr."AMEND_ID" as amend_id,
    itemd."AMOUNT_A" as itemized_total,
    unitemd."AMOUNT_A" as unitemized_total,
    combined."AMOUNT_A" as combined_total
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
-- get the itemized total
LEFT JOIN "SMRY_CD" itemd
ON cvr."FILING_ID" = itemd."FILING_ID"
AND cvr."AMEND_ID" = itemd."AMEND_ID"
AND UPPER(itemd."FORM_TYPE") = 'A'
AND itemd."LINE_ITEM" = '1'
-- get the unitemized total
LEFT JOIN "SMRY_CD" unitemd
ON cvr."FILING_ID" = unitemd."FILING_ID"
AND cvr."AMEND_ID" = unitemd."AMEND_ID"
AND UPPER(unitemd."FORM_TYPE") = 'A'
AND unitemd."LINE_ITEM" = '2'
-- get the combined total
LEFT JOIN "SMRY_CD" combined
ON cvr."FILING_ID" = combined."FILING_ID"
AND cvr."AMEND_ID" = combined."AMEND_ID"
AND UPPER(combined."FORM_TYPE") = 'A'
AND combined."LINE_ITEM" = '3'
WHERE cvr."FORM_TYPE" = 'F460';

create_f460_schedule_c.sql

-- Non-Monetary Contributions Received
CREATE TABLE f460_schedule_c AS
SELECT 
    cvr."FILING_ID" as filing_id,
    cvr."AMEND_ID" as amend_id,
    itemd."AMOUNT_A" as itemized_total,
    unitemd."AMOUNT_A" as unitemized_total,
    combined."AMOUNT_A" as combined_total
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
-- get the itemized total
LEFT JOIN "SMRY_CD" itemd
ON cvr."FILING_ID" = itemd."FILING_ID"
AND cvr."AMEND_ID" = itemd."AMEND_ID"
AND UPPER(itemd."FORM_TYPE") = 'C'
AND itemd."LINE_ITEM" = '1'
-- get the unitemized total
LEFT JOIN "SMRY_CD" unitemd
ON cvr."FILING_ID" = unitemd."FILING_ID"
AND cvr."AMEND_ID" = unitemd."AMEND_ID"
AND UPPER(unitemd."FORM_TYPE") = 'C'
AND unitemd."LINE_ITEM" = '2'
-- get the combined total
LEFT JOIN "SMRY_CD" combined
ON cvr."FILING_ID" = combined."FILING_ID"
AND cvr."AMEND_ID" = combined."AMEND_ID"
AND UPPER(combined."FORM_TYPE") = 'C'
AND combined."LINE_ITEM" = '3'
-- get the summary sheet totals
LEFT JOIN "SMRY_CD" sum_sheet
ON cvr."FILING_ID" = sum_sheet."FILING_ID"
AND cvr."AMEND_ID" = sum_sheet."AMEND_ID"
AND UPPER(sum_sheet."FORM_TYPE") = 'F460'
AND sum_sheet."LINE_ITEM" = '4'
WHERE cvr."FORM_TYPE" = 'F460';

create_f460_schedule_d.sql

-- Summary of Expenditures Supporting/Opposing Other Candidates, Measures and Committees 
CREATE TABLE f460_schedule_d AS
SELECT 
    cvr."FILING_ID" as filing_id,
    cvr."AMEND_ID" as amend_id,
    itemd."AMOUNT_A" as itemized_total,
    unitemd."AMOUNT_A" as unitemized_total,
    combined."AMOUNT_A" as combined_total
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
-- get the itemized total
LEFT JOIN "SMRY_CD" itemd
ON cvr."FILING_ID" = itemd."FILING_ID"
AND cvr."AMEND_ID" = itemd."AMEND_ID"
AND UPPER(itemd."FORM_TYPE") = 'D'
AND itemd."LINE_ITEM" = '1'
-- get the unitemized total
LEFT JOIN "SMRY_CD" unitemd
ON cvr."FILING_ID" = unitemd."FILING_ID"
AND cvr."AMEND_ID" = unitemd."AMEND_ID"
AND UPPER(unitemd."FORM_TYPE") = 'D'
AND unitemd."LINE_ITEM" = '2'
-- get the combined total
LEFT JOIN "SMRY_CD" combined
ON cvr."FILING_ID" = combined."FILING_ID"
AND cvr."AMEND_ID" = combined."AMEND_ID"
AND UPPER(combined."FORM_TYPE") = 'D'
AND combined."LINE_ITEM" = '3'
WHERE cvr."FORM_TYPE" = 'F460';

create_f460_schedule_e.sql

-- Payments Made
CREATE TABLE f460_schedule_e AS
SELECT 
    cvr."FILING_ID" as filing_id,
    cvr."AMEND_ID" as amend_id,
    itemd."AMOUNT_A" as itemized_total,
    unitemd."AMOUNT_A" as unitemized_total,
    interest."AMOUNT_A" as interest_paid_total,
    combined."AMOUNT_A" as combined_total
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
-- get the itemized total
LEFT JOIN "SMRY_CD" itemd
ON cvr."FILING_ID" = itemd."FILING_ID"
AND cvr."AMEND_ID" = itemd."AMEND_ID"
AND UPPER(itemd."FORM_TYPE") = 'E'
AND itemd."LINE_ITEM" = '1'
-- get the unitemized total
LEFT JOIN "SMRY_CD" unitemd
ON cvr."FILING_ID" = unitemd."FILING_ID"
AND cvr."AMEND_ID" = unitemd."AMEND_ID"
AND UPPER(unitemd."FORM_TYPE") = 'E'
AND unitemd."LINE_ITEM" = '2'
-- get the interest total
LEFT JOIN "SMRY_CD" interest
ON cvr."FILING_ID" = interest."FILING_ID"
AND cvr."AMEND_ID" = interest."AMEND_ID"
AND UPPER(interest."FORM_TYPE") = 'E'
AND interest."LINE_ITEM" = '3'
-- get the combined total
LEFT JOIN "SMRY_CD" combined
ON cvr."FILING_ID" = combined."FILING_ID"
AND cvr."AMEND_ID" = combined."AMEND_ID"
AND UPPER(combined."FORM_TYPE") = 'E'
AND combined."LINE_ITEM" = '4'
WHERE cvr."FORM_TYPE" = 'F460';

create_f460_schedule_f.sql

-- Accrued Expenses (Unpaid Bills)
CREATE TABLE f460_schedule_f AS
SELECT 
    cvr."FILING_ID" as filing_id,
    cvr."AMEND_ID" as amend_id,
    incur."AMOUNT_A" as incurred_total,
    paid."AMOUNT_A" as paid_total,
    net."AMOUNT_A" as net_total
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
-- get the incurred payment total
LEFT JOIN "SMRY_CD" incur
ON cvr."FILING_ID" = incur."FILING_ID"
AND cvr."AMEND_ID" = incur."AMEND_ID"
AND UPPER(incur."FORM_TYPE") = 'E'
AND incur."LINE_ITEM" = '1'
-- get the paid total
LEFT JOIN "SMRY_CD" paid
ON cvr."FILING_ID" = paid."FILING_ID"
AND cvr."AMEND_ID" = paid."AMEND_ID"
AND UPPER(paid."FORM_TYPE") = 'E'
AND paid."LINE_ITEM" = '2'
-- get the net total
LEFT JOIN "SMRY_CD" net
ON cvr."FILING_ID" = net."FILING_ID"
AND cvr."AMEND_ID" = net."AMEND_ID"
AND UPPER(net."FORM_TYPE") = 'E'
AND net."LINE_ITEM" = '3'
WHERE cvr."FORM_TYPE" = 'F460';

create_f460_schedule_g.sql

-- Payments Made by an Agent or Independent Contractor (on Behalf of This Committee)
CREATE TABLE f460_schedule_g AS
SELECT 
    cvr."FILING_ID" as filing_id,
    cvr."AMEND_ID" as amend_id,
    tot."AMOUNT_A" as total
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
JOIN "SMRY_CD" tot
ON cvr."FILING_ID" = tot."FILING_ID"
AND cvr."AMEND_ID" = tot."AMEND_ID"
AND UPPER(tot."FORM_TYPE") = 'G'
AND tot."LINE_ITEM" = '0'
WHERE cvr."FORM_TYPE" = 'F460';

create_f460_schedule_h.sql

-- Loans Made to Others
CREATE TABLE f460_schedule_h AS
SELECT 
    cvr."FILING_ID" as filing_id,
    cvr."AMEND_ID" as amend_id,
    made."AMOUNT_A" as made_total,
    rcvd."AMOUNT_A" as rcvd_total,
    net."AMOUNT_A" as net_total
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
-- get the loans made total
LEFT JOIN "SMRY_CD" made
ON cvr."FILING_ID" = made."FILING_ID"
AND cvr."AMEND_ID" = made."AMEND_ID"
AND UPPER(made."FORM_TYPE") = 'H'
AND made."LINE_ITEM" = '1'
-- get the payment received total
LEFT JOIN "SMRY_CD" rcvd
ON cvr."FILING_ID" = rcvd."FILING_ID"
AND cvr."AMEND_ID" = rcvd."AMEND_ID"
AND UPPER(rcvd."FORM_TYPE") = 'H'
AND rcvd."LINE_ITEM" = '2'
-- get the net total
LEFT JOIN "SMRY_CD" net
ON cvr."FILING_ID" = net."FILING_ID"
AND cvr."AMEND_ID" = net."AMEND_ID"
AND UPPER(net."FORM_TYPE") = 'H'
AND net."LINE_ITEM" = '3'
WHERE cvr."FORM_TYPE" = 'F460';

create_f460_schedule_i.sql

-- Miscellaneous increases in cash
CREATE TABLE f460_schedule_i AS
SELECT 
    cvr."FILING_ID" as filing_id,
    cvr."AMEND_ID" as amend_id,
    itemd."AMOUNT_A" as itemized_total,
    unitemd."AMOUNT_A" as unitemized_total,
    interest."AMOUNT_A" as interest_paid_total,
    combined."AMOUNT_A" as combined_total
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
-- get the itemized total
LEFT JOIN "SMRY_CD" itemd
ON cvr."FILING_ID" = itemd."FILING_ID"
AND cvr."AMEND_ID" = itemd."AMEND_ID"
AND UPPER(itemd."FORM_TYPE") = 'I'
AND itemd."LINE_ITEM" = '1'
-- get the unitemized total
LEFT JOIN "SMRY_CD" unitemd
ON cvr."FILING_ID" = unitemd."FILING_ID"
AND cvr."AMEND_ID" = unitemd."AMEND_ID"
AND UPPER(unitemd."FORM_TYPE") = 'I'
AND unitemd."LINE_ITEM" = '2'
-- get the interest total
LEFT JOIN "SMRY_CD" interest
ON cvr."FILING_ID" = interest."FILING_ID"
AND cvr."AMEND_ID" = interest."AMEND_ID"
AND UPPER(interest."FORM_TYPE") = 'I'
AND interest."LINE_ITEM" = '3'
-- get the combined total
LEFT JOIN "SMRY_CD" combined
ON cvr."FILING_ID" = combined."FILING_ID"
AND cvr."AMEND_ID" = combined."AMEND_ID"
AND UPPER(combined."FORM_TYPE") = 'I'
AND combined."LINE_ITEM" = '4'
WHERE cvr."FORM_TYPE" = 'F460';

create_f460_summary.sql

CREATE TABLE f460_summary AS
SELECT 
    cvr."FILING_ID" as filing_id,
    cvr."AMEND_ID" as amend_id,
    line_1."AMOUNT_A" as monetary_contributions,
    line_2."AMOUNT_A" as loan_received,
    line_3."AMOUNT_A" as subtotal_cash_contributions,
    line_4."AMOUNT_A" as nonmonetary_contributions,
    line_5."AMOUNT_A" as total_contributions,
    line_6."AMOUNT_A" as payments_made,
    line_7."AMOUNT_A" as loans_made,
    line_8."AMOUNT_A" as subtotal_cash_payments,
    line_9."AMOUNT_A" as unpaid_bills,
    line_10."AMOUNT_A" as nonmonetary_adjustment,
    line_11."AMOUNT_A" as total_expenditures_made,
    line_12."AMOUNT_A" as begin_cash_balance,
    line_13."AMOUNT_A" as cash_receipts,
    line_14."AMOUNT_A" as miscellaneous_cash_increases,
    line_15."AMOUNT_A" as cash_payments,
    line_16."AMOUNT_A" as ending_cash_balance,
    line_17."AMOUNT_A" as loan_guarantees_received,
    line_18."AMOUNT_A" as cash_equivalents,
    line_19."AMOUNT_A" as outstanding_debts
FROM "CVR_CAMPAIGN_DISCLOSURE_CD" cvr
-- get Monetary Contributions
LEFT JOIN "SMRY_CD" line_1
ON cvr."FILING_ID" = line_1."FILING_ID"
AND cvr."AMEND_ID" = line_1."AMEND_ID"
AND UPPER(line_1."FORM_TYPE") = 'F460'
AND line_1."LINE_ITEM" = '1'
-- get Loans Received
LEFT JOIN "SMRY_CD" line_2
ON cvr."FILING_ID" = line_2."FILING_ID"
AND cvr."AMEND_ID" = line_2."AMEND_ID"
AND UPPER(line_2."FORM_TYPE") = 'F460'
AND line_2."LINE_ITEM" = '2'
-- get Cash Contributions Sub-total
LEFT JOIN "SMRY_CD" line_3
ON cvr."FILING_ID" = line_3."FILING_ID"
AND cvr."AMEND_ID" = line_3."AMEND_ID"
AND UPPER(line_3."FORM_TYPE") = 'F460'
AND line_3."LINE_ITEM" = '3'
-- get Non-monetary Contributions
LEFT JOIN "SMRY_CD" line_4
ON cvr."FILING_ID" = line_4."FILING_ID"
AND cvr."AMEND_ID" = line_4."AMEND_ID"
AND UPPER(line_4."FORM_TYPE") = 'F460'
AND line_4."LINE_ITEM" = '4'
-- get Total Contributions
LEFT JOIN "SMRY_CD" line_5
ON cvr."FILING_ID" = line_5."FILING_ID"
AND cvr."AMEND_ID" = line_5."AMEND_ID"
AND UPPER(line_5."FORM_TYPE") = 'F460'
AND line_5."LINE_ITEM" = '5'
-- get Payments Made
LEFT JOIN "SMRY_CD" line_6
ON cvr."FILING_ID" = line_6."FILING_ID"
AND cvr."AMEND_ID" = line_6."AMEND_ID"
AND UPPER(line_6."FORM_TYPE") = 'F460'
AND line_6."LINE_ITEM" = '6'
-- get Loans Made
LEFT JOIN "SMRY_CD" line_7
ON cvr."FILING_ID" = line_7."FILING_ID"
AND cvr."AMEND_ID" = line_7."AMEND_ID"
AND UPPER(line_7."FORM_TYPE") = 'F460'
AND line_7."LINE_ITEM" = '7'
-- get Cash Payments Sub-total
LEFT JOIN "SMRY_CD" line_8
ON cvr."FILING_ID" = line_8."FILING_ID"
AND cvr."AMEND_ID" = line_8."AMEND_ID"
AND UPPER(line_8."FORM_TYPE") = 'F460'
AND line_8."LINE_ITEM" = '8'
-- get Accrued Expenses (Unpaid Bills) 
LEFT JOIN "SMRY_CD" line_9
ON cvr."FILING_ID" = line_9."FILING_ID"
AND cvr."AMEND_ID" = line_9."AMEND_ID"
AND UPPER(line_9."FORM_TYPE") = 'F460'
AND line_9."LINE_ITEM" = '9'
-- get Non-monetary Adjustment
LEFT JOIN "SMRY_CD" line_10
ON cvr."FILING_ID" = line_10."FILING_ID"
AND cvr."AMEND_ID" = line_10."AMEND_ID"
AND UPPER(line_10."FORM_TYPE") = 'F460'
AND line_10."LINE_ITEM" = '10'
-- get Total Expenditures Made
LEFT JOIN "SMRY_CD" line_11
ON cvr."FILING_ID" = line_11."FILING_ID"
AND cvr."AMEND_ID" = line_11."AMEND_ID"
AND UPPER(line_11."FORM_TYPE") = 'F460'
AND line_11."LINE_ITEM" = '11'
-- get Beginning Cash Balance
LEFT JOIN "SMRY_CD" line_12
ON cvr."FILING_ID" = line_12."FILING_ID"
AND cvr."AMEND_ID" = line_12."AMEND_ID"
AND UPPER(line_12."FORM_TYPE") = 'F460'
AND line_12."LINE_ITEM" = '12'
-- get Cash Receipts
LEFT JOIN "SMRY_CD" line_13
ON cvr."FILING_ID" = line_13."FILING_ID"
AND cvr."AMEND_ID" = line_13."AMEND_ID"
AND UPPER(line_13."FORM_TYPE") = 'F460'
AND line_13."LINE_ITEM" = '13'
-- get Miscellaneous Cash Increases
LEFT JOIN "SMRY_CD" line_14
ON cvr."FILING_ID" = line_14."FILING_ID"
AND cvr."AMEND_ID" = line_14."AMEND_ID"
AND UPPER(line_14."FORM_TYPE") = 'F460'
AND line_14."LINE_ITEM" = '14'
-- get Cash Payments
LEFT JOIN "SMRY_CD" line_15
ON cvr."FILING_ID" = line_15."FILING_ID"
AND cvr."AMEND_ID" = line_15."AMEND_ID"
AND UPPER(line_15."FORM_TYPE") = 'F460'
AND line_15."LINE_ITEM" = '15'
-- get Ending Cash Balance
LEFT JOIN "SMRY_CD" line_16
ON cvr."FILING_ID" = line_16."FILING_ID"
AND cvr."AMEND_ID" = line_16."AMEND_ID"
AND UPPER(line_16."FORM_TYPE") = 'F460'
AND line_16."LINE_ITEM" = '16'
-- get Loan Guarantees Received
LEFT JOIN "SMRY_CD" line_17
ON cvr."FILING_ID" = line_17."FILING_ID"
AND cvr."AMEND_ID" = line_17."AMEND_ID"
AND UPPER(line_17."FORM_TYPE") = 'F460'
AND line_17."LINE_ITEM" = '17'
-- get Cash Equivalents
LEFT JOIN "SMRY_CD" line_18
ON cvr."FILING_ID" = line_18."FILING_ID"
AND cvr."AMEND_ID" = line_18."AMEND_ID"
AND UPPER(line_18."FORM_TYPE") = 'F460'
AND line_18."LINE_ITEM" = '18'
-- get Outstanding Debts
LEFT JOIN "SMRY_CD" line_19
ON cvr."FILING_ID" = line_19."FILING_ID"
AND cvr."AMEND_ID" = line_19."AMEND_ID"
AND UPPER(line_19."FORM_TYPE") = 'F460'
AND line_19."LINE_ITEM" = '19'
WHERE cvr."FORM_TYPE" = 'F460';