jared
fefd4fe969
total adds an intergovernmental leg (M = to local, L = to state) as a UNION ALL over new ig_annotated views. The IG leg deliberately skips NOT is_aggregate -- legacy IG lives almost entirely on aggregate rows, and the aggregate codes are year-disjoint from their modern leaf components, so nothing double-counts. L-- (the IG-to-state family total) is excluded. direct is the default and is numerically unchanged.
17 lines
436 B
SQL
17 lines
436 B
SQL
CREATE OR REPLACE VIEW ig_annotated_harmonized AS
|
|
SELECT
|
|
s.*,
|
|
x.gov_name AS xwalk_gov_name,
|
|
x.govs_type,
|
|
x.type_label,
|
|
x.fips_state AS xwalk_fips_state,
|
|
x.fips_county AS xwalk_fips_county,
|
|
x.fips_place,
|
|
x.population_acs,
|
|
c.category,
|
|
c.category_type,
|
|
c.spend_subtype
|
|
FROM ig_long_harmonized s
|
|
LEFT JOIN canonical_fips_xwalk x USING (canonical_govid)
|
|
LEFT JOIN summary_categories c USING (item_code);
|