18F/openFEC

-- vsum is as amended and has fewer columns
-- this takes the name from sum_and_det_sum_report to vsum
select vs.cmte_id,
    vs.cvg_start_dt,
    vs.cvg_end_dt,
    vs.rpt_yr,
    vs.receipt_dt,
    vs.rpt_yr + vs.rpt_yr % 2::numeric as election_cycle,
    vs.coh_bop,
    vs.ttl_receipts as ttl_receipts_per,
    vs.ttl_disb as ttl_disb_per,
    vs.coh_cop,
    vs.debts_owed_to_cmte,
    vs.debts_owed_by_cmte,
    vs.net_contb as net_contb_per,
    vs.op_exp_per as ttl_op_exp_per,
    vs.fed_funds_per,
    vs.indv_item_contb as indv_item_contb_per,
    vs.indv_unitem_contb as indv_unitem_contb_per,
    vs.indv_ref as ttl_indiv_contb_per, -- unfortunately, a wrong name in v_sum but mapped none the less
    vs.pty_cmte_contb as pol_pty_cmte_contb_per,
    vs.oth_cmte_contb as other_pol_cmte_contb_per,
    vs.cand_cntb as cand_contb_per,
    vs.ttl_contb as ttl_contb_per,
    vs.tranf_from_other_auth_cmte as tranf_from_affilated_cmte_per,
    vs.cand_loan as loans_received_from_cand_per,
    vs.oth_loans as other_loans_received_per,
    vs.ttl_loans as ttl_loans_received_per,
    vs.offsets_to_op_exp as offsets_to_op_exp_per,
    vs.offsets_to_fndrsg as offsets_to_fndrsg_exp_per,
    vs.offsets_to_legal_acctg as offsets_to_legal_acctg_per,
    vs.offsets_to_op_exp + vs.offsets_to_fndrsg + vs.offsets_to_legal_acctg as ttl_offsets_to_op_exp_per,
    vs.other_receipts as other_receipts_per,
    vs.op_exp_per,
    vs.tranf_to_other_auth_cmte as tranf_to_other_auth_cmte_per,
    vs.fndrsg_disb as fndrsg_disb_per,
    vs.exempt_legal_acctg_disb as exempt_legal_acctg_disb_per,
    vs.cand_loan_repymnt as repymts_loans_made_by_cand_per,
    vs.oth_loan_repymts as repymts_other_loans_per,
    vs.cand_loan_repymnt + vs.oth_loan_repymts as ttl_loan_repymts_made_per,
    vs.indv_ref as ref_indv_contb_per,
    vs.pol_pty_cmte_contb as ref_pol_pty_cmte_contb_per, -- bad column name in v_sum
    vs.oth_cmte_ref as ref_other_pol_cmte_contb_per,
    vs.pol_pty_cmte_contb + vs.oth_cmte_ref as ttl_contb_ref_per,
    vs.other_disb_per as other_disb_per,
    vs.orig_sub_id as sub_id
from disclosure.v_sum_and_det_sum_report vs
;