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

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 Send health awareness communications 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. 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.

Where do I find the queries supplied with Bp Premier?

A number of useful queries are provided with a Bp Premier installation. These can be found in the folder C:\Program Files\Best Practice Software\BPS\SuppliedQueries.

  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 useful query for your practice to run is 'Active patients who have not had a visit in the last 12 months and have no future appointments and not created in the last 6 months'. This query can assist your practice in deactivating patients who have not visited your practice in some time, and can be marked as inactive. See Mark patients as inactive or deceased for more information.

See Supplied Database Queries for more information.

Can I run an email merge or phone list from a database search?

You can only mail merge to letter, SMS, or Best Health App from a database search. However, you could save the results to a .csv file and use a spreadsheet application to extract data from a column. See Export query results to file for more information.

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.

Run a database search query

NOTE  Patients who have Deny access to other users set in the patient demographic are excluded from appearing in search utility results. See Understanding confidentiality and patient notes for more information.

  1. Select Utilities > Search from the main menu to view the Database Search screen.

    2. Database Search

    The filter buttons along the top will open a filter screen that allows you to drill down on the data you are looking for. For example, click Demographics to search based on patient demographic data, or click Visits to search based on patient visit data.

  2. If you are proficient at SQL and know the Bp Premier database structure, you can type your custom query directly into the SQL Query text field.
  3. Otherwise, click the button that matches the area you want to search by. A Search for ... screen will be displayed that depends on the selected button.
  4. Complete the criteria you want to search for in the top half of the Search screen. The fields available will depend on the selected button. For example, if you clicked Drugs, you would select the drug prescription you are searching for, and which doctor prescribed. If you clicked Observations, you would select the clinical observation and the range of values the observation should fall between.
  5. Click Add to add the search condition to the Condition box at the bottom of the screen.
  6. Repeat to add as many conditions are you need.
  7. Click OK from the Search screen to return to the Database search screen. The SQL Query has been updated to reflect your search criteria.
  8. If you need to include inactive patients in your search results, tick the checkbox.
  9. If you need to include deceased patients in your search results, tick the checkbox.
  10. Click Run query to execute the query and list the patients who meet that condition.
  11. If the query ran successfully, and you think you may need to rerun the query in the future, click Save query to save the query to disk with a meaningful name.

It may take a few attempts to create a query that returns the results you want. You can use the samples database installed with Bp Premier to practice running database requests without putting load on the live server.

Using AND and NOT

Use the logic operators in each filter screen to create complex searches. For example, the following construct for the Rx filter searches for patients prescribed Zoloft by both Dr I. Cure and Dr F. Findacure, and have not been prescribed Lipitor by Dr I. Cure.

EXAMPLE

This example searches for all patients who are 55 or over years old with a body mass index over 25.

  1. From the Database search screen, click Demographics to display the Search for patients screen.
  2. Select 'Age' from the Column name field.
  3. Select '>=' from the middle column.
  4. Type '55' in the field marked with the red asterisk.
  5. Click Add, and click OK.
  6. Click Observations to open the Search by observation screen.
  7. Select 'BMI' in the Observation field.
  8. Select '>' from the middle column.
  9. Type '25' in the text field on the right.
  10. Click Add, and click OK.
  11. Click Run Query.
  12. Select File > Save to save the list of patients who match the query results.

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

Last updated 04 April 2022

Forums

Webinars

Videos

Training

Subscribe