PC Lab: Complex SQL Queries and Reports

Exercise 1

Using the database search report builder, create a query that finds patients that match the criteria below:

  • Active patients
  • Currently a smoker
  • Do not have a Past History of COPD recorded.

Expected results: 3 patients.

Exercise 2

Supplied query: patients with a GPMP/TCA plan eligible for a GPCCM plan.

Update the query to include the following:

  • Patient is not registered for MyMedicare.

Expected results: 1 patient.

Supplied query SQL:

SELECT *
FROM BPS_Patients
WHERE StatusText = 'Active'
AND InternalID IN (										
	SELECT i.InternalID
	FROM INVOICES i
       INNER JOIN SERVICES s 
       ON i.INVOICEID = s.INVOICEID
       WHERE s.MBSITEM IN (721, 723, 732, 92024, 92025, 92028, 229, 230, 233, 92055, 92056, 92059)
       AND s.SERVICEDATE >= DATEADD(MONTH, -3, GETDATE())
       AND i.RECORDSTATUS = 1
)
ORDER BY surname, firstname;

The column is in the Patients table. Use Bp Browser to verify the column name and value to filter for.

Column name: MyMedicare

Value: 0

SELECT *
FROM BPS_Patients
WHERE StatusText = 'Active'
AND InternalID IN (
	SELECT i.InternalID
	FROM INVOICES i
	INNER JOIN SERVICES s
	ON i.INVOICEID = s.INVOICEID
	WHERE s.MBSITEM IN (721, 723, 732, 92024, 92025, 92028, 229, 230, 233, 92055, 92056, 92059)
	AND s.SERVICEDATE >= DATEADD(MONTH, -3, GETDATE())
	AND i.RECORDSTATUS = 1
)
AND InternalID IN (SELECT InternalID FROM Patients WHERE MyMedicare = 0)
ORDER BY surname, firstname;

Exercise 3

Supplied query: Patients that are registered for MyMedicare.

Update the provided query to include the following:

  • Does not have a MyMedicare location set, or
  • Does not have a MyMedicare registration date set.

Expected results: 18 patients.

Supplied query SQL:

SELECT *
FROM BPS_Patients
WHERE StatusText = 'Active'
AND InternalID IN (SELECT InternalID FROM Patients WHERE MyMedicare = 1)
ORDER BY surname, firstname;

Column names are MyMedicareRegisteredLocation and MyMedicareRegistrationDate

When the date is not set the column will be NULL.

SELECT *
FROM BPS_Patients
WHERE StatusText = 'Active'
AND InternalID IN 	(
				SELECT 	InternalID 
				FROM 	Patients 
				WHERE 	MyMedicare = 1 
				AND 
				   (
				    ISNULL(MyMedicareRegisteredLocation, 0) = 0 
				    OR MyMedicareRegistrationDate IS NULL
				    )
			)
ORDER BY surname, firstname;

Exercise 4

Supplied query: Patients with 5 or more Current Rx records.

Update the columns being displayed to match the following example:

InternalID FirstName Surname DrugName
6 Alfred Charles Aldridge Accupril 10mg Tablet
6 Alfred Charles Aldridge Dilatrend 3. 125mg Tablet
32 Gwenda Alfreds Cipramil 20mg Tablet

Supplied query SQL:

SELECT *
FROM Patients
WHERE RecordStatus = 1
AND PatientStatus = 1
AND InternalId IN (SELECT InternalID FROM CurrentRx WHERE RecordStatus = 1 GROUP BY internalid HAVING COUNT(RecordID) >= 5)
ORDER BY surname, firstname

Perform an INNER JOIN with the CurrentRx table

SELECT p.InternalID, FirstName, Surname, DrugName
FROM Patients p INNER JOIN CurrentRx rx ON p.InternalID = rx.InternalID
WHERE p.RecordStatus = 1
AND PatientStatus = 1
AND rx.RecordStatus = 1
AND p.InternalId IN (SELECT InternalID FROM CurrentRx WHERE RecordStatus = 1 GROUP BY internalid HAVING COUNT(RecordID) >= 5)
ORDER BY surname, firstname

Additional Resources

Tools to assist with writing SQL

When writing custom SQL or modifying a query you can either do this directly in the Bp Premier, or use an external tool and then copy paste the SQL across. Using an external tool can assist with improving readability by highlighting system words as well making it easier to format.

Notepad++ is installed on the demo laptops and can be used if required. When using go to Language > S > SQL to improve readability.

SQL Validator can be helpful if you need assistance with validating the syntax of your custom SQL It can also help optimise the query as well as help with readability and formatting when writing custom SQL. Set the ‘Selected DB’ drop down to TSQL.

How to guides

Supplied queries

External Resources