Searching the database

Bp Premier offers a comprehensive database search tool for retrieving records from the database based on patient record or system criteria.

What can I use a database search for?

A common use of the search tool is to produce a list of patients for a mail merge. For example, your practice is introducing a new diabetes management plan, and you need to set up a mailout to all of your active patients with this condition. See Print labels and mail merge from a database search for more information.

If you are only interested in searching for patients by name or basic demographics, the Patient List window might be easier to use. Press F10 from the main screen.

Searches are created in Structured Query Language (SQL), but you do not need to understand SQL to use the search tool. You can select from a set of standard search filters for the records you are looking for, and Bp Premier will create the SQL query for you.

Important: The database search is a high-level function that grants generous access to Bp Premier data. If the option to search the database is not available under the Utilities menu, your Bp Premier administrator may be able to provide access.

You can also save queries that you run often, and import queries from other Bp Premier users.

Two common searches are searching for patients with diabetes and searching for patients who are or are not immunised. These searches provide examples of creating complex queries using the logic operators NOT, AND, and OR.

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

What would you like to do?