Home   COVID-19   FAQ   How-To Videos   Media Resources   Docs   Troubleshoot

Supplied Database Queries

This article lists the supplied database queries that are available to use with Bp Premier.

You can access supplied database queries from C:\Program Files\Best Practice Software\BPS\SuppliedQueries.

To use the supplied queries:

  1. Select Utilities > Search from the Main menu to view the Database Search screen.
  2. Click Load query. A file explorer will open.
  3. Browse to the SQL query file you want to load and click Open. The query will be loaded into the SQL query box.
  4. Click Run Query.

A number of extra COVID-19 related database queries are also available. See COVID-19 Database Queries to access these queries.

Supplied queries are divided into four categories:

Clinical queries

  • ATSI Patients over 55 seen in last 2 years, not had Fluvax in the last year, not opted out and not allergic
  • ATSI Patients over 55 seen in last year, not had Fluvax, not opted out and not allergic
  • Covid19 at risk patients
  • Deceased patients with active reminders
  • Female Patients over 60 who are current or past smokers that have been on Corticosteroids
  • Female patients who've had a pregnancy recorded as terminated in specified year
  • Female patients with 3 gardasil immunisations recorded
  • Female Patients with a cervical smear investigation received and a smear result added to the clinical record on the same day
  • Female Patients with a cervical smear investigation received and a smear result added to the clinical record within 2 days
  • Female Patients with a cervical smear investigation received and a smear result not added to the clinical record within 2 days
  • Female Patients with a cervical smear investigation received
  • Female patients with pregnancies commenced in specified year
  • Female patients with pregnancies recorded in specified year
  • Females Aged 45 to 69 requiring BreastScreening
  • Fluvax given but recalls forgotten
  • Over 65 year olds who have a flu vaccine prompt displayed
  • Patients 40 - 49, with 3 visits in 2 years, no waist measurement
  • Patients 40 - 49, with 3 visits in 2 years, waist above specified threshhold
  • Patients 65+ year olds with Care plan preventative health item not excluded
  • Patients 65+ year olds with Diabetes review preventative health item not excluded
  • Patients 65+ year olds with Fluvax preventative health item not excluded
  • Patients 65+ year olds with Health assessment preventative health item not excluded
  • Patients 65+ year olds with Medication review preventative health item not excluded
  • Patients 65+ year olds with PNEUMOVAX preventative health item not excluded
  • Patients 75 or over who have never had a Health Assessments
  • Patients aged below 16 with Immunisations in last year
  • Patients aged between 40 and 50 with an appointment today and haven't had a Diabetes Risk Assessment performed
  • Patients over 18 and under 65 with asthma and diabetes who attended last year
  • Patients over 55 with Ischaemic Heart Disease and on Nitrate or Ramipril and on a statin, but not on ezetimibe
  • Patients over 65 seen in last 2 years, not had Fluvax in the last year, not opted out and not allergic
  • Patients over 65 seen in last year, not had Fluvax in the last year, not opted out and not allergic
  • Patients over 65 with flu immunisation in past years but not this year
  • Patients over 75 who haven't had an EPC health assessment in last 12 months
  • Patients who are getting the Diabetes Risk Assessment prompt
  • Patients who are to be excluded from Pap Smears
  • Patients who have a past history of diabetes in the last 12 months
  • Patients who have had certain path tests
  • Patients who have had Gardasil Sequence 1 only
  • Patients who have had Gardasil Sequence 2 only
  • Patients who have had Gardasil Sequence 3 only
  • Patients who have overdue reminders and no visits in over 2 years and no future appointments
  • Patients who have overdue reminders
  • Patients who have unsent overdue reminders with no visits in over 2 years and no future appointments
  • Patients who haven't had a specific observation recorded on the same day as their last appointment
  • Patients with a Best Practice health assessment performed between given dates
  • Patients with a diagnosis of hypertension with blood pressure recorded more than once last calendar year and with high readings each time
  • Patients with a diagnosis of hypertension with blood pressure recorded more than once this calendar year and with high readings each time
  • Patients with a HBA1c greater than or equal 7 recorded in the last 24 months
  • Patients with a specific free texted past history item
  • Patients with an active medication review done within a year
  • Patients with an inactive diabetes past history item
  • Patients with an investigation with 'letter' in the title
  • Patients with correspondence in in the past year with subject of 'house call'
  • Patients with diabetes and a reminder for care plan due 3 months ago to 1 month ahead
  • Patients with diabetes and BMI over 30
  • Patients with diabetes that have not had a Diabetes Cycle of Care in the last 12 months
  • Patients with Diabetes Type 2 recorded in the last calendar year
  • Patients with Diabetes with no HBA1C recorded
  • Patients with Flu vaccine preventative health item
  • Patients with Gardasil marked in notes
  • Patients with HBA1c Greater Than Or Equal 7 In the Last 12 Months
  • Patients with investigations marked for 'Send Routine Reminder', for specific user
  • Patients with no allergies or nil known recorded
  • Patients With No Past History
  • Patients With No Smoking History Recorded
  • Patients with outstanding pathology requests
  • Patients with past history entered by specific doctor
  • Patients with specified free-typed past history
  • Patients wth 5 or more CX records
  • Pregnant patients due in the next month

