QC Open Data, Assessed Values: PINs without townships

[wrridgeway]

See https://github.com/ccao-data/data-architecture/issues/483#issuecomment-2145493865

[wrridgeway]

This shows that the PIN being investigated will no longer have a row for 2021 since it doesn't have a row in pardat for 2021.

with old as(
    select
        TRUE AS in_old,
        pin,
        year,
        class AS class_old,
        mailed_class AS mailed_class_old,
        mailed_tot AS mailed_old
    from default.vw_pin_history
    where pin = '32182130040000'
    and year IN ('2020', '2021')
),

new as(
    select
        TRUE AS in_new,
        pin,
        year,
        class AS class_new,
        mailed_class AS mailed_class_new,
        mailed_tot AS mailed_new
    from "z_ci_483_qc_open_data_assessed_values_pins_without_townships_default".vw_pin_history
    where pin = '32182130040000'
    and year IN ('2020', '2021')
)

select
    old.pin,
    old.year,
    in_old,
    in_new,
    class_old,
    class_new,
    mailed_class_old,
    mailed_class_new,
    mailed_old,
    mailed_new
from old
left join new on
    old.pin = new.pin
    and new.year = old.year

pin	year	in_old	in_new	class_old	class_new	mailed_class_old	mailed_class_new	mailed_old	mailed_new
32182130040000	2020	TRUE	TRUE	592	592	592	592	53444	53444
32182130040000	2021	TRUE				592		52494

[wrridgeway]

year	old_count	new_count	diff
2024	446066	446066	0
2023	1864162	1864161	-1
2022	1865013	1865013	0
2021	1864602	1864589	-13
2020	1864035	1864035	0
2019	1864102	1864102	0
2018	1864588	1864588	0
2017	1864621	1864621	0
2016	1863996	1863996	0
2015	1862756	1862756	0
2014	1862170	1862170	0
2013	1861929	1861929	0
2012	1862181	1862181	0
2011	1861008	1861008	0
2010	1859244	1859244	0
2009	1849650	1849650	0
2008	1832617	1832617	0
2007	1804217	1804217	0
2006	1771313	1771313	0
2005	1740963	1740963	0
2004	1716214	1716214	0
2003	1693139	1693139	0
2002	1673530	1673530	0
2001	1655168	1655168	0
2000	1637079	1637079	0
1999	1620798	1620798	0

[wrridgeway]

Seems reasonable, but should we wait for confirmation from @ccao-jardine that exclusion from the view is the right way to handle these PINs?

For context, @ccao-jardine , pardat is usually our universe rather than asmt_all, so this change will ultimately bring this view in line with others.

[ccao-jardine]

Yep, seems reasonable to me. Thanks for checking into it!