This article provides information on database queries you can run to assist with managing COVID-19, and booking vaccinations.
If the queries provided do not suit your requirements, the Development Services team can assist with custom queries. Some customisation instructions are available for the below queries in the query description.
Click a heading to see the full description for that query, or click here to download the query. You may need to right-click and select Save Target As or Save Link As. Because the query files contain SQL, some browsers may alert that the file is a security risk. You can safely download these files.
You may also wish to make use of Bp Premier's Reporting on telehealth and COVID-19 statistics . These are useful reports to run on telehealth and related billing.
Find patients who have not had 2nd, 3rd or 4th doses
Queries based on patient age
Find non-regular patients
Patients who have had one or both doses with the practice
Description | Download |
|---|---|
The list of patients who have had BOTH vaccines with the practice | |
Billing-related queries
At-risk patients
Description | Download |
|---|---|
Patients with other conditions in their past history
Description | Download |
|---|---|
Patients who have had both vaccine doses and have a cancer recorded in their past history |
Reporting on telehealth and COVID-19 statistics
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.
List of patients by count of COVID Vaccines received
Returns a list of patients by count of COVID Vaccines received.
Right-click here and select Save Target as or Save Link As to download the query file PatientsByCountofCOVIDVaccinesReceived.sql to a known location.
Patients who have had two or less doses of a COVID vaccine
Returns all patients who have had two or less doses of a COVID vaccine
Right-click here and select Save Target as or Save Link As to download the query file 2OrLessVaccines.sql to a known location.
Patients over 50 who had their 2nd shot over 4 months ago, but have not had a 3rd shot
Returns all patients over 50 years of age with two COVID vaccination recorded with the last shot more than four months prior, but no third vaccination has been recorded.
Right-click here and select Save Target as or Save Link As to download the query file BOver50Shot2_4months_NoBooster.sql to a known location.
Booked for upcoming 1st Covid dose and under age of 60
Returns all patients booked at your practice for upcoming appointments for an appointment type like “Covid 1st Dose” and under the age of 60 years.
Right-click here and select Save Target as or Save Link As to download the query file BookedForCovid1stDoseAndUnder60.sql to a known location.
Had Both Vaccines with Practice
Returns all patients recorded as having received both vaccination doses at your practice.
Right-click here and select Save Target as or Save Link As to download the query file HadBothVaccineswithPractice.sql to a known location.
Had Both Vaccines With Practice (second dose given within specified date range)
Returns all patients recorded as having received both vaccination doses at your practice, within a specified date range.
Right-click here and select Save Target as or Save Link As to download the query file HadBothVaccinesWithPracticeDateRange.sql to a known location.
Had 1st but not 2nd dose
Returns all patients recorded as receiving the first dose of a Covid vaccination at your practice, but no second dose.
Right-click here and select Save Target as or Save Link As to download the query file HadFirstbutnot2ndDose.sql to a known location.
Patients booked for upcoming appointments for appointment type like “Covid 1st Dose” under 60
This query returns all patients who are under 60 years old and currently booked in for their first Covid Vaccine (including all vaccine types).
Right-click here and select Save Target as or Save Link As to download the query file CovidUnder60Appt.sql to a known location.
Patients eligible for Phase 1b Covid vaccinations
Right-click here and select Save Target as or Save Link As to download the query PatientsEligibleForPhase1b.sql to a known location.
The query in this file may be too large to run through the Import function of the Database Search screen. If so, copy and paste the text into the database search manually. Open the .sql file in a text editor such as Notepad. Press Ctrl+A to select all the text in the file, Ctrl+C to copy the text, and Ctrl+V to paste the text into the SQL Query field of the Database Search screen.
Returns
All active patients that match any of the following conditions:
- 50 years or older (can be toggled)
- 70 years or older
- Aboriginal or Torres Strait Islander and 55 years or older
- Is a healthcare, aged care, disability or critical and high risk worker (1)
- ADF status set to either of the following
- Current Australian Defence Force - Permanent member
- Current Australian Defence Force - Reserves
Has an underlying medical condition that is currently active in Past History:
- Haematological diseases or cancers
- Non-haematological cancers within the past 5 years
- Adult survivor of childhood cancer (i.e. had cancer prior to turning 18)
- Heart disease including Aneurysm
- Chronic lung disease
- Diabetes
- Chronic liver disease
- Chronic neurological condition
- Significant disability requiring frequent assistance with activities of daily life
- Severe mental health condition
- Chronic inflammatory condition (determined by past history item only and not whether they are receiving medical treatment)
- Primary or acquired immunodeficiency
- Severe obesity (determined by BMI greater than or equal to 40)
- Chronic renal failure (determined by last eGFR result is < 44 mL/min)
- Poorly controlled blood pressure (determined by having two or more prescriptions for blood pressure control in Current Rx)
Limitations
- Chronic inflammatory conditions are only determined by past history item and not whether they are receiving medical treatment
- Non-haematological cancers are checked if present in last 5 years, and not whether they are currently being treated or at advanced stage
- The following conditions were not implemented due to complexity or lack of available data:
- Solid organ transplant recipients who are on immune suppressive therapy
- Bone marrow transplant OR chimeric antigen receptor T-cell (CAR-T) therapy recipients OR those with graft versus host disease
- Household contacts of quarantine and border workers
- Cleaners, kitchen staff and other ancillary staff in healthcare settings
- Carers of eligible patients
Searching for age
By default, the query returns patients 50 years and older.
Depending on the vaccine that your practice has, and the latest advice on the AstraZenica vaccine from the Australian Government Department of Health, you may run several searches based on age, or change the age of patient returned by the query.
At the current time, the use of the Pfizer COVID-19 vaccine is preferred over AstraZeneca COVID-19 vaccine in adults aged under 50 years who have not already received the first dose of AstraZeneca vaccine.... COVID-19 Vaccine AstraZeneca can be used in adults aged under 50 years where the benefits are likely to outweigh the risks for that individual, and the person has made an informed decision based on an understanding of the risks and benefits.
If your practice has the Pfizer vaccine, you can delete the line in red text in the SQL query.
SELECT *
FROM BPS_Patients
WHERE StatusText = 'Active'
AND DOB < DateAdd(Year, -50, GetDate()) -- Patient is 50 years and older
AND
Searching for drugs
Not all drugs are mapped to a drug class. Drugs known to not be linked can be entered as free text search terms in the SQL query: add or remove items in the example below (red text). Any search term must be surrounded by single quotes. Separate multiple search terms with a comma.
SELECT ProductID
FROM BPSDrugs.dbo.Products p INNER JOIN BPSDrugs.dbo.ProductNames pm ON p.ProductNameID = pm.ProductNameID
WHERE pm.ProductName IN ('hydralazine', 'minoxidil','prazosin', 'methyldopa', 'clonidine', 'carvedilol', 'labetalol')
Patients who have been given the first vaccine and do not have an appointment booked for the second dose
Right-click here and select Save Target as or Save Link As to download the query file GivenFirstVaccineDoseAndnoAppointmentBookedforSecond.sql to a known location.
Returns
All active patients that match all of the following conditions:
- Has received first dose of Covid vaccination (Pfizer Comirnaty, Moderna Spikevax, Janssen-Cilag or COVID 19 Vaccine AstraZeneca) 1 day prior to the current date.
- Does not have a future appointment booked of the specified appointment types
Limitations
Appointment types can be different for each practice, so these will need to be customised. Towards the bottom of the query, the appointment types to search for are listed:
SELECT AppointmentCode
FROM AppointmentTypes
WHERE Description IN ('Immunisation', 'COVID') -- Enter appointment types here
The default query supplied is for both Immunisation and COVID appointment types. Replace the red text in the brackets with the specific appointment types to suit your practice. All appointment types must be surround by single quotes and multiple entries separated by commas.
Patients who have been vaccinated but haven't been billed the correct item
NOTE AstraZeneca, Novavax and boosters are now included in this query (updated 07 April 2022).
Right-click here and select Save Target as or Save Link As to download the query file VaccinatedButNotBilledCorrectItem.sql to a known location.
Returns
Returns all patients (except deleted) that match either of the following condition sets:
Condition 1
- Given dose 1 of the Covid vaccination at the practice prior to the current day, and
- Not bulk billed the first Covid suitability assessment.
First Covid assessment items include 93624, 93625, 93626, 93627, 93634, 93635, 93636, 93637.
Condition 2
- Given dose 2 of the Covid vaccination at the practice prior to the current day, and
- Not bulk billed the second Covid suitability assessment.
First Covid assessment items include 93644, 93645, 93646, 93647, 93653, 93654, 93655, 93656.
Limitations
The results from the database search will only display a list of patients and cannot advise which suitability assessment item should have been billed.
You can remove a condition set from the query so patients for the specified dose (1 or 2) are returned.
If you only want first dose patients that haven’t been billed, delete the text in blue below from the SQL query. If you only want dose 2 patients, delete the text in red, including the OR at the start.
OR ( -- Given dose 2
InternalID IN (SELECT InternalID
FROM Immunisations
WHERE RecordStatus = 1
AND NotGivenHere = 0
AND GivenDate < CAST(GETDATE() AS DATE)
AND Sequence = 2
AND VaccineID IN (170, -- Pfizer Comirnaty
172) -- COVID 19 Vaccine AstraZeneca
)
-- Not bulk billed 2nd suitability assessment
AND InternalID NOT IN (SELECT InternalID
FROM Invoices i INNER JOIN Services s ON i.InvoiceID = s.InvoiceID
WHERE i.RecordStatus = 1
AND s.RecordStatus = 1
AND s.PayerCode IN (2, 3)
AND s.MBSItem IN (93644, 93645, 93646, 93647, 93653, 93654, 93655, 93656)
Patients who have had both Covid vaccinations and booster but not billed any other items.
NOTE AstraZeneca, Novavax and boosters are now included in this query (updated 07 April 2022).
Patients who have had both Covid vaccinations and a booster shot but not billed any other items. This query can be used to mark these patients as inactive if required.
Right-click here and select Save Target as or Save Link As to download the query file BothCovidVaccinationsButNotBilledOtherItems.sql to a known location.
Returns
Returns all active patients that match all of the following conditions:
- Received 2 covid vaccinations
- No future appointments booked
- Only been billed COVID suitability assessment items for the last 2 years.
Limitations
Condition 3 currently only looks for past COVID suitability assessment items for the past 2 years, for patients that were previously inactive but made active again for the COVID vaccination.
The date range can either be removed or changed. To change the date, change the -2 highlighted red below. This is the number of years in the past to search for billings. For example, to change to 3 years in the past put -3. If the date condition is not required, the entire line can be removed.
-- Not billed any other items than the Covid suitability assessments
AND InternalID NOT IN (SELECT InternalID
FROM Invoices i INNER JOIN Services s ON i.InvoiceID = s.InvoiceID
WHERE i.RecordStatus = 1
AND s.RecordStatus = 1
AND s.MBSItem NOT IN (93624, 93625, 93626, 93627, 93634, 93635, 93636, 93637, 93644, 93645, 93646, 93647, 93653, 93654, 93655, 93656)
AND s.ServiceDate >= DATEADD(year, -2, GETDATE())
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
Patients who have only visited the practice for COVID-19 vaccination and have no future appointments
Returns a list of patients who have only visited the practice to receive their COVID-19 vaccination, and have no future appointments booked. This query can be used to mark these patients as inactive if required.
Right-click here and select Save Target as or Save Link As to download the query file OnlyVisitedForVaccineNoFutureAppointments.sql to a known location.
Patients who have had both vaccine doses and have a haematological cancer or are prescribed an alkylating agent or calcineurin inhibitor
Returns a list of patients who have had exactly two COVID vaccine doses and have a haematological cancer or are prescribed an alkylating agent or calcineurin inhibitor.
Right-click here and select Save Target as or Save Link As to download the query file HadBothVaccinesAndHaematologicalCancerOrAlkylatingAgentOrCalcineurinInhibitor.sql to a known location.
Patients who have had both vaccine doses and have a cancer recorded in their past history
Returns a list of patients who have had exactly two COVID vaccine doses and have a cancer recorded in their past history.
Right-click here and select Save Target as or Save Link As to download the query file HadBothVaccinesAndHasACancerRecord.sql to a known location.
Patients eligible for a booster (3rd vaccination)that have had both COVID-19 vaccinations and their last dose was over 6 months ago
Returns a list of patients eligible for the COVID-19 booster (3rd vaccination) that have had both COVID-19 vaccine doses, and their second dose was over 6 months ago.
Right-click here and select Save Target as or Save Link As to download the query file EligibleForBoosterLastDoseOver6MonthsAgo.sql to a known location.
Patients who have had at least 2 doses and the last dose was over 6 months ago
Returns a list of patients who have had any number of vaccine doses over 2 (for example, the patient may have had 4 vaccine doses), but their last dose was over 6 months ago.
Right-click here and select Save Target as or Save Link As to download the query file HadAtLeast2DosesLastDoseOver6MonthsAgoOngoing.sql to a known location.
Active patients 12 and older who have 2 or more visits within the last 18 months, who have NOT had any COVID vaccine administered
Returns a list of all active patients aged 12 and older who have 2 or more visits within the last 18 months. who have NOT had any COVID vaccine administered.
Right-click here and select Save Target as or Save Link As to download the query file Patients12AndOlderWith2AppointmentsIn18MonthsNoVaccine.sql to a known location.
Active Patients who have had 'sequence 2' COVID Vaccine 5 or more months ago
Returns a list of all active patients who have had 'sequence 2' COVID Vaccine 5 or more months ago.
Right-click here and select Save Target as or Save Link As to download the query file ActivePatientsHadSequence2Vaccine5OrMoreMonthsAgo.sql to a known location.
Patients who have had their 2nd COVID shot later than 4 months ago, but have not yet had their 3rd shot
Returns a list of all patients who have had their 2nd COVID shot later than 4 months ago, but have not yet had their 3rd shot.
Right-click here and select Save Target as or Save Link As to download the query file Had2ndCOVIDShot4MonthsAgoNo3rdShot.sql to a known location.
Active patients aged 5-11 who have NOT had an Astrazeneca, Moderna or Pfizer vaccine administered
Returns a list of all active patients aged 5-11 who have NOT had an Astrazeneca, Moderna or Pfizer vaccine administered.
Right-click here and select Save Target as or Save Link As to download the query file Patients5-11NoCOVIDVax.sql to a known location.
Patients who have had 2 or less visits, all with the visit reason: 'COVID-19 testing
Returns a list of all active patients who have had 2 or less visits, of which all had a visit reason of: 'COVID-19 testing'.
Right-click here and select Save Target as or Save Link As to download the query file 2OrLessVisitsCOVID-19Testing.sql to a known location.
Patients over 65, or over 50 and Aboriginal or Torres Strait Islander who had their 3rd shot over four months ago, but have not had a 4th shot
Returns a list of all patients over 65, or over 50 and Aboriginal or Torres Strait Islander who have had their 3rd COVID-19 shot over four months ago, but have not yet had a 4th shot.
Right-click here and select Save Target as or Save Link As to download the query file Over65OrOver50AndATSIHad3rdShotOverFourMonthsAgoNo4thShot.sql to a known location.
All patients who had their 3rd shot over four months ago, but have not had a 4th shot
Returns a list of all patients who had their 3rd COVID-19 shot over four months ago, but have not yet had a 4th shot.
Right-click here and select Save Target as or Save Link As to download the query file Had3rdShotOverFourMonthsAgoNo4thShot.sql to a known location.
Patients who have had a COVID booster but not billed incentive item
Returns all active patients that match all of the following conditions:
- received Booster
- patient has not been billed an incentive item.
Limitations
The incentive item number will be different for each practice, this item will need to be edited prior to running the script. The incentive number can be edited towards the bottom of the query highlighted in red:
SELECT *
FROM BPS_Patients p
WHERE
( -- Given booster dose.
(
SELECT COUNT(InternalID)
FROM Immunisations
WHERE RecordStatus = 1
AND InternalID = p.InternalID
AND GivenDate < CAST(GETDATE() AS DATE)
AND VaccineID IN
(
170, -- Pfizer Comirnaty
172, -- COVID 19 Vaccine AstraZeneca
174, -- Moderna Spikevax
175, -- Janssen-Cilag
176, -- AstraZeneca COVISHIELD
181 -- Novavax NUVAXOVID
)
) >= 3
AND InternalID NOT IN
( -- Not billed Incentive item number
SELECT InternalID
FROM Invoices i
INNER JOIN Services s ON i.InvoiceID = s.InvoiceID
WHERE i.RecordStatus = 1
AND s.RecordStatus = 1
AND s.MBSItem = 00000 -- Enter incentive item number here
GROUP BY InternalID
)
)
ORDER BY Surname, Firstname;
Right-click here and select Save Target as or Save Link As to download the query file BoosterGivenWithoutBillingIncentiveItem.sql to a known location.
Last updated 28 July 2022
AU 