A Appendix A - SQL code

A.1 Finding patients eligible for the study - SQL code

Find eligible patients (female, age criteria), who are active patients (defined by three or more contacts in the past two years, including one billed visit in the past six months), who have not had cervical screening detected found in the PapSmear table or the Investigations table.

Note that there is another table ‘ObGyn’ which should contain the most recent cervical screening result, but unfortunately, ‘over-chose’ 5 patients (found 295 patients, instead of ‘290’ as found using this search.)

SELECT *

FROM BPS_Patients
WHERE StatusText = 'Active'
AND Sex = 'Female'
AND DOB BETWEEN  DateAdd(Year,-75,'20190301') AND DateAdd(Year,-25,'20190301')

AND InternalID IN (SELECT InternalID
FROM Visits v
INNER JOIN (VALUES('%bhagwat%'),('%fong%'),('%ekanayake%'),('%shoesmith%'),
('%plastow%'),('%samarawickrama%'),('%obeyesekere%'),('%chaves%'),
('%ryan%'),('%mikhail%'),('%haynes%'),('%buckwell%'),('%maxwell%'),
('%grace ho%'))
AS ProviderName(Name)
ON v.DrName LIKE ProviderName.Name

WHERE VisitDate BETWEEN DateAdd(Year,-2,'20190301') AND '20190301'
AND RecordStatus = 1

GROUP BY internalid
HAVING count(internalid) >= 3)

AND InternalID IN (SELECT InternalID
FROM Invoices WHERE InvoiceID IN (SELECT InvoiceID
FROM Services
WHERE Recordstatus = 1
AND Servicedate > DateAdd(Month, -6, '20190301'))) 

AND InternalID NOT IN (SELECT InternalID
FROM PapSmears
WHERE PapDate > DATEADD(Month, -45, '20190301')
-- CST not extremely overdue (>45 months old)
)
AND InternalID NOT IN (SELECT InternalID
FROM Investigations
WHERE (
TestName LIKE '%CERVICAL SCREENING%'
OR TestName LIKE '%PAP SMEAR%')
AND ReportDate >  DATEADD(Month, -45, '20190301')
-- alternative search for Cervical Screening in Investigations (does not always appear in PAP table)
)
AND InternalID NOT IN (SELECT InternalID
FROM ObsGynDetail
WHERE NoPap=1
-- for some reason, Pap/CST marked as no longer required
)

ORDER BY surname, firstname

A.1.1 Additional code to detect if recorded history of refugee or asylum seeker status

AND (
InternalID IN
(SELECT InternalID FROM PastHistory WHERE ItemCode = 13155 AND RecordStatus = 1)
OR 
InternalID IN 
(SELECT InternalID FROM PastHistory WHERE ItemCode = 13154 AND RecordStatus = 1)
)

A.1.2 Additional code to detect if contacted by a specific clinician (Dr Buddini).

AND InternalID IN (SELECT InternalID
FROM Visits v
INNER JOIN (VALUES('%ekanayake%'))
AS ProviderName(Name)
ON v.DrName LIKE ProviderName.Name
WHERE VisitDate BETWEEN DateAdd(Year,-2,'20190301') AND '20190301'
AND RecordStatus = 1

GROUP BY internalid
HAVING count(internalid) >= 1)

A.2 Historical cervical screening rates among the under-screened - SQL code

Code finds ‘active’ patients (5 contacts in two years, include one contact within the previous six months) preceding 1st March 2018. Excludes patients who are marked for ‘no cervical screening’.

The ‘recent’ visit definition is different to seeking patients eligible for the study, because no billing data is available prior to June 2018.

Further restriction to patients who had a cervical screening test in the next three months.

SELECT *

FROM BPS_Patients
WHERE StatusText = 'Active'
AND Sex = 'Female'
AND DOB BETWEEN  DateAdd(Year,-75,'20180301') AND DateAdd(Year,-25,'20180301')

AND InternalID IN (SELECT InternalID
FROM Visits v
INNER JOIN (VALUES('%bhagwat%'),('%fong%'),('%ekanayake%'),('%shoesmith%'),
('%plastow%'),('%samarawickrama%'),('%obeyesekere%'),('%chaves%'),
('%ryan%'),('%mikhail%'),('%haynes%'),('%buckwell%'),('%maxwell%'),
('%grace ho%'))
AS ProviderName(Name)
ON v.DrName LIKE ProviderName.Name

WHERE VisitDate BETWEEN DateAdd(Year,-2,'20180301') AND '20180301'
AND RecordStatus = 1

GROUP BY internalid
HAVING count(internalid) >= 3)

AND InternalID IN (SELECT InternalID
FROM Visits v
INNER JOIN (VALUES('%bhagwat%'),('%fong%'),('%ekanayake%'),('%shoesmith%'),
('%plastow%'),('%samarawickrama%'),('%obeyesekere%'),('%chaves%'),
('%ryan%'),('%mikhail%'),('%haynes%'),('%buckwell%'),('%maxwell%'),
('%grace ho%'))
AS ProviderName(Name)
ON v.DrName LIKE ProviderName.Name

WHERE VisitDate BETWEEN DateAdd(Month,-6,'20180301') AND '20180301'
AND RecordStatus = 1

GROUP BY internalid
HAVING count(internalid) >= 1)

AND InternalID NOT IN (SELECT InternalID
FROM PapSmears
WHERE PapDate > DATEADD(Month, -45, '20180301')
AND PapDate < '20180301'
-- CST not extremely overdue (>45 months old)
)
AND InternalID NOT IN (SELECT InternalID
FROM Investigations
WHERE (
TestName LIKE '%CERVICAL SCREENING%'
OR TestName LIKE '%PAP SMEAR%')
AND ReportDate >  DATEADD(Month, -45, '20180301')
AND ReportDate <  '20180301'

-- alternative search for Cervical Screening in Investigations (does not always appear in PAP table)
)
AND (InternalID IN (SELECT InternalID
FROM PapSmears
WHERE PapDate >= '20180301'
AND PapDate < DATEADD(Month, 3, '20180301')
-- CST done in next three months
)
OR InternalID IN (SELECT InternalID
FROM Investigations
WHERE (
TestName LIKE '%CERVICAL SCREENING%'
OR TestName LIKE '%PAP SMEAR%')
AND ReportDate >  '20180301'
AND ReportDate <  DATEADD(Month, 3, '20180301')

-- alternative search for Cervical Screening in Investigations (does not always appear in PAP table)
))
AND InternalID NOT IN (SELECT InternalID
FROM ObsGynDetail
WHERE NoPap=1
-- for some reason, Pap/CST marked as no longer required
)

ORDER BY surname, firstname