sql-latest-linked-postcodes

Date: 2026-09-12. Status: superseded.

Decision. Two SQL examples share one latest-postcode view. One uses a chosen SIMD edition (default 2020v2); one uses PHS v3.5 Table 4 by event year. Both use PHS population-weighted Scotland quintiles, 1 most deprived. The date selects the edition, not a historical postcode life. Ordinary split postcodes use A; large users use the latest record of the exact named small-user link, including an explicit split suffix. Missing/unusable links have null SIMD, never an own-geography fallback. Keep every event row and expose both matched and source natural keys.

Reason. The user chose the linked-small-user route. Keep edition choice separate from postcode selection and put shared rules in one reviewable place. PHS's postcode page describes latest versions and A parts; Appendix A describes large-user links but does not fully specify own-versus-linked disagreement. Exact equivalence to a published PHS postcode lookup remains unverified.

Evidence. docs/LINKAGE_BY_ERA.md maps code to guidance and labels project choices; tests/test_sql.py uses explicit fixtures and downloaded-data checks. SPD 2026/2 example AB11 6GN has own 2020v2 quintile 2 but linked AB11 6BE supplies 3.