Include archived HES tables in relevant queries

[evansd]

As described in the issue below, this updates all queries involving the HES/SUS tables to combine data from both the regularly updated tables and the soon-to-be-populated archive tables.

There are a few subtleties here:

• The current and archived tables containing overlapping data so, to avoid duplicate rows, we pick a cutoff date and filter the data on this before combining. The stated intention is that the regularly updated tables contain data from 2022 onwards, so we use 2022-01-01 as the cutoff. Note that we will need to double-check this once the real data starts flowing.

• Filtering by date involves picking a date column. The most natural column for each dataset seemed to be:

  • APCS.Admission_Date
  • OPA.Appointment_Date
  • EC.Arrival_Date

• There are a small number of NULL values in these columns (approx 0.04% of APCS, 0.001% of OPA, and none in EC). As most research work using these tables is going to want to use these dates these rows are effectively not present in any case. But I don't want this change to suddenly make them completely invisible so I've opted to include NULL-dated rows from the current dataset but not from the archive (we can't do both or we risk duplicates).

Closes #2038

[cloudflare-pages[bot]]

Deploying databuilder-docs with    Cloudflare Pages

Latest commit:	153dbf6
Status:	✅  Deploy successful!
Preview URL:	https://c95a8b15.databuilder.pages.dev
Branch Preview URL:	https://evansd-hes-archive.databuilder.pages.dev

View logs