Last updated: 03 August 2020
This article provides information on database queries you can run to assist with managing COVID-19, and on useful reports for finding COVID-19 related statistics at your practice.
Patients with a continuous history of face to face visits
Find telehealth and COVID-19 statistics for your practice
IMPORTANT These queries are intended for a specific purpose. While Best Practice Software indicate where some values can be modified to include or exclude patients, such as age or time period, queries should not be modified and used for different purposes than that stated below.
Search for at-risk patients
Right-click here and select Save Target as or Save Link As to download the query file to a known location.
The query identifies any active or inactive patients who have an appointment booked in the next three months and fall into at least one of the categories below. Your practice might want to run this query to anticipate any patients coming into your practice that are in a higher risk group for COVID-19, and organise an alternate consultation method such as a telehealth consultation.
- Currently on an immunosuppressant drug
- 70 years or older
- ATSI and 50 or older
- Currently pregnant
- Lives with a child under 12 months
- Currently has one of the following chronic diseases:
- hypertension
- heart disease
- cardiovascular disease
- cerebrovascular
- stroke
- TIA
- peripheral arterial
- diabetes
- COPD
- chronic obstructive pulmonary disease
- emphysema
- hepatitis B
- cancer
- malignancy
- HIV
- immune deficiency
- obesity
- respiratory disease
- asthma
- chronic bronchitis
- cystic fibrosis
- bronchiectasis.
Can I change the values to look for in the query?
Yes. You should save the original query as a backup somewhere for reference. The text of the query is included below.
Comments (indicated by --) show where you can update values to change the patients you are looking for, such as the age of patients, or include additional conditions.
SELECT *
FROM BPS_Patients
WHERE StatusText != 'Deceased'
AND
-- Have an appointment booked in the next 3 months
InternalID IN ( SELECT InternalID
FROM Appointments
WHERE RecordStatus = 1
-- Change the bolded number to return patients with an appointment within the next x number of months
AND AppointmentDate BETWEEN CAST(GETDATE() AS Date) AND CAST(DATEADD(MONTH, 3, GETDATE()) AS DATE))
AND
-- And fall into one of the following categories
(
-- Currently on an immunosuppressant drug
InternalID IN ( SELECT InternalID
FROM CurrentRx
WHERE RecordStatus = 1
AND ProductID IN ( SELECT Product_ATC.PRODUCTID
FROM bpsdrugs..Product_ATC INNER JOIN bpsdrugs..Products ON Product_ATC.ProductID = Products.ProductID
LEFT OUTER JOIN bpsdrugs..ProductNames ON Products.ProductNameID = ProductNames.ProductNameID
RIGHT OUTER JOIN bpsdrugs..Availability ON Products.ProductID = Availability.ProductID
WHERE Products.RecordStatus = 1
AND ProductNames.RecordStatus = 1
AND Availability.RecordStatus = 1
AND Product_ATC.ATCCode LIKE 'L04%'
))
OR
-- 70 and over. Change the bolded number to look for older or younger patients.
DOB < DateAdd(Year, -70, GetDate())
OR
-- ATSI 50 and over
(
Ethnicity IN ('Aboriginal', 'Torres Strait Islander', 'Aboriginal/Torres Strait Islander')
AND DOB < DateAdd(Year, -50, GetDate())
)
OR
-- Have an active chronic disease
InternalID IN ( SELECT InternalID
FROM PastHistory
WHERE RecordStatus = 1
AND StatusCode = 1
AND (
ItemText LIKE '%hypertension%'
OR ItemText LIKE '%heart disease%'
OR ItemText LIKE '%cardiovascular disease%'
OR ItemText LIKE '%cerebrovascular%'
OR ItemText LIKE '%stroke%'
OR ItemText LIKE 'TIA'
OR ItemText Like 'TIA %'
OR ItemText LIKE '% TIA%'
OR ItemText LIKE '% TIA %'
OR ItemText LIKE '%peripheral arterial%'
OR ItemText LIKE '%diabetes%'
OR ItemText LIKE '%COPD%'
OR ItemText LIKE '%chronic obstructive pulmonary disease%'
OR ItemText LIKE '%emphysema%'
OR ItemText LIKE '%hepatitis B%'
OR ItemText LIKE '%cancer%'
OR ItemText LIKE '%malignancy%'
OR ItemText LIKE '%HIV%'
OR ItemText LIKE '%immune deficiency%'
OR ItemText LIKE '%obesity%'
OR ItemText LIKE '%respiratory disease%'
OR ItemText LIKE '%asthma%'
OR ItemText LIKE '%chronic bronchitis%'
OR ItemText LIKE '%cystic fibrosis%'
OR ItemText LIKE '%bronchiectasis%'
-- Copy and add one of the lines above to include additional chronic diseases
)
)
OR
-- Currently pregnant
InternalID IN (SELECT InternalID FROM Pregnancies WHERE NominalLMP >= DateAdd(Month, -11, GetDate()) AND EndDate IS NULL AND RecordStatus = 1 and outcomecode = 0)
OR
-- Live with a child under 12 months
(
Address1 + Address2 + City + PostCode IN (
SELECT Address1 + Address2 + City + PostCode
FROM Patients
WHERE RecordStatus = 1
AND DOB > DateAdd(Year, -1, GetDate())
AND ISNULL(Address1, '') != ''
)
AND DOB !> DateAdd(Year, -1, GetDate())
))
ORDER BY surname, firstname
Patients with a continuous history of face to face visits
Right-click here and select Save Target as or Save Link As to download the query file to a known location.
This query returns patients who have had a physical visit to your practice (not a telehealth or telephone consult) in the last 12 months, and who have an appointment booked within the next month. The query might be useful for practices booking telehealth consultations after the July 20 updates to telehealth rules.
Save a copy of the query as a backup and modify where indicated in the query text below.
SELECT *
FROM BPS_Patients
WHERE StatusText = 'Active'
AND InternalID IN (SELECT InternalID
FROM Visits
WHERE RecordStatus = 1
AND VisitCode IN (1, 2, 3, 4, 8, 9, 10, 11) -- Additional visit codes can be added/removed as needed
AND VisitDate > DATEADD(Month, -12, GETDATE()))
AND InternalID IN (SELECT InternalID
FROM Appointments
WHERE RecordStatus = 1
AND AppointmentType NOT IN (SELECT AppointmentCode
FROM AppointmentTypes
WHERE Description IN ('Telehealth Consult', 'Telephone consult', 'Teleconference')) -- Appointment types to be excluded can be added/removed as needed
AND AppointmentDate BETWEEN CAST(GETDATE() AS Date)
AND CAST(DATEADD(MONTH, 1, GETDATE()) AS DATE)) -- To change future appointment date range change 1 to number of months required
ORDER BY surname, firstname
-- Active patients that have had a face to face visit in the last 12 months and have an in person appointment booked in the next month
Find telehealth and COVID-19 statistics for your practice
You can use Bp Premier reports to look up how many telehealth appointments your practice has performed, or how many patients presenting with COVID-19 symptoms your practice has treated.
The Appointments (grouped by type) report can provide statistics on telehealth appointments.
The Services - by Account Type (grouped by item) report can provide a statistics on the number of patients billed to an MBS number.
