Winter Planning 2023 — coHealth Kensington
Preventing ill health through vaccinations and implementing winter plans
Requirements
Proactively engage — with the support of Primary Health Network (PHN) grant - with vulnerable patients with the aim to:
provide information and support on seasonal illness and preventative measures
enable timely access to vaccinations to prevent seasonal illnesses such COVID-19, influenza and pneumococcal disease
plan for early testing and treatment for COVID-19
plan for managing deterioration of seasonal or chronic illness, including early access to antiviral treatments where appropriate.
Target populations
The target population comprises people who meet one or more of these criteria:
are under-vaccinated
have chronic or complex conditions
are aged over 50
are pregnant
identify as Aboriginal or Torres Strait Islander
have a body mass index of 30 or above (or, for children, who are above the 95th percentile)
are immunocompromised
are receiving chemotherapy.
Required activities
Conduct database searches to identify patients at risk of poor outcomes if they contract a seasonal illness or COVID-19.
Proactively reach out to the cohort of at-risk patients.
Aim to contact more than 200 individuals by telephone, and reach a minimum of 150, to provide
information and support on seasonal illness and preventative measures.
Organise follow-up appointments for those who require:
COVID-19, influenza or other vaccination; or
GP consultation to assess and plan for early antiviral prescription.
Develop COVID-19 readiness plans for consenting individuals.
Identify patients eligible for antiviral treatment if they contract COVID-19 or influenza, and put a
treatment plan in place.
Postulates
After discussion with coHealth Kensington general practice team members the following ideas, suggestions and postulates were discussed:
It was thought that people are more likely to be responsive to contacts if they thought themselves at increased perceived risk of serious consequences of infection. This was particularly thought to be the case if people are aged over 60 years.
- This also increased the opportunity to offer and provide other immunisations, such as pneumococcal, shingles, pertussis and influenza.
It was thought that people are more likely to be responsive to offers of immunization if they had already received 3 or 4 (as compared to 2, or less) COVID immunisations.
- We were also mindful, particularly in our messaging, of the possibility of “COVID-fatigue” with regard to COVID immunisation messaging.
It was thought that people who are of non-English speaking, refugee or asylum-seeker background may be vulnerable to not having received more than two COVID immunisations due to inadequate opportunity to receive appropriate and accurate immunisation information in their preferred language.
Response
- Database search of groups based on the postulates
- Overall, the search was for ‘active’ patients (Three visits or more in the past twenty-four months, and more than one visit in the previous twelve months) who were at increased risk of serioius consequences of infection. Populations-at-risk included on the basis of age, indigenous status or the presence of conditions such as immunodeficiency, respiratory or cardiovascular disease or pregnancy.
- The search groups were divided on the basis of age (age > 60 years), refugee or asylum seeker status, non-English speaking (‘NESB’) status (but no recorded refugee or asylum seeker status) and number of previous COVID vaccinations (0-1 vaccinations, 2 vaccinations, or 3-4 vaccinations).
- Likelihood of being of ‘non-English speaking background (NESB)’ depends on identification of recorded benefit from using interpreters/preferred language in the comments field of Best Practice. Recording of preferred language in this way is routine practice at the Kensington site of coHealth.
- Excluded from the search were those less than eighteen years of age, or who had a COVID immunisation or COVID illness in the previous six months i.e. not eligible for additional COVID immunisation.
- Search conducted via Best Practice search tool. The example SQL code is shown in Appendix 1 — searching for vulnerable populations (SQL code). The code was modified depending on the specific vulnerable sub-population being searched for.
Search results
Code
# read data files
classes <- c("numeric", rep("character", 24))
# read all columns, except first (INTERNALID), as character
# added columns
# 'Vax' - number of recorded COVID vaccinations
# 0 : 0 or 1
# 2 : 2
# 3 : 3 or more
# 'Refugee' - recorded refugee or asylum seeker background
# 'NESB' - recorded non-English speaking background (and not refugee)
# 'age60' - age 60 or above
d <-
(
read.csv(file = "Q:WinterVaxProject2023/COVID01vax 3visits24 1visit12 not refugeeORnesb high risk age lt 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 0,
Refugee = FALSE,
NESB = FALSE,
age60 = FALSE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID01vax 3visits24 1visit12 not refugeeORnesb high risk age ge 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 0,
Refugee = FALSE,
NESB = FALSE,
age60 = TRUE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID01vax 3visits24 1visit12 refugee high risk age lt 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 0,
Refugee = TRUE,
NESB = FALSE,
age60 = FALSE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID01vax 3visits24 1visit12 refugee high risk age ge 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 0,
Refugee = TRUE,
NESB = FALSE,
age60 = TRUE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID01vax 3visits24 1visit12 NESB not refugee high risk age lt 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 0,
Refugee = FALSE,
NESB = TRUE,
age60 = FALSE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID01vax 3visits24 1visit12 NESB not refugee high risk age ge 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 0,
Refugee = FALSE,
NESB = TRUE,
age60 = TRUE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID2vax 3visits24 1visit12 not refugeeORnesb high risk age lt 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 2,
Refugee = FALSE,
NESB = FALSE,
age60 = FALSE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID2vax 3visits24 1visit12 not refugeeORnesb high risk age ge 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 2,
Refugee = FALSE,
NESB = FALSE,
age60 = TRUE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID2vax 3visits24 1visit12 refugee high risk age lt 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 2,
Refugee = TRUE,
NESB = FALSE,
age60 = FALSE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID2vax 3visits24 1visit12 refugee high risk age ge 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 2,
Refugee = TRUE,
NESB = FALSE,
age60 = TRUE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID2vax 3visits24 1visit12 NESB not refugee high risk age lt 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 2,
Refugee = FALSE,
NESB = TRUE,
age60 = FALSE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID2vax 3visits24 1visit12 NESB not refugee high risk age ge 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 2,
Refugee = FALSE,
NESB = TRUE,
age60 = TRUE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID3or4vax 3visits24 1visit12 not refugeeORnesb high risk age lt 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 3,
Refugee = FALSE,
NESB = FALSE,
age60 = FALSE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID3or4vax 3visits24 1visit12 not refugeeORnesb high risk age ge 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 3,
Refugee = FALSE,
NESB = FALSE,
age60 = TRUE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID3or4vax 3visits24 1visit12 refugee high risk age lt 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 3,
Refugee = TRUE,
NESB = FALSE,
age60 = FALSE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID3or4vax 3visits24 1visit12 refugee high risk age ge 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 3,
Refugee = TRUE,
NESB = FALSE,
age60 = TRUE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID3or4vax 3visits24 1visit12 NESB not refugee high risk age lt 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 3,
Refugee = FALSE,
NESB = TRUE,
age60 = FALSE
)
) |>
rbind(
read.csv(file = "Q:WinterVaxProject2023/COVID3or4vax 3visits24 1visit12 NESB not refugee high risk age ge 60 20230321.csv", colClasses = classes) |>
mutate(
Vax = 3,
Refugee = FALSE,
NESB = TRUE,
age60 = TRUE
)
)Code
# create minimal data sets, summary sets
d_minimum <- d |>
# minimal data set
select(INTERNALID, Vax, Refugee, NESB, age60) |>
filter(Vax %in% c(2,3))
# only really interested in those who've had two or more COVID vaccinations
d_minimum_groups <- d_minimum |>
group_by(Vax, Refugee, NESB, age60) |>
# n is number of people in each group
# proportion is the size of n compared to the entire d_minimum group
summarise(n = n(), proportion = n()/nrow(d_minimum)) |>
ungroup()Zero or One previous recorded COVID immunisation
Not Refugee or asylum seeker or NESB age < 60 : 45
Not Refugee or asylum seeker or NESB age >= 60 : 50
Refugee or asylum seeker age < 60 : 21
Refugee or asylum seeker age >= 60 : 8
Non-English speaking background ‘NESB’, not Refugee or asylum seeker or NESB age < 60 : 4
Non-English speaking background ’NESB, not Refugee or asylum seeker or NESB age >= 60 : 20
Total 148
Two previous recorded COVID immunisations
Not Refugee or asylum seeker or NESB age < 60 : 35, (4.5%)
Not Refugee or asylum seeker or NESB age >= 60 : 32, (4.1%)
Refugee or asylum seeker age < 60 : 31, (3.9%)
Refugee or asylum seeker age >= 60 : 15, (1.9%)
Non-English speaking background ‘NESB’, not Refugee or asylum seeker or NESB age < 60 : 5, (0.6%)
Non-English speaking background ’NESB, not Refugee or asylum seeker or NESB age >= 60 : 11, (1.4%)
Total 129
Three or four previous recorded COVID immunisations
Not Refugee or asylum seeker or NESB age < 60 : 120, (15.3%)
Not Refugee or asylum seeker or NESB age >= 60 : 317, (40.3%)
Refugee or asylum seeker age < 60 : 90, (11.5%)
Refugee or asylum seeker age >= 60 : 53, (6.7%)
Non-English speaking background ‘NESB’, not Refugee or asylum seeker or NESB age < 60 : 12, (1.5%)
Non-English speaking background ’NESB, not Refugee or asylum seeker or NESB age >= 60 : 65, (8.3%)
Total 657
Selection
The target populations for the winter planning project are those with a risk of serious complication of respiratory infection, restricted to those who have had two more COVID vaccinations already). Particularly those aged 60 and above and those from a refugee/asylum-seeker or non-English speaking (NESB) background.
Plan
Group 1 (SMS and follow-up telephone contact)
Decrease by 8% the proportion of patients chosen who have three or four previous recorded COVID immunisations aged less than 60. Distribute an extra 1% to each of refugee/NESB subgroups.
Decrease by 4% the proportion of patients chosen who have three or four previous recorded COVID immunisations aged 60 or more, and distribute an extra 1% to each of refugee/NESB subgroups aged 60 years or more.
Code
# determine number of patients to choose from each group
d_minimum_groups <- d_minimum_groups |>
# arrange/sort
arrange(Vax, Refugee, NESB, age60) |>
# increase some sub-groups (age >= 60, Refugee, NESB) in comparison to others
mutate(
proportion_select = case_when(
(Refugee == TRUE | NESB == TRUE) & age60 == TRUE ~ proportion + 0.02,
(Refugee == TRUE | NESB == TRUE) ~ proportion + 0.01,
Refugee == FALSE & NESB == FALSE & age60 == FALSE & Vax == 3 ~ proportion - 0.08,
Refugee == FALSE & NESB == FALSE & age60 == TRUE & Vax == 3 ~ proportion - 0.04,
.default = proportion
)
) |>
mutate (
n_select = round(proportion_select * 200)
)Code
d_minimum_groups |>
kbl(
caption = "Number of patients sampled from each sub-group",
col.names = c("Number of COVID vaccinations", "Refugee or asylum seeker", "NESB", "age 60+", "n", "proportion", "selected proportion", "n selected"),
digits = 3) |>
kable_minimal(bootstrap_options = c("striped")) |>
add_header_above(c("Sub-group" = 6, "Selected" = 2)) |>
pack_rows("Two previous COVID vaccinations", 1, 6) |>
pack_rows("Three (or more) previous COVID vaccinations", 7, 12)Sub-group |
Selected |
||||||
|---|---|---|---|---|---|---|---|
| Number of COVID vaccinations | Refugee or asylum seeker | NESB | age 60+ | n | proportion | selected proportion | n selected |
| Two previous COVID vaccinations | |||||||
| 2 | FALSE | FALSE | FALSE | 35 | 0.045 | 0.045 | 9 |
| 2 | FALSE | FALSE | TRUE | 32 | 0.041 | 0.041 | 8 |
| 2 | FALSE | TRUE | FALSE | 5 | 0.006 | 0.016 | 3 |
| 2 | FALSE | TRUE | TRUE | 11 | 0.014 | 0.034 | 7 |
| 2 | TRUE | FALSE | FALSE | 31 | 0.039 | 0.049 | 10 |
| 2 | TRUE | FALSE | TRUE | 15 | 0.019 | 0.039 | 8 |
| Three (or more) previous COVID vaccinations | |||||||
| 3 | FALSE | FALSE | FALSE | 120 | 0.153 | 0.073 | 15 |
| 3 | FALSE | FALSE | TRUE | 317 | 0.403 | 0.363 | 73 |
| 3 | FALSE | TRUE | FALSE | 12 | 0.015 | 0.025 | 5 |
| 3 | FALSE | TRUE | TRUE | 65 | 0.083 | 0.103 | 21 |
| 3 | TRUE | FALSE | FALSE | 90 | 0.115 | 0.125 | 25 |
| 3 | TRUE | FALSE | TRUE | 53 | 0.067 | 0.087 | 17 |
- Randomly select patients from each sub-group
Code
# choose patients for group 1 from patient groups
set.seed(20230323)
# set random number seed
d_minimum$random <- replicate(nrow(d_minimum), runif(1))
# add random number to each patient for selection purposes
d_minimum_chosen <- NULL
for (v in c(2,3)) {
for (r in c(FALSE, TRUE)) {
for (nesb in c(FALSE, TRUE)) {
for (a in c(FALSE, TRUE)) {
if (!(r & nesb)) {
# both 'refugee' and 'nesb' is an illegal combination
d_minimum_chosen <- d_minimum_chosen |>
rbind(
d_minimum |>
filter(Vax == v, Refugee == r, NESB == nesb, age60 == a) |>
slice_max(
order_by = random,
n = d_minimum_groups |>
filter(
Vax == v,
Refugee == r,
NESB == nesb,
age60 == a) |>
pull(n_select),
with_ties = FALSE)
)
}
}
}
}
}Group 2 (SMS only)
Choose another group of patients, in the same proportion (as far as possible) as the first group
This group is initially only to be sent an SMS. If time and resources are available, they too will be contacted by telephone.
Code
# choose patients for group 2 from patient groups
d_minimum_available_forgroup2 <- d_minimum |>
# filter out those already chosen for group 1
filter(!(INTERNALID %in% (d_minimum_chosen |> pull(INTERNALID))))
d_minimum_chosen_2 <- NULL
for (v in c(2,3)) {
for (r in c(FALSE, TRUE)) {
for (nesb in c(FALSE, TRUE)) {
for (a in c(FALSE, TRUE)) {
if (!(r & nesb)) {
# both 'refugee' and 'nesb' is an illegal combination
d_minimum_chosen_2 <- d_minimum_chosen_2 |>
rbind(
d_minimum_available_forgroup2 |>
filter(Vax == v, Refugee == r, NESB == nesb, age60 == a) |>
slice_max(
order_by = random,
n = d_minimum_groups |>
filter(
Vax == v,
Refugee == r,
NESB == nesb,
age60 == a) |>
pull(n_select),
with_ties = FALSE)
)
}
}
}
}
}Sub-group composition of group 2:
Code
# show table of selected patients from each sub-group
d_minimum_chosen_2 |>
group_by(Vax, Refugee, NESB, age60) |>
summarise(n = n()) |>
kbl(
caption = "Number of patients sampled from each sub-group for group 2",
col.names = c("Number of COVID vaccinations", "Refugee or asylum seeker", "NESB", "age 60+", "n selected"),
digits = 3) |>
kable_minimal(bootstrap_options = c("striped")) |>
add_header_above(c("Sub-group" = 4, "Selected" = 1)) |>
pack_rows("Two previous COVID vaccinations", 1, 6) |>
pack_rows("Three (or more) previous COVID vaccinations", 7, 12)Sub-group |
Selected |
|||
|---|---|---|---|---|
| Number of COVID vaccinations | Refugee or asylum seeker | NESB | age 60+ | n selected |
| Two previous COVID vaccinations | ||||
| 2 | FALSE | FALSE | FALSE | 9 |
| 2 | FALSE | FALSE | TRUE | 8 |
| 2 | FALSE | TRUE | FALSE | 2 |
| 2 | FALSE | TRUE | TRUE | 4 |
| 2 | TRUE | FALSE | FALSE | 10 |
| 2 | TRUE | FALSE | TRUE | 7 |
| Three (or more) previous COVID vaccinations | ||||
| 3 | FALSE | FALSE | FALSE | 15 |
| 3 | FALSE | FALSE | TRUE | 73 |
| 3 | FALSE | TRUE | FALSE | 5 |
| 3 | FALSE | TRUE | TRUE | 21 |
| 3 | TRUE | FALSE | FALSE | 25 |
| 3 | TRUE | FALSE | TRUE | 17 |
Code
# add group 1 and group 2 tags to original dataset
d <- d |>
mutate(
phase = case_when(
INTERNALID %in% (d_minimum_chosen |> pull(INTERNALID)) ~ 1,
INTERNALID %in% (d_minimum_chosen_2 |> pull(INTERNALID)) ~ 2,
.default = NA
)
)Details of Group 1 patients stored in file “COVID High Risk Group 1 20230321.csv”.
Details of Group 2 patients stored in file “COVID High Risk Group 2 20230321.csv”
Code
# wrote out CSV files
write.csv(
d |>
filter(phase == 1) |>
# only group 1
left_join(
d_minimum |> select(INTERNALID, random),
by = c("INTERNALID")
) |>
arrange(desc(random)) |>
# arrange by 'random', as stored in 'd_minimum'
select(-random),
file = "Q:WinterVaxProject2023/COVID High Risk Group 1 20230321.csv"
)
write.csv(
d |>
filter(phase == 2) |>
# only group 1
left_join(
d_minimum |> select(INTERNALID, random),
by = c("INTERNALID")
) |>
arrange(desc(random)) |>
# arrange by 'random', as stored in 'd_minimum'
select(-random),
file = "Q:WinterVaxProject2023/COVID High Risk Group 2 20230321.csv"
)Appendix
Appendix 1 — searching for vulnerable populations (SQL code)
Example SQL code to search for vulnerable populations.
Code modified depending on specific sub-population being searched for.
SELECT * FROM BPS_Patients
WHERE StatusText = 'Active'
AND InternalID IN (SELECT InternalID
FROM Visits v
INNER JOIN
(VALUES('%bhagwat%'),('%fong%'),('%ekanayake%'),('%tyrrell%'),
('%ziegler%'),('%samarawickrama%'),('%obeyesekere%'),('%chaves%'),
('%lambrou%'),('%summers%'), ('%jung%'), ('%kaur%'),
('%ryan%'),('%mikhail%'),('%bullen%'),('%buckwell%'),
('%luciana%'),('%rohini%'),('%carroll%'))
/* clinician name. can be a 'partial' name */
AS ProviderName(Name)
ON v.DrName LIKE ProviderName.Name
WHERE VisitDate BETWEEN DateAdd(Month,-24,GetDate()) AND GetDate()
AND RecordStatus = 1
AND VisitCode <> 12 /* non-visit */
GROUP BY InternalID
HAVING count(InternalID) >= 3
-- at least three visits in the past 24 month period
)
AND InternalID IN (SELECT InternalID
FROM Visits v
INNER JOIN
(VALUES('%bhagwat%'),('%fong%'),('%ekanayake%'),('%tyrrell%'),
('%ziegler%'),('%samarawickrama%'),('%obeyesekere%'),('%chaves%'),
('%lambrou%'),('%summers%'), ('%jung%'), ('%kaur%'),
('%ryan%'),('%mikhail%'),('%bullen%'),('%buckwell%'),
('%luciana%'),('%rohini%'),('%carroll%'))
/* clinician name. can be a 'partial' name */
AS ProviderName(Name)
ON v.DrName LIKE ProviderName.Name
WHERE VisitDate BETWEEN DateAdd(Month,-12,GetDate()) AND GetDate()
AND RecordStatus = 1
AND VisitCode <> 12 /* non-visit */
GROUP BY InternalID
HAVING count(InternalID) >= 1
-- at least one visit in the past 12 month period
)
AND InternalID IN (
SELECT InternalID FROM BPSPatients.dbo.IMMUNISATIONS
WHERE VaccineID IN (
SELECT VaccineID FROM BPSDrugs.dbo.VACCINE_DISEASE WHERE DiseaseCode = 34
/* has had COVID-19 vaccination */
)
AND RECORDSTATUS = 1 /* not a deleted vaccine record */
GROUP BY InternalID
HAVING (count(*) = 2 OR (count(*) = 1 AND max(Sequence) = 2)) AND max(GivenDate) < DATEADD(month, -6, GetDate())
-- HAVING ((count(*) BETWEEN 3 AND 4) OR ((count(*) + min(Sequence)) > 4) AND max(GivenDate) < DATEADD(month, -6, GetDate()))
/* count of 3 vaccines OR minimum sequence count plus count suggests minimum 3 vaccines */
/* AND the most recent given date is at least two months ago */
)
AND InternalID IN (
SELECT InternalID FROM PastHistory WHERE ItemCode IN (13154, 13155) AND RecordStatus = 1
-- refugee or asylum seeker
)
/* AND (
-- requires interpreter
InternalID IN (
SELECT InternalID FROM BPSPatients.dbo.APPOINTMENTNOTES apptnote
INNER JOIN
(VALUES('%somali%'),('%mandarin%'),('%tamil%'),('%dari%'),('%kurdish%'),('%persian%'),('%farsi%'),('%cantonese%'),('%vietnamese%'),
('%arabic%'),('%amharic%'),('%oromo%'),('%tigri%'),('%tigre%'),('%somali%'),('%spanish%'),('%italian%'),('%greek%'))
AS LanguageName(Name)
on apptnote.APPOINTMENTNOTE LIKE LanguageName.Name
) OR
InternalID IN (
SELECT InternalID FROM BPSPatients.dbo.CLINICAL clinical
INNER JOIN
(VALUES('%somali%'),('%mandarin%'),('%tamil%'),('%dari%'),('%kurdish%'),('%persian%'),('%farsi%'),('%cantonese%'),('%vietnamese%'),
('%arabic%'),('%amharic%'),('%oromo%'),('%tigri%'),('%tigre%'),('%somali%'),('%spanish%'),('%italian%'),('%greek%'))
AS LanguageName(Name)
on clinical.OTHERCOMMENT LIKE LanguageName.Name
)
) */
AND InternalID NOT IN (
-- COVID-19 infection within the past six months (of a specified date)
Select InternalID FROM PastHistory WHERE
ItemCode = 13338 AND -- past history of COVID-19 infection
DATEFROMPARTS(
CASE WHEN Year > 0 THEN Year ELSE 1 END,
-- dates in PastHistory can be stored with month/day recorded as 'zero' i.e. no month or day
-- need to convert those numbers to '1' because DATEFROMPARTS does not handle zero inputs
CASE WHEN Month > 0 THEN Month ELSE 1 END,
CASE WHEN Day > 0 THEN Day ELSE 1 END
) > DateAdd(Month, -6, '20230325')
)
AND
-- minimum age 18 years
DOB < DateAdd(Year, -18, GetDate())
AND (
-- age 60 years or more
(DOB > DateAdd(Year,-60,GetDate()))
AND (
-- indigenous
Ethnicity IN ('Aboriginal', 'Torres Strait Islander', 'Aboriginal/Torres Strait Islander')
-- lymphoma
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 2222 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 5054 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 2223 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 6541 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 2224 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 2225 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 2226 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 6426 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 5595 AND RecordStatus = 1)
-- leukaemias
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 2173 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 2174 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 2175 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 2176 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 2177 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 2178 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 2179 AND RecordStatus = 1)
-- recent chemotherapy or radiotherapy
-- Past history 'date' is not in standard SQL date format
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 2862 AND Year = 2021 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 5775 AND Year = 2021 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 2955 AND Year = 2021 AND RecordStatus = 1)
-- organ transplants
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 4160 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 3691 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 3814 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 3826 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 12026 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 3765 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 3989 AND RecordStatus = 1)
-- on certain medications
OR InternalID In (SELECT InternalID FROM BPSPatients.dbo.CURRENTRX
WHERE RxStatus IN (1, 2) -- long or short term. 3 is no longer in list
AND RecordStatus = 1 -- active. 0 and 2 are no longer valid
AND ProductID IN (
SELECT ProductID FROM BPSDrugs.dbo.PRODUCTS p
INNER JOIN
(VALUES('%mycophenylate%'), ('%methotrexate%'), ('%leflunomide%'), ('%azathioprine%'),
('%mercaptopurine%'), ('%cyclophosphamide%'), ('%chlorambucil%'), ('%cyclosporin%'),
('%tacrolimus%'),
('%fingilomod%'), ('%alemtuzumab%'), ('%eculizumab%'), ('%abatacept%')
)
-- medication names - can be 'partial'
AS SelectDrugNames(Name)
ON p.GenericName LIKE SelectDrugNames.Name
)
)
-- primary immunodificiencies
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 1547 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 6292 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 1548 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 1549 AND RecordStatus = 1)
-- HIV
-- note that ATAGI guidelines actually state CD4<250, which should be a minority of patients now?
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 1727 AND RecordStatus = 1)
-- haemodialysis or peritoneal dialysis
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 2882 AND RecordStatus = 1)
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 2853 AND RecordStatus = 1)
-- diabetes
OR InternalID IN (
SELECT InternalID FROM PastHistory WHERE
ItemCode IN (
3, 775, 776, 778, 774, 7840, 11998 -- diabetes codes
)
AND
RecordStatus = 1
)
-- asthma/lung disease
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE
ItemCode IN (
281, 285, 283, 284, 282, 598, 4740, 414, 702 -- asthma and lung disease codes
)
AND
RecordStatus = 1
)
-- pregnancy
OR InternalID IN (
SELECT InternalID FROM Pregnancies
WHERE EndDate IS NULL
AND NOT (
EDCbyDate < DateAdd(Week, -2, GetDate())
)
)
-- cardiovascular
OR InternalID IN (SELECT InternalID FROM PastHistory WHERE
ItemCode IN (
226, 227, 228, 2376, 2377, 2378, 2379, 2380, 2381, 2382, 3576, 3577, 3578, 3579, 1534, 2556, 6847, 7847,
1480, 3083, 777, 1522, 677, 678, 679, 680, 681, 1522
)
AND
RecordStatus = 1
)
OR InternalID IN (SELECT InternalID FROM BPS_Observations WHERE
ObservationName = 'BMI'
AND TRY_CAST(ObservationValue as float) >= 30
AND ObservationDate >= DateAdd(Month, -12, GetDate())
-- BMI obese, observation date within the past twelve months
)
)
)
ORDER BY Surname, Firstname, DOB