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.

This is the default installation folder for supplied queries, if your installation path for Bp is different, follow the path after Best Practice Software.

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.

Supplied queries are divided into four categories:

Clinical queries

Clinical queries generally return patients based on data in the clinical record such as prescriptions, conditions, or immunisations. These can be altered and saved as new queries to match specific circumstances for your Practice. Some examples of clinical queries are:

  • Patients with diabetes type 2 recorded in last calendar year
  • Patients that have been prescribed with a certain medication
  • Females Aged 45 to 69 that have had Fluvax in the last year

Demographic queries

Demographic queries search for patients using information in the demographic record, such as name, age, and location. Some examples are:

  • Patients added in the last month
  • Patients with a referral to a specific dr from a specific contact
  • Patients with a Health Care Card

Management queries

Management queries contain queries such as appointment management, fee management and billing management. Some examples are as follows:

  • Patients with an appointment in the next 14 days of a specific appointment type
  • Patients who have multiple 732s waiting to be send to Medicare
  • Patients with a specific custom fee schedule set

Visit queries

Visit queries search on visit type information, such as hospital, hostel, and antenatal visits.

  • Patients with a Hospital visit in the last month
  • Patients with a home visit in the last month.

Create and Save Custom Database Search Queries

The following video demonstrates how to create and save a custom query.

Limited time or capacity? Prefer to have someone build a custom report, query, or template for you? Learn more about our SQL and Custom Report Building service here.

Database search examples

EXAMPLE   

Patients who have past history of diabetes but not Gestational diabetes and their Systolic BP in the past year has been over 170

SELECT *

FROM BPS_Patients

WHERE StatusText = 'Active'

AND InternalID IN (SELECT InternalID FROM PastHistory

WHERE ItemCode IN (3, 775, 776, 778, 774) AND RecordStatus = 1)

AND NOT InternalID IN (SELECT InternalID FROM PastHistory WHERE ItemCode = 780 AND RecordStatus = 1)

AND InternalID IN (SELECT InternalID FROM Observations WHERE DataName = 'Systolic' AND ObsDate > '01/01/2007' AND DataValue >= 170 AND RecordStatus = 1)

ORDER BY surname, firstname

EXAMPLE   

Patients who have had 45 to 49 year old health assessments performed in the last year who was seen by a specific doctor

SELECT *

FROM BPS_Patients

WHERE StatusText = 'Active'

AND DOB < DateAdd(Year, -45, GetDate())

AND DOB > DateAdd(Year, -50, GetDate())

AND InternalID IN (SELECT InternalID FROM Visits WHERE VisitDate >= DateAdd(Year, -1, GetDate()) AND VisitDate <= GetDate()AND RecordStatus = 1 AND

UserID in (SELECT UserID FROM Users WHERE Surname ='Findacure'))

ORDER BY surname, firstname

EXAMPLE   

Patients who smoke more than 10 cigarettes per day and have not received a Fluvax immunisation

SELECT *

FROM BPS_Patients

WHERE StatusText = 'Active'

AND InternalID IN (SELECT InternalID FROM Tobacco WHERE SmokingCode = 3 AND SmokesPerDay > 10)

AND InternalID NOT IN (SELECT InternalID FROM Immunisations

WHERE GivenDate >= '01/01/2005' AND VaccineName = 'Fluvax')

ORDER BY surname, firstname

Related topics

Last updated 24 May 2023.