Fix and test remaining dataset definitions in ehrQL examples

[StevenMaude]

#1648 adds tests for most of the dataset definitions in the examples page. There were a couple left as it wasn't immediately clear how best to fix them. We should fix and test these.

Specifically these are the two sections below.

"What is the earliest/latest hospitalisation event matching some criteria?"

ehrql/docs/how-to/examples.md

Lines 493 to 527 in 260caf1

	### What is the earliest/latest hospitalisation event matching some criteria?
	```python
	from ehrql import create_dataset, codelist_from_csv
	from ehrql.tables.tpp import apcs, patients
	cardiac_diagnosis_codes = codelist_from_csv("XXX", column="YYY")
	dataset = create_dataset()
	dataset.first_cardiac_hospitalisation_date = apcs.where(
	apcs.snomedct_code.is_in(cardiac_diagnosis_codes)
	).where(
	apcs.date.is_on_or_after("2022-07-01")
	).sort_by(
	apcs.date
	).first_for_patient().date
	dataset.define_population(patients.exists_for_patient())
	```
	```ehrql
	from ehrql import create_dataset, codelist_from_csv
	from ehrql.tables.core import medications, patients
	cardiac_diagnosis_codes = codelist_from_csv("XXX", column="YYY")
	dataset = create_dataset()
	dataset.last_cardiac_hospitalisation_date = medications.where(
	medications.dmd_code.is_in(cardiac_diagnosis_codes)
	).where(
	medications.date.is_on_or_after("2022-07-01")
	).sort_by(
	medications.date
	).last_for_patient().date
	dataset.define_population(patients.exists_for_patient())
	```

Issues

• The first dataset definition of the two in this section uses apcs.snomedct_code which doesn't exist. This could be switched to primary_diagnosis which is a single ICD-10 code, or all_diagnoses which is a string of diagnosis codes separated by semi-colons.
• The second dataset definition is valid ehrQL, but isn't really a semantically sensible example:
  • A variable refers to "hospitalisation date" when actually dealing with medications (and the example is supposed to be dealing with hospitalisations)
  • It checks whether diagnosis codes are in the dmd_code.

"Finding the observed value of clinical events matching some criteria expressed relative to another value"

ehrql/docs/how-to/examples.md

Lines 661 to 684 in 260caf1

	### Finding the observed value of clinical events matching some criteria expressed relative to another value
	```python
	from ehrql import create_dataset, codelist_from_csv
	from ehrql.tables.core import clinical_events, patients
	hba1c_codelist = codelist_from_csv("XXX", column="YYY")
	dataset = create_dataset()
	mean_hba1c = clinical_events.where(
	clinical_events.snomedct_code.is_in(hba1c_codelist)
	).where(
	clinical_events.date.is_on_or_after("2022-07-01")
	).numeric_value.maximum_for_patient()
	dataset.mean_max_hbac_difference = max_hba1c - (
	clinical_events.where(clinical_events.snomedct_code.is_in(hba1c_codelist)
	).where(
	clinical_events.numeric_value == max_hba1c
	).sort_by(
	clinical_events.date
	).numeric_value.mean_for_patient())
	dataset.define_population(patients.exists_for_patient())
	```

Issues

• The dataset definition creates a variable called mean_hba1c but does this by calculating the maximum_for_patient.
• The dataset definition uses max_hba1c, which doesn't exist.
• The dataset definition finds the value of clinical events matching the hba1c_codelist where the value is the same as max_hba1c, finds the mean (which will be the same value) and then subtracts this from max_hba1c.

What this might have been intended as

Maybe something like the following?

from ehrql import create_dataset, codelist_from_csv
from ehrql.tables.core import clinical_events, patients

hba1c_codelist = codelist_from_csv("XXX", column="YYY")

dataset = create_dataset()

max_hba1c = clinical_events.where(
        clinical_events.snomedct_code.is_in(hba1c_codelist)
).where(
        clinical_events.date.is_on_or_after("2022-07-01")
).numeric_value.maximum_for_patient()

mean_hba1c = clinical_events.where(
        clinical_events.snomedct_code.is_in(hba1c_codelist)
).where(
        clinical_events.date.is_on_or_after("2022-07-01")
).numeric_value.mean_for_patient()

dataset.mean_max_hbac_difference = max_hba1c - mean_hba1c
dataset.define_population(patients.exists_for_patient())

[StevenMaude]

This doesn't cover examples in the surrounding OpenSAFELY documentation, for which there's another issue: opensafely/documentation#1373.

[rebkwok]

Just a note as a reminder - currently the tests assume that all ehrql snippets are dataset definitions. I added a measures definition in #1723 which failed because the tests tried to test it as a dataset definition using generate_dataset. Possibly it'll be addressed in #1357

[StevenMaude]

I opened a separate issue for measure definitions not yet being tested: #1726.