Demographic queries

  • Patients added in the last month
  • Patients added in the last week
  • Patients first added to an address where anyone at that address has had a visit in the last 3 years
  • Patients registered for CTG
  • Patients who have 'Other notes' matching specific term
  • Patients who turn 4 in 3 months
  • Patients who turned 4 last month
  • Patients with a birthday in specified month
  • Patients with a Commonwealth Seniors Health Card
  • Patients with a DVA gold card
  • Patients with a Health Care Card
  • Patients with a Pensioner concession Card
  • Patients with a referral to a specific dr from a specific contact
  • Patients with DOB in December
  • Patients with no ethnicity recorded
  • Patients with 'Nursing' or 'Hostel' in their address
  • Patients with Pension numbers but no Pension Card type recorded
  • Patients with referrals entered for specific doctor
  • Patients with referrals from doctors with specific provider number
  • Patients with referrals from doctors without specific provider number
  • Patients without a usual doctor set
  • Patients without email recorded
  • Patients without record number

Management queries

  • 4yo Patients with an appointment on a given day who haven't been billed a 701, 703, 705, 707 or 10986 in the past year
  • ATSI Patients who've been billed a 715 in the past year and have been billed more than 10 10987's
  • Patients 45 - 49 with an appointment on a given day who haven't been billed a 701, 703, 705 or 707in the past year
  • Patients 75+ not at a specific nursing home (specified) no 702 or 707 raised in a year with more than 2 visits in the last year
  • Patients invoiced for Care Plans between specified dates
  • Patients marked as 'Aboriginal' previously charged a 703,721,723 in a specific month
  • Patients marked as 'Aborignal Torres Strait Islander' previously charged a 703,721,723 in a specific month
  • Patients marked as 'Torres Strait Islander' previously charged a 703,721,723 in a specific month
  • Patients over 75 that've been billed a 701, 703, 705 or 707 and seen by specific dr in the last 6 month
  • Patients over 75 with an appointment on a given day who haven't been billed a 701, 703, 705 or 707in the past year
  • Patients who have been invoiced for item 721 or 723
  • Patients who have multiple 732s waiting to be sent to medicare
  • Patients who were billed the custom item between date range
  • Patients who've been billed a 731 over 3 months ago and haven't been billed a 731 in the last 3 months
  • Patients with a chronic disease and no care plan item billed
  • Patients with a health assessment billed between given dates
  • Patients with a health assessment performed between given dates but not billed between given dates
  • Patients with a specific a custom fee schedule set as their usual account
  • Patients with a usual account of Direct Bill
  • Patients with an appointment in the next 14 days of specific type
  • Patients with an appointment on a given day who have been billed a 721 more than 3 months ago and haven't been billed a 732 in the past 3 months
  • Patients with an appointment tomorrow, with results in the last 3 months not marked as given
  • Patients with 'Appointment Notes' that contain specific term
  • Patients with non-funded flu vaxes not billed for a specific custom item in the current year
  • Patients with reminders due in the next 2 months, who don't have an appointment in the next 2 months

Visit queries

  • Antenatal visits recorded in the last 12 months
  • Patients over 50, with 4 or more visits in the last 12 months
  • Patients who have a surname between N-R who have visited within the past 18 months
  • Patients who have a surname between Q-Z who have visited within the past 18 months
  • Patients with a Home Visit in the last month
  • Patients with a Hospital Visit in the last month
  • Patients with a Hostel Visit in the last month
  • Patients with a Locum Consultation in the last month
  • Patients with a Non-Visit in the last month
  • Patients with a Nursing Home Visit in the last month
  • Patients with a specific visit type between given dates
  • Patients with a Surgery Consultation in the last month
  • Patients with a Telephone Consultation in the last month
  • Patients with an Email Consultation in the last month
  • Patients with an Other Consultation in the last month
  • Patients with an Out Of Office Consultation in the last month
  • Patients with Home visits during range of dates
  • Patients with only one recorded visit
  • Patients without a usual doctor set and less than 3 visits

Last updated 30 March 2022

Forums

Webinars

Videos

Training

Subscribe