Repository: opensafely-core/cohort-extractor (language: python)

Issue:
Add out-patient appointment data to study definition
Issue for querying HES / SUS out-patient appointments data from the `OPA` tables in the OpenSAFELY-TPP database.

A [full data dictionary for the outpatient dataset in HES](https://nhs-prod.global.ssl.fastly.net/binaries/content/assets/website-assets/data-and-information/data-tools-and-services/data-services/hospital-episode-statistics/hes-data-dictionary/dd-op_v12.pdf) is available, though by the time this data gets to the OpenSAFELY-TPP database there's been some filtering and processing -- see [the database schema](https://github.com/opensafely/database-notebooks/blob/master/notebooks/database-schema.ipynb) to see what's available. 

Fields that are likely to be important:

* `Attendance_status` in the `OPA` table -- did the patient attend the appointment? 
* `Main_Specialty_Code` in the `OPA` table. This is the "the specialty under which the consultant is contracted".  One of around 100 possible codes eg ENT, obstetrics.
* `Treatment_Function_Code` in the `OPA` table. This is "the specialty in which the consultant was working during the period of care". Same set of codes as `Main_Specialty_Code`. I'm not sure the exact difference between this and `Main_Specialty_Code` though very often they're the same.
* `Primary_Procedure_code` in the `OPA_Proc` table. I.e., what procedure was undertaken, if any? These are OPCS-4 codes https://classbrowser.nhs.uk/#/book/OPCS-4.9. 
	* If there was a procedure, then in theory `Operation_Status` should be `1` from the `OPA` table. If none, eg consultation only, then `Operation_Status` should be `8`. But sometimes `Primary_Procedure_code` is `NULL` and `Operation_Status` is `8`. So not sure what's going on here.
	* There's also `Procedure_Code_2`. There are up to 23 secondary procedure codes in HES but this column only has at most one secondary procedure code.


The study definition variable will look something like this:

```
appointment_date = patients.outpatient_appointment_date(
	returning = "date",
	attended = True,
	with_these_treatment_codes = treatment_codelist,
	with_these_specialty_codes = specialty_codelist,
	with_these_procedure_codes = OPCS_codelist,
	on_or_after = "index_date",
	date_format="YYYY-MM-DD"
)
```

Potential other returning values that may be needed are `returning = "primary_procedure"` or `returning = "treatment_code"` or `returning = "specialty_code"` or `returning = "diagnosis"`, but these aren't immediately needed for the vaccine study.


## Use-case for vaccine study

The immediate use-case for this variable is to identify outpatient appointments that are likely to increase the chance of getting a covid vaccine. Principally because vaccines may have occurred or have been booked on the back of OP appointments. Essentially, we need to return the appointment date for a subset of appointments based on the type of appointment ("type" to be defined!). 

It's important to be able to exclude vaccination appointments, otherwise by using these appointments to predict vaccination we have a tautology. So any appointments involving vaccine-related OPCS codes must be explicitly excluded (eg `X44.3 Subcutaneous injection of vaccine`). Since only the primary procedure and one secondary procedure codes are available, then we may not catch all vaccine appointments.

We probably only need appointments that were actually attended (`Attendance_status==True`), although in theory it's possible that the vaccination was arranged at the time the appointment was _booked_ rather than when the appointment _occurred_.

## Multiple appointments on the same day
There's an added complexity to outpatient appointments given that multiple appointments on the same day are more likely, and info from all of them may be relevant and need to be queried / returned. This is tricky because it's not currently possible to return event-level data (ie, one row per patient per event), and `on_or_after`-type queries for getting consecutive event dates returned across multiple columns can't (?) be used to return two distinct events occurring on the same day.

Since appointment time isn't known (`Appointment_Date` is given as `YYY-MM-DD`) I think we'd have use the `OPA_Ident` appointment ID column instead. So for example `on_or_after="appointment_date_1"` internally creates two conditions: `appointment_date_2 >= appointment_date_1 AND appointment_ID_2 != appointment_ID_1`. But it's probably a mega-faff since `"appointment_date_1"` is just a date/string by the time it's passed to `on_or_after` (it doesn't additionally contain patient or appointment ID info). 

This sort of feature would be handy for hospital admissions and A&E attendances too. 

Add out-patient appointment data to study definition
Issue for querying HES / SUS out-patient appointments data from the `OPA` tables in the OpenSAFELY-TPP database.

A [full data dictionary for the outpatient dataset in HES](https://nhs-prod.global.ssl.fastly.net/binaries/content/assets/website-assets/data-and-information/data-tools-and-services/data-services/hospital-episode-statistics/hes-data-dictionary/dd-op_v12.pdf) is available, though by the time this data gets to the OpenSAFELY-TPP database there's been some filtering and processing -- see [the database schema](https://github.com/opensafely/database-notebooks/blob/master/notebooks/database-schema.ipynb) to see what's available. 

Fields that are likely to be important:

* `Attendance_status` in the `OPA` table -- did the patient attend the appointment? 
* `Main_Specialty_Code` in the `OPA` table. This is the "the specialty under which the consultant is contracted".  One of around 100 possible codes eg ENT, obstetrics.
* `Treatment_Function_Code` in the `OPA` table. This is "the specialty in which the consultant was working during the period of care". Same set of codes as `Main_Specialty_Code`. I'm not sure the exact difference between this and `Main_Specialty_Code` though very often they're the same.
* `Primary_Procedure_code` in the `OPA_Proc` table. I.e., what procedure was undertaken, if any? These are OPCS-4 codes https://classbrowser.nhs.uk/#/book/OPCS-4.9. 
	* If there was a procedure, then in theory `Operation_Status` should be `1` from the `OPA` table. If none, eg consultation only, then `Operation_Status` should be `8`. But sometimes `Primary_Procedure_code` is `NULL` and `Operation_Status` is `8`. So not sure what's going on here.
	* There's also `Procedure_Code_2`. There are up to 23 secondary procedure codes in HES but this column only has at most one secondary procedure code.


The study definition variable will look something like this:

```
appointment_date = patients.outpatient_appointment_date(
	returning = "date",
	attended = True,
	with_these_treatment_codes = treatment_codelist,
	with_these_specialty_codes = specialty_codelist,
	with_these_procedure_codes = OPCS_codelist,
	on_or_after = "index_date",
	date_format="YYYY-MM-DD"
)
```

Potential other returning values that may be needed are `returning = "primary_procedure"` or `returning = "treatment_code"` or `returning = "specialty_code"` or `returning = "diagnosis"`, but these aren't immediately needed for the vaccine study.


## Use-case for vaccine study

The immediate use-case for this variable is to identify outpatient appointments that are likely to increase the chance of getting a covid vaccine. Principally because vaccines may have occurred or have been booked on the back of OP appointments. Essentially, we need to return the appointment date for a subset of appointments based on the type of appointment ("type" to be defined!). 

It's important to be able to exclude vaccination appointments, otherwise by using these appointments to predict vaccination we have a tautology. So any appointments involving vaccine-related OPCS codes must be explicitly excluded (eg `X44.3 Subcutaneous injection of vaccine`). Since only the primary procedure and one secondary procedure codes are available, then we may not catch all vaccine appointments.

We probably only need appointments that were actually attended (`Attendance_status==True`), although in theory it's possible that the vaccination was arranged at the time the appointment was _booked_ rather than when the appointment _occurred_.

## Multiple appointments on the same day
There's an added complexity to outpatient appointments given that multiple appointments on the same day are more likely, and info from all of them may be relevant and need to be queried / returned. This is tricky because it's not currently possible to return event-level data (ie, one row per patient per event), and `on_or_after`-type queries for getting consecutive event dates returned across multiple columns can't (?) be used to return two distinct events occurring on the same day.

Since appointment time isn't known (`Appointment_Date` is given as `YYY-MM-DD`) I think we'd have use the `OPA_Ident` appointment ID column instead. So for example `on_or_after="appointment_date_1"` internally creates two conditions: `appointment_date_2 >= appointment_date_1 AND appointment_ID_2 != appointment_ID_1`. But it's probably a mega-faff since `"appointment_date_1"` is just a date/string by the time it's passed to `on_or_after` (it doesn't additionally contain patient or appointment ID info). 

This sort of feature would be handy for hospital admissions and A&E attendances too. 



Discussion / hints:
Looks great. We may also want to use the data to indicate something about patients' clinical conditions (e.g. to infer seriousness of a diagnosis code that appears in primary care) and to look at activity trends during and after the pandemic. 

Other fields which are useful more generally:
- First_Attendance - indicates whether the appointment relates to a new referral or part of ongoing care (potentially useful for assessing trends in activity)
- Referral_Request_Received_Date - indicates for how long the treatment has been ongoing. 
- Outcome_of_Attendance - indicates whether this was the end of the treatment or not, or whether inpatient treatment was required
- Consultation_Medium_Used - face to face or not - obviously big covid-related changes here.
- Medical_Staff_Type_Seeing_Patient - can help narrow down type of treatment e.g for an appointment in trauma & orthopaedics is patient seeing a surgeon vs a nurse for a wound issue.
- Activity_Location_Type_Code - can determine whether the patient was seen in an NHS or private hospital (or other location such as a care home) - if seen in private hospital this could be a reasonable indicator of being seen/considered for elective surgery. Care home location could give new information about care home status of patient.

### On specialty / treatment function codes:

- Main specialty -- 84 different codes in our OPA dataset --252k nulls
- TFC --152 different codes -- 148k nulls
- Other than where both are null (101k appointments), nulls most often (but not always) occur where the other is a code starting with 6 or 7 (these seem to be generally mental health and psych - as a side note we won't have most MH attendances because these will be in community/MH Trusts which usually don't submit data to SUS)

#### Top 3 'specialty-TFC' combinations:
- 130-130 (ophthalmology) -- 6.5m
- 110-110 (Trauma & orthopaedics) --5.5m
- 960-650 (Allied Health Professional - Physiotherapy Service) -- 3.2m
#### next most common Specialty-TFC combinations (where they are different):
- 810-812 (Radiology - Diagnostic Imaging Service) --1.4m
- 100-103 (General Surgery - Breast surgery)--1.2m

Overall I'd say TFC is most useful because it's a bit more specific/descriptive, e.g. in the case of the most common non-identical combination `960-650 (Allied Health Professional - Physiotherapy Service)` the purpose of these appointments is completely obscure from the specialty of Allied Health Professional. 
Great. Is it possible to pin this down to a desired specification yet?

Probably the best way to do that is fill out Will's proposal (copied below) with all the desired `with_these_*` arguments, plus draft documentation that would describe the use adequately to end users. 

So for example how would we explain when to use which kind of specialty codes to researchers?

```
appointment_date = patients.outpatient_appointment_date(
	returning = "date",
	attended = True,
	with_these_treatment_codes = treatment_codelist,
	with_these_specialty_codes = specialty_codelist,
	with_these_procedure_codes = OPCS_codelist,
	on_or_after = "index_date",
	date_format="YYYY-MM-DD"
)

```
- `treatment` should be named `treatment function code` or `TFC` for clarity if we are using it. But probably we should pick one. I think TFC is more useful so we could drop specialty. 
- I would also add the list of fields I put above, in particular `is_first_attendance` would be a very useful filter as it can be used to capture a new referral, or count a period of treatment per person, and `Consultation_Medium_Used` as a returning option I think will be in demand.
Thanks @HelenCEBM , for the avoidance of doubt could you write this up as it would appear in the docs? 

For example, I think you're proposing the following (but with more fields)?

```
appointment_date = patients.outpatient_appointment_date(
	returning = "date",
	attended = True,
	with_these_treatment_function_codes=treatment_codelist,
	with_these_procedure_codes=OPCS_codelist,
        referral_request_received_date="YYYY-MM-DD",
	on_or_after = "index_date",
	date_format="YYYY-MM-DD"
)
```

I also don't quite understand what @wjchulme is proposing we do about multiple appointments on the same day.

Also it would be helpful to have a stab at what we'd say in the docs about procedure codes (coding, what we're querying and why we're not querying specialty, etc)

Thanks! I'll be getting an engineer who's not made variables before onto this, so making it really clear up front is helpful
```
appointment_date = patients.outpatient_appointment_date(
	returning = "date",
	attended = True,
        is_first_attendance = True,
	with_these_treatment_function_codes=tfc_codelist,
	with_these_procedure_codes=OPCS_codelist,
        referral_request_received_date="YYYY-MM-DD",
	on_or_after = "index_date",
	date_format="YYYY-MM-DD"
)
```

Additional returning options:
- Referral_Request_Received_Date 
- Outcome_of_Attendance
- Consultation_Medium_Used
- Medical_Staff_Type_Seeing_Patient
- Activity_Location_Type_Code
- There's also a diagnosis code but it is usually highly incomplete.

We should check if these fields are all populated but I don't really have time this week!
Re appointments on the same day

There is currently no way to use a study definition to reliably retrieve info from two or more distinct events (in this case appointments) that occur on the same day in the same patient. It's likely that there will be many such cases in out-patient appointment data, so this is a problem (I will check how often this occurs). 

The current workaround to retrieve consecutive events is to do something like

```py
event_date1 = patients.appointment(
  on_or_after="index_date",
  returning = "date"
)

event_date2 = patients.appointment(
  on_or_after = "event_date1 + 1 day",
  returning = "date"
)
```
This is fine for events occurring on different days, but doesn't work for events occurring on the same day as if we used `on_or_after = "event_date1"` for `event_date2` then we'd just pick up `event_date1` again.

Even if we were able to use a higher time resolution in study definitions (eg making `on_or_after` queries to the nearest minute, say), `Appointment_Date` in the `OPA` table is only known to `YYYY-MM-DD` so this wouldn't help. 

What we do have in `OPA` is a unique appointment identifier, `OPA_Ident`. This provides an opportunity to identify _distinct_ appointments occurring on the same day in the same patient, but only if it is correctly ordered. 

There are 3 potential solutions:

* Ask for `Appointment_Date` to a higher time resolution, and update the the cohort extractor to be able to use eg `YYYY-DD-MM HH:MM:SS` in `on_or_before`/`on_or_after` queries.
* Use `OPA_Ident` to identify and query multiple same-day appointments, if possible
* Merge all same-day appointments into one super-appointment, and make queries on the super-appointment instead. 
Thanks. For the second option, what might the API look like?

For example:

```py
event_date1 = patients.appointment(
  on_or_after="index_date",
  returning = "date"
)

event_date2 = patients.appointment(
  on_or_after = "event_date1",
  returning = "date"
)

event_date3 = patients.appointment(
  on_or_after = "event_date2",
  returning = "date"
)
```
Same day events summary, as of today:

101,601,411 unique rows in the `OPA` table. Presumably each is a distinct out-patient appintment.

84,745,962 (83%)  of these were "single" appointments -- there were no other appointments for that patient on that day

16,855,449 (17%)  were "non-single" appointments -- there was at least one other appointment for that patient on that day

Top few are as follows:

| N appointments per day per patient | Appointments |
| ---: | ---: |
| 1  | 84,745,962 |
| 2  | 6,901,194 |
| 3 | 751,141 | 
| 4 | 133,027 | 
| 5 | 24,578 | 
| 6 | 9,783 | 
| >6 |  9,035,726 |

So there are 16,855,449 appointments that can't be queried unambiguously using the current study def API, because each has one or more sister appointments that occurred on the same day. This will be much lower if the query includes only a specific appointment type/specialty/etc (as will be true for the majority of use-cases, i think) but I still think it's big enough to have to worry about it.
Hi all, I've been looking at this & trying to figure out what we could do, in the context of (what I understand so far) cohort-extractor can do. 

@sebbacon I don't see how this would work 

```python
event_date1 = patients.appointment(
  on_or_after="index_date",
  returning = "date"
)

event_date2 = patients.appointment(
  on_or_after = "event_date1",
  returning = "date"
)

event_date3 = patients.appointment(
  on_or_after = "event_date2",
  returning = "date"
)
```

I suppose it would have to be 

```python
event_date1 = patients.appointment(
  on_or_after="index_date",
  sort_by="ident", # perhaps this is the default?
  same_day_appointment_index="1",
  returning = "date"
)

event_date2 = patients.appointment(
  on_or_after = "event_date1",
  sort_by="ident",
  same_day_appointment_index="2",
  returning = "date"
)

event_date3 = patients.appointment(
  on_or_after = "event_date2",
  sort_by="ident",
  same_day_appointment_index="3",
  returning = "date"
)
```

but it would require the study author to be able to determine how many same day appointments there are in the raw data, which seems excessive. 

I'm still struggling to get my head around whether we could return date & ident as a tuple from this proposed function (seems like no!) which would have been my first thought for something like this. I also don't know whether this would look too much like row-level data to be an allowable thing to do. 

```python
events = patients.appointment(
  on_or_after="index_date",
  returning = ["date", "ident"]
)
```

Also - @wjchulme what do the idents look like - do they look like increments (so sorting on them works in a useful way) or do they look like randomly generated things (e.g. guids) (so sorting on them is probably misleading) - I don't have access to TPP so can't look at that for myself. and/or does it matter whether same-day appointments are randomly ordered or not? if you're trying to find evidence of a causal link (this specific appointment triggered this vaccination) maybe it does?
Could we, in the first instance, limit the returning options to: date, binary flag and number of events in period? Then it doesn't matter if there are multiple appointments on the same day because the query output would be unaffected. It's only when you want to return information about the individual appointments (eg. outcome of attendance) where it becomes a problem but the use-cases are probably minimal if we build in plenty of filter options... e.g. We could add Consultation_Medium_Used as an input option if this is useful (e.g. if you have a virtual appointment this would not increase your potential exposure to covid)

We could also return procedure code, but we would need to add a filter for there being a procedure code present, in case patients had multiple appointments the same day, some of which had no procedures. There will still be some patients with multiple procedures the same day and we could only return one of these, but hopefully this is a small subset. The filter options should cover most bases on procedures though, so it won't usually be necessary to return this. 

Looks great. We may also want to use the data to indicate something about patients' clinical conditions (e.g. to infer seriousness of a diagnosis code that appears in primary care) and to look at activity trends during and after the pandemic. 

Other fields which are useful more generally:
- First_Attendance - indicates whether the appointment relates to a new referral or part of ongoing care (potentially useful for assessing trends in activity)
- Referral_Request_Received_Date - indicates for how long the treatment has been ongoing. 
- Outcome_of_Attendance - indicates whether this was the end of the treatment or not, or whether inpatient treatment was required
- Consultation_Medium_Used - face to face or not - obviously big covid-related changes here.
- Medical_Staff_Type_Seeing_Patient - can help narrow down type of treatment e.g for an appointment in trauma & orthopaedics is patient seeing a surgeon vs a nurse for a wound issue.
- Activity_Location_Type_Code - can determine whether the patient was seen in an NHS or private hospital (or other location such as a care home) - if seen in private hospital this could be a reasonable indicator of being seen/considered for elective surgery. Care home location could give new information about care home status of patient.

### On specialty / treatment function codes:

- Main specialty -- 84 different codes in our OPA dataset --252k nulls
- TFC --152 different codes -- 148k nulls
- Other than where both are null (101k appointments), nulls most often (but not always) occur where the other is a code starting with 6 or 7 (these seem to be generally mental health and psych - as a side note we won't have most MH attendances because these will be in community/MH Trusts which usually don't submit data to SUS)

#### Top 3 'specialty-TFC' combinations:
- 130-130 (ophthalmology) -- 6.5m
- 110-110 (Trauma & orthopaedics) --5.5m
- 960-650 (Allied Health Professional - Physiotherapy Service) -- 3.2m
#### next most common Specialty-TFC combinations (where they are different):
- 810-812 (Radiology - Diagnostic Imaging Service) --1.4m
- 100-103 (General Surgery - Breast surgery)--1.2m

Overall I'd say TFC is most useful because it's a bit more specific/descriptive, e.g. in the case of the most common non-identical combination `960-650 (Allied Health Professional - Physiotherapy Service)` the purpose of these appointments is completely obscure from the specialty of Allied Health Professional. 
Great. Is it possible to pin this down to a desired specification yet?

Probably the best way to do that is fill out Will's proposal (copied below) with all the desired `with_these_*` arguments, plus draft documentation that would describe the use adequately to end users. 

So for example how would we explain when to use which kind of specialty codes to researchers?

```
appointment_date = patients.outpatient_appointment_date(
	returning = "date",
	attended = True,
	with_these_treatment_codes = treatment_codelist,
	with_these_specialty_codes = specialty_codelist,
	with_these_procedure_codes = OPCS_codelist,
	on_or_after = "index_date",
	date_format="YYYY-MM-DD"
)

```
- `treatment` should be named `treatment function code` or `TFC` for clarity if we are using it. But probably we should pick one. I think TFC is more useful so we could drop specialty. 
- I would also add the list of fields I put above, in particular `is_first_attendance` would be a very useful filter as it can be used to capture a new referral, or count a period of treatment per person, and `Consultation_Medium_Used` as a returning option I think will be in demand.
Thanks @HelenCEBM , for the avoidance of doubt could you write this up as it would appear in the docs? 

For example, I think you're proposing the following (but with more fields)?

```
appointment_date = patients.outpatient_appointment_date(
	returning = "date",
	attended = True,
	with_these_treatment_function_codes=treatment_codelist,
	with_these_procedure_codes=OPCS_codelist,
        referral_request_received_date="YYYY-MM-DD",
	on_or_after = "index_date",
	date_format="YYYY-MM-DD"
)
```

I also don't quite understand what @wjchulme is proposing we do about multiple appointments on the same day.

Also it would be helpful to have a stab at what we'd say in the docs about procedure codes (coding, what we're querying and why we're not querying specialty, etc)

Thanks! I'll be getting an engineer who's not made variables before onto this, so making it really clear up front is helpful
```
appointment_date = patients.outpatient_appointment_date(
	returning = "date",
	attended = True,
        is_first_attendance = True,
	with_these_treatment_function_codes=tfc_codelist,
	with_these_procedure_codes=OPCS_codelist,
        referral_request_received_date="YYYY-MM-DD",
	on_or_after = "index_date",
	date_format="YYYY-MM-DD"
)
```

Additional returning options:
- Referral_Request_Received_Date 
- Outcome_of_Attendance
- Consultation_Medium_Used
- Medical_Staff_Type_Seeing_Patient
- Activity_Location_Type_Code
- There's also a diagnosis code but it is usually highly incomplete.

We should check if these fields are all populated but I don't really have time this week!
Re appointments on the same day

There is currently no way to use a study definition to reliably retrieve info from two or more distinct events (in this case appointments) that occur on the same day in the same patient. It's likely that there will be many such cases in out-patient appointment data, so this is a problem (I will check how often this occurs). 

The current workaround to retrieve consecutive events is to do something like

```py
event_date1 = patients.appointment(
  on_or_after="index_date",
  returning = "date"
)

event_date2 = patients.appointment(
  on_or_after = "event_date1 + 1 day",
  returning = "date"
)
```
This is fine for events occurring on different days, but doesn't work for events occurring on the same day as if we used `on_or_after = "event_date1"` for `event_date2` then we'd just pick up `event_date1` again.

Even if we were able to use a higher time resolution in study definitions (eg making `on_or_after` queries to the nearest minute, say), `Appointment_Date` in the `OPA` table is only known to `YYYY-MM-DD` so this wouldn't help. 

What we do have in `OPA` is a unique appointment identifier, `OPA_Ident`. This provides an opportunity to identify _distinct_ appointments occurring on the same day in the same patient, but only if it is correctly ordered. 

There are 3 potential solutions:

* Ask for `Appointment_Date` to a higher time resolution, and update the the cohort extractor to be able to use eg `YYYY-DD-MM HH:MM:SS` in `on_or_before`/`on_or_after` queries.
* Use `OPA_Ident` to identify and query multiple same-day appointments, if possible
* Merge all same-day appointments into one super-appointment, and make queries on the super-appointment instead. 
Thanks. For the second option, what might the API look like?

For example:

```py
event_date1 = patients.appointment(
  on_or_after="index_date",
  returning = "date"
)

event_date2 = patients.appointment(
  on_or_after = "event_date1",
  returning = "date"
)

event_date3 = patients.appointment(
  on_or_after = "event_date2",
  returning = "date"
)
```
Same day events summary, as of today:

101,601,411 unique rows in the `OPA` table. Presumably each is a distinct out-patient appintment.

84,745,962 (83%)  of these were "single" appointments -- there were no other appointments for that patient on that day

16,855,449 (17%)  were "non-single" appointments -- there was at least one other appointment for that patient on that day

Top few are as follows:

| N appointments per day per patient | Appointments |
| ---: | ---: |
| 1  | 84,745,962 |
| 2  | 6,901,194 |
| 3 | 751,141 | 
| 4 | 133,027 | 
| 5 | 24,578 | 
| 6 | 9,783 | 
| >6 |  9,035,726 |

So there are 16,855,449 appointments that can't be queried unambiguously using the current study def API, because each has one or more sister appointments that occurred on the same day. This will be much lower if the query includes only a specific appointment type/specialty/etc (as will be true for the majority of use-cases, i think) but I still think it's big enough to have to worry about it.
Hi all, I've been looking at this & trying to figure out what we could do, in the context of (what I understand so far) cohort-extractor can do. 

@sebbacon I don't see how this would work 

```python
event_date1 = patients.appointment(
  on_or_after="index_date",
  returning = "date"
)

event_date2 = patients.appointment(
  on_or_after = "event_date1",
  returning = "date"
)

event_date3 = patients.appointment(
  on_or_after = "event_date2",
  returning = "date"
)
```

I suppose it would have to be 

```python
event_date1 = patients.appointment(
  on_or_after="index_date",
  sort_by="ident", # perhaps this is the default?
  same_day_appointment_index="1",
  returning = "date"
)

event_date2 = patients.appointment(
  on_or_after = "event_date1",
  sort_by="ident",
  same_day_appointment_index="2",
  returning = "date"
)

event_date3 = patients.appointment(
  on_or_after = "event_date2",
  sort_by="ident",
  same_day_appointment_index="3",
  returning = "date"
)
```

but it would require the study author to be able to determine how many same day appointments there are in the raw data, which seems excessive. 

I'm still struggling to get my head around whether we could return date & ident as a tuple from this proposed function (seems like no!) which would have been my first thought for something like this. I also don't know whether this would look too much like row-level data to be an allowable thing to do. 

```python
events = patients.appointment(
  on_or_after="index_date",
  returning = ["date", "ident"]
)
```

Also - @wjchulme what do the idents look like - do they look like increments (so sorting on them works in a useful way) or do they look like randomly generated things (e.g. guids) (so sorting on them is probably misleading) - I don't have access to TPP so can't look at that for myself. and/or does it matter whether same-day appointments are randomly ordered or not? if you're trying to find evidence of a causal link (this specific appointment triggered this vaccination) maybe it does?
Could we, in the first instance, limit the returning options to: date, binary flag and number of events in period? Then it doesn't matter if there are multiple appointments on the same day because the query output would be unaffected. It's only when you want to return information about the individual appointments (eg. outcome of attendance) where it becomes a problem but the use-cases are probably minimal if we build in plenty of filter options... e.g. We could add Consultation_Medium_Used as an input option if this is useful (e.g. if you have a virtual appointment this would not increase your potential exposure to covid)

We could also return procedure code, but we would need to add a filter for there being a procedure code present, in case patients had multiple appointments the same day, some of which had no procedures. There will still be some patients with multiple procedures the same day and we could only return one of these, but hopefully this is a small subset. The filter options should cover most bases on procedures though, so it won't usually be necessary to return this. 


Fix patch:
diff --git a/cohortextractor/patients.py b/cohortextractor/patients.py
index bb1edbd9..b9b157cc 100644
--- a/cohortextractor/patients.py
+++ b/cohortextractor/patients.py
@@ -2135,3 +2135,47 @@ def with_healthcare_worker_flag_on_covid_vaccine_record(
         return_expectations: as described elsewhere.
     """
     return "with_healthcare_worker_flag_on_covid_vaccine_record", locals()
+
+
+def outpatient_appointment_date(
+    returning="binary_flag",
+    attended=None,
+    is_first_attendance=None,
+    with_these_treatment_function_codes=None,
+    on_or_after=None,
+    between=None,
+    date_format="YYYY-MM-DD",
+):
+    """
+    Return when the patient had an outpatient appointment
+
+    See https://github.com/opensafely-core/cohort-extractor/issues/492 for in-depth discussion and background.
+
+    Args:
+        returning: string indicating value to be returned. Options are:
+
+            * `binary_flag`: indicates if they have had an outpatient appointment or not
+            * `date`: earliest date of outpatient appointment within the specified period
+            * `number_of_matches_in_period`: number of outpatient appointments in period
+
+        attended: if True, filters appointments to only those where the patient
+            was recorded as being seen. If it is not known whether they attended
+            (e.g. NULL value), it is assumed that they did not attend.
+        is_first_attendance: if True, filter appointments to only those where
+            it is known whether it is a first attendance. If it is not known
+            (e.g. NULL value), it is assumed that it is not a first attendance.
+        with_these_treatment_function_codes: Filter the appointments to those
+            whose "specialty in which the consultant was working during the
+            period of care" matches the supplied codelist.
+        on_or_after: date of interest as a string with the format `YYYY-MM-DD`.
+            Filters results to on or after the given date. The default value is
+            `None`.
+        between: two dates of interest as a list with each date as a string
+            with the format `YYYY-MM-DD`. Filters matches to between the two
+            dates. The default value is `None`.
+        date_format: a string detailing the format of the dates to be returned.
+            It can be `YYYY-MM-DD`, `YYYY-MM` or `YYYY` and wherever possible
+            the least disclosive data should be returned. i.e returning only
+            year is less disclosive than a date with day, month and year.
+    """
+    return "outpatient_appointment_date", locals()
diff --git a/cohortextractor/process_covariate_definitions.py b/cohortextractor/process_covariate_definitions.py
index 455c5c71..1409308a 100644
--- a/cohortextractor/process_covariate_definitions.py
+++ b/cohortextractor/process_covariate_definitions.py
@@ -327,6 +327,9 @@ def type_of_with_healthcare_worker_flag_on_covid_vaccine_record(
     ):
         return self._type_from_return_value(returning)
 
+    def type_of_outpatient_appointment_date(self, returning, **kwargs):
+        return self._type_from_return_value(returning)
+
     def _type_from_return_value(self, returning):
         if returning == "nhse_region_name":
             raise ValueError(
diff --git a/cohortextractor/tpp_backend.py b/cohortextractor/tpp_backend.py
index 3634dad0..c4593f5b 100644
--- a/cohortextractor/tpp_backend.py
+++ b/cohortextractor/tpp_backend.py
@@ -2353,6 +2353,69 @@ def patients_with_healthcare_worker_flag_on_covid_vaccine_record(self, returning
           patient_id
         """
 
+    def patients_outpatient_appointment_date(
+        self,
+        attended=None,
+        is_first_attendance=None,
+        with_these_treatment_function_codes=None,
+        between=None,
+        returning="binary_flag",
+    ):
+        date_condition, date_joins = self.get_date_condition(
+            "OPA", "Appointment_Date", between
+        )
+
+        conditions = [date_condition]
+
+        if attended:
+            # codes from `ATTENDED` field in HES data dictionary
+            # https://github.com/opensafely-core/cohort-extractor/issues/492#issuecomment-888961963
+            attended_conditions = [
+                "Attendance_Status = 5",
+                "Attendance_Status = 6",
+            ]
+            conditions.append("(" + " OR ".join(attended_conditions) + ")")
+
+        if is_first_attendance:
+            # codes from `FIRSTATT` field in HES data dictionary
+            # https://github.com/opensafely-core/cohort-extractor/issues/492#issuecomment-889017544
+            is_first_attendance_conditions = [
+                "First_Attendance = 1",
+                "First_Attendance = 3",
+            ]
+            conditions.append("(" + " OR ".join(is_first_attendance_conditions) + ")")
+
+        if with_these_treatment_function_codes:
+            with_these_treatment_function_codes_conditions = codelist_to_sql(
+                with_these_treatment_function_codes
+            )
+            conditions.append(
+                f"""Treatment_Function_Code IN ({with_these_treatment_function_codes_conditions})"""
+            )
+
+        conditions = " AND ".join(conditions)
+
+        if returning == "binary_flag":
+            column_definition = "1"
+        elif returning == "date":
+            column_definition = "MIN(Appointment_Date)"
+        elif returning == "number_of_matches_in_period":
+            column_definition = "COUNT(OPA_Ident)"
+        else:
+            raise ValueError(f"Unsupported `returning` value: {returning}")
+
+        return f"""
+        SELECT
+          Patient_ID AS patient_id,
+          {column_definition} AS {returning}
+        FROM
+          OPA
+          {date_joins}
+        WHERE {conditions}
+        GROUP BY
+          Patient_ID
+        """
+
 
 class ColumnExpression:
     def __init__(


Test patch:
diff --git a/tests/test_tpp_backend.py b/tests/test_tpp_backend.py
index 6c764101..eb3923e4 100644
--- a/tests/test_tpp_backend.py
+++ b/tests/test_tpp_backend.py
@@ -3688,3 +3688,149 @@ def test_with_healthcare_worker_flag_on_covid_vaccine_record():
         hcw=patients.with_healthcare_worker_flag_on_covid_vaccine_record(),
     )
     assert_results(study.to_dicts(), hcw=["1", "1", "0", "0"])
+
+
+def _make_patient_with_outpatient_appointment():
+    """Makes patients with Outpatient Appointments"""
+    session = make_session()
+    session.add_all(
+        [
+            Patient(
+                OPAEpisodes=[
+                    OPA(
+                        Appointment_Date="2021-01-01 12:00:00.000",
+                        Attendance_Status="6",
+                        First_Attendance="1",
+                        Treatment_Function_Code="130",
+                    ),
+                ]
+            ),
+            Patient(
+                OPAEpisodes=[
+                    OPA(
+                        Appointment_Date="2021-01-01 12:00:00.000",
+                        Attendance_Status="7",
+                        First_Attendance="4",
+                        Treatment_Function_Code="180",
+                    ),
+                    OPA(
+                        Appointment_Date="2021-01-02 12:00:00.000",
+                        Attendance_Status="5",
+                        First_Attendance="2",
+                        Treatment_Function_Code="812",
+                    ),
+                ]
+            ),
+            Patient(
+                OPAEpisodes=[
+                    OPA(
+                        Appointment_Date="2021-01-02 12:00:00.000",
+                        Ethnic_Category="GF",
+                        Attendance_Status="3",
+                        First_Attendance="3",
+                        Treatment_Function_Code="180",
+                    ),
+                    OPA(
+                        Appointment_Date="2021-01-03 12:00:00.000",
+                        Ethnic_Category="GF",
+                        First_Attendance="4",
+                        Treatment_Function_Code="320",
+                    ),
+                ],
+            ),
+            Patient(),
+        ]
+    )
+    session.commit()
+
+
+def test_outpatient_appointment_date_returning_binary_flag():
+    _make_patient_with_outpatient_appointment()
+
+    study = StudyDefinition(
+        population=patients.all(),
+        opa=patients.outpatient_appointment_date(returning="binary_flag"),
+    )
+    assert_results(study.to_dicts(), opa=["1", "1", "1", "0"])
+
+
+def test_outpatient_appointment_date_returning_binary_flag_on_or_after():
+    _make_patient_with_outpatient_appointment()
+
+    study = StudyDefinition(
+        population=patients.all(),
+        opa=patients.outpatient_appointment_date(
+            returning="binary_flag", on_or_after="2021-01-02"
+        ),
+    )
+    assert_results(study.to_dicts(), opa=["0", "1", "1", "0"])
+
+
+def test_outpatient_appointment_date_returning_binary_flag_attended():
+    _make_patient_with_outpatient_appointment()
+
+    study = StudyDefinition(
+        population=patients.all(),
+        opa=patients.outpatient_appointment_date(
+            returning="binary_flag", attended=True
+        ),
+    )
+    assert_results(study.to_dicts(), opa=["1", "1", "0", "0"])
+
+
+def test_outpatient_appointment_date_returning_binary_flag_first_attendance():
+    _make_patient_with_outpatient_appointment()
+
+    study = StudyDefinition(
+        population=patients.all(),
+        opa=patients.outpatient_appointment_date(
+            returning="binary_flag", is_first_attendance=True
+        ),
+    )
+    assert_results(study.to_dicts(), opa=["1", "0", "1", "0"])
+
+
+def test_outpatient_appointment_date_returning_binary_flag_with_these_treatment_function_codes():
+    _make_patient_with_outpatient_appointment()
+
+    study = StudyDefinition(
+        population=patients.all(),
+        opa=patients.outpatient_appointment_date(
+            returning="binary_flag", with_these_treatment_function_codes=["812", "813"]
+        ),
+    )
+    assert_results(study.to_dicts(), opa=["0", "1", "0", "0"])
+
+
+def test_outpatient_appointment_date_returning_dates():
+    _make_patient_with_outpatient_appointment()
+
+    study = StudyDefinition(
+        population=patients.all(),
+        opa=patients.outpatient_appointment_date(returning="date"),
+    )
+    assert_results(study.to_dicts(), opa=["2021-01-01", "2021-01-01", "2021-01-02", ""])
+
+
+def test_outpatient_appointment_date_returning_dates_formatted():
+    _make_patient_with_outpatient_appointment()
+
+    study = StudyDefinition(
+        population=patients.all(),
+        opa=patients.outpatient_appointment_date(
+            returning="date", date_format="YYYY-MM"
+        ),
+    )
+    assert_results(study.to_dicts(), opa=["2021-01", "2021-01", "2021-01", ""])
+
+
+def test_outpatient_appointment_date_returning_number_of_matches_in_period():
+    _make_patient_with_outpatient_appointment()
+
+    study = StudyDefinition(
+        population=patients.all(),
+        opa=patients.outpatient_appointment_date(
+            returning="number_of_matches_in_period"
+        ),
+    )
+    assert_results(study.to_dicts(), opa=["1", "2", "2", "0"])
diff --git a/tests/tpp_backend_setup.py b/tests/tpp_backend_setup.py
index 374ac1d5..dc8ef7c3 100644
--- a/tests/tpp_backend_setup.py
+++ b/tests/tpp_backend_setup.py
@@ -732,7 +732,10 @@ class OPA(Base):
     )
     OPA_Ident = Column(Integer, primary_key=True)
     Appointment_Date = Column(Date)
+    Attendance_Status = Column(String)
     Ethnic_Category = Column(String)
+    First_Attendance = Column(String)
+    Treatment_Function_Code = Column(String)
 
 
 class DecisionSupportValue(Base):