My Health Statistics Queries

The following SQL queries have been developed by Best Practice Software Support to assist retrieving information related to My Health in Bp Premier. Instructions on running a query and saving a query are also supplied.

For more information on managing database queries, see Searching the Database.

All active patients who were registered for a My Health record between the dates specified

SELECT *

FROM BPS_Patients

WHERE StatusText = 'Active'

AND InternalID IN (SELECT INTERNALID

FROM PCEHRAccess

WHERE SERVICE = 12 -- Registered for MyHealthRecord

AND RESULT = 1 -- Successful registration

AND cast(ACCESSDATE as date) BETWEEN '20160101' AND '20160201') -- change dates here

ORDER BY surname, firstname

All active patients with a IHI entered

SELECT *

FROM BPS_Patients

WHERE StatusText = 'Active'

AND InternalID IN (SELECT INTERNALID

FROM PATIENTS

WHERE LEN(IHI) > 0)

ORDER BY surname, firstname

All active patients with a shared health summary uploaded within the date range (shows patient multiple times if they have more than one upload)

SELECT *

FROM BPS_Patients p

INNER JOIN pcehrdocuments d on d.internalid = p.internalid

WHERE statustext = 'active'

AND d.recordstatus = 1 and documenttype = 1

AND d.documentdate between '20170101' and '20170721' -- change upload dates here

ORDER BY surname, firstname

All active patients with a shared health summary uploaded within the date range

SELECT *

FROM bps_patients p

WHERE statustext = 'active'

AND internalid IN (SELECT InternalID

FROM pcehrdocuments

WHERE recordstatus = 1

AND documenttype = 1

AND documentdate between '20170101' and '20170721') -- change upload dates here

ORDER BY surname, firstname

All active patients with an IHI that has been recorded as active and verified

SELECT *

FROM BPS_Patients

WHERE StatusText = 'Active'

AND InternalID IN (SELECT INTERNALID

FROM PATIENTS

WHERE LEN(IHI) > 0

AND IHISTATUS = 1

AND IHIRECORDSTATUS = 1)

ORDER BY surname, firstname

All active patients with no IHI entered

SELECT *

FROM BPS_Patients

WHERE StatusText = 'Active'

AND InternalID IN (SELECT INTERNALID

FROM PATIENTS

WHERE IHI = ''

OR IHI IS NULL)

ORDER BY surname, firstname

To run a query

  1. Copy the SQL query text you want from inside the shared areas above. Include all the text from 'Select * ...' to '...surname, firstname'. Use Ctrl+C to copy.
  2. From the main screen of Bp Premier, select Utilities > Search.

  3. Select all of the default query text in the SQL Query text area and press Delete on your keyboard.

  4. The SQL Query window will be cleared.

  5. Paste the text you copied above into the SQL Query text area. Use Ctrl+V to paste.

  6. Click Run Query. Patients from the Bp Premier database who match the query will be displayed in the bottom half of the screen.

To save a query

  1. From the Database Search screen (Utilities > Search), click Save query.
  2. Choose the folder to save the script to and give the file a meaningful name.
  3. Select ‘SQL Query’ from the Save as type drop down list.

  4. Click Save.

To save results to a spreadsheet file

After you have run a query and obtained results, you can save the results to a spreadsheet file for use in another application.

  1. Run your query so the bottom half of the screen is populated with results.
  2. From the Database Search screen, select File > Save.
  3. Choose the folder to save the results to and give the file a meaningful name.
  4. Select ‘CSV File’ from the Save as type drop down list.

  5. Click Save.

If you have any problems running, saving, or exporting your custom SQL query, please contact the Best Practice Software Support team on 1300 401 111 (select option 1 and 1).