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:
- Select Utilities > Search from the Main menu to view the Database Search screen.
- Click Load query. A file explorer will open.
- Browse to the SQL query file you want to load and click Open. The query will be loaded into the SQL query box.
- 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
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
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
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
AU 