sql-as-of-address-date

Date: 2026-09-13. Status: implemented.

Decision. The SPD set adds link_as_of.sql with input id, postcode, address_date, analysis_year. As a project policy, select lives in [introduced_on, deleted_on), apply the whole/A preference among valid lives, follow a large user's exact link to the small-user life valid on that date, and choose the edition from analysis_year. Report postcode_not_yet_introduced, between_lives or postcode_deleted_by_date when no life contains the date, and missing_address_date for a known key with no date. Return first_introduced_on, previous_life_deleted_on and next_life_introduced_on as context. The 41-column core is shared with the other queries, but the added dates make 110 total columns. SSPL has no dated query because NRS kept one life per postcode.

Reason. Latest-life matching can assign a re-used postcode's new location to an older address: 2,957 reviewed SPD postcodes have a latest life in a different 2011 zone from the life before, 2,030 with a different 2020v2 quintile. A reliable address date can distinguish these lives; a general CHI record edit timestamp must not be assumed to describe residence without checking its meaning. Keep address date separate from the health data year. This policy does not reconstruct historical administrative or rurality snapshots; context remains what the downloaded release supplies for the selected life.

Evidence. FK17 8DS: 1975 resolves to the 1973 life in S01013116 (quintile 5), 1990 to the 1978 life in S01013113 (quintile 3), June 1978 falls between the lives. TD9 7PQ: matched on 15 May 1990 with matched_is_current false, deleted by date on its deletion day, not yet introduced in 1970. tests/test_sql_sets.py covers the interval boundaries, the gap, the split and link rules on a date, and every recorded life on its own introduction day against the built table.