jared
93300ae0c1
R-CMD-check / check (push) Successful in 3m5s
Rewrites expenditure/revenue classification off item-code first-letter
prefixes and onto summary_categories membership (F-018: prefix Y spans
revenue, expenditure, and balance codes), and exposes
expenditure_concept = c("primary", "direct", "total") with primary as
the new default:
primary = operations + capital + assistance
direct = primary + interest + insurance_benefits (Census Direct)
total = direct + intergovernmental (M/L/Q via ig views)
- inst/sql: flow views (20-25) select by crosswalk membership;
summary_categories moves to 11- so it registers before them (DuckDB
binds view sources eagerly). The IG leg gains Q11/Q12/Q18 state
school-system payments (F-017).
- R: one subtype scope per verb call drives the verb SQL, the
harmonization exclusion count, and the complete = TRUE grid;
flow_prefixes survives only to scope recipe suggestions.
cog_geographic_rollup/cog_peer_compare accept primary|direct, still
refuse total, and now actually pass the concept through.
- Balance codes can never reach a spending or revenue result
(uscogdata#25), asserted at both view and verb level.
- Deletes the #11 skip; per the 2026-07-30 owner ruling the F-018 Y01
proof is asserted against the crosswalk, not the default
cog_revenue() call (which stays General Revenue pending #12).
Suite: 696 pass / 0 fail / 1 skip (#12, expected).
Closes #11
26 lines
1.3 KiB
SQL
26 lines
1.3 KiB
SQL
-- Intergovernmental expenditure rows: crosswalk spend_subtype =
|
|
-- 'intergovernmental' (M = to local govts, L = to state govts, Q11/Q12/Q18
|
|
-- = state payments to school systems -- uscogdata#11, finding F-017).
|
|
--
|
|
-- Deliberately does NOT filter `NOT is_aggregate`, unlike spending_long. In the
|
|
-- wide era (<= FY2011) the IG families M05/M12/M47/M89/L47/L89 are published
|
|
-- ONLY as aggregate-flagged rows -- filtering them would hide ~70% of legacy IG
|
|
-- dollars and make Total silently collapse to Direct. This is safe because the
|
|
-- aggregate codes and their modern leaf components are strictly year-disjoint
|
|
-- (M47 ends 2011 / M94 starts 2012; M89 is aggregate only <= 2011 and a leaf
|
|
-- from 2012 alongside M91-93), so no row is ever counted twice. Same argument
|
|
-- the pipeline's recipe joins use.
|
|
--
|
|
-- `L--` stays excluded: it is the IG-to-state FAMILY TOTAL and genuinely
|
|
-- rolls up the L-NN codes, so including it would double-count. The crosswalk
|
|
-- deliberately carries no `--` family-total codes, so membership excludes it
|
|
-- (guarded by "the IG leg never includes the L-- family total" in
|
|
-- tests/testthat/test-expenditure-concept.R).
|
|
CREATE OR REPLACE VIEW ig_long AS
|
|
SELECT *
|
|
FROM long
|
|
WHERE item_code IN (
|
|
SELECT item_code FROM summary_categories
|
|
WHERE spend_subtype = 'intergovernmental'
|
|
);
|