The Northern NSW Local Health District has partnered with the North Coast Primary Health Network to reduce the burden of winter flus and secondary infections on the community and hospital system. 

Part of the Winter Strategy involves identifying patients at high risk of admission and providing patient education about their diseases and how to manage them effectively, and to seek assistance from their usual GP and surgery at the first signs of deterioration. 

Practices have been asked to identify and register these patients so the hospital system can also assist in their timely, co-ordinated care. 

GPs may choose to concentrate on their respiratory patients in the first instance but it is acknowledged that these patients also have multiple morbidities and are on multiple medications. 

The following query runs in Best Practice under Utilities | Search by pasting into the SQL query box. It finds current patients who have more than 8 active, "summary" conditions one of which is chronic lung disease of one type or another. The patients also are on 8 or more medications. The results are grouped by "usual doctor".

Excluding some diagnoses and reducing the number of active conditions or medications is easily performed by altering the highlighted variables. 

The query should take less than a minute to run.  


SELECT *
FROM BPS_Patients
WHERE StatusText = 'Active'
and internalid in (select internalid
from currentrx
where recordstatus = 1
and rxstatus in (1,2)
group by internalid
having count(internalid) >= 8)
AND InternalID IN (SELECT InternalID
FROM PastHistory
WHERE ItemCode IN (414, 419, 598, 599, 912, 928, 929, 3063, 4870, 4871, 6848, 8045, 8206)
AND RecordStatus = 1)
AND InternalID IN (SELECT InternalID
FROM PastHistory
WHERE RecordStatus = 1
and StatusCode = 1
and summarycode = 1
group by internalid
having count(internalid) >= 8)
ORDER BY usualdoctor, surname, firstname


For 5 disease and 5 drugs

SELECT *
FROM BPS_Patients
WHERE StatusText = 'Active'
and internalid in (select internalid
from currentrx
where recordstatus = 1
and rxstatus in (1,2)
group by internalid
having count(internalid) >= 5)
AND InternalID IN (SELECT InternalID
FROM PastHistory
WHERE ItemCode IN (414, 419, 598, 599, 912, 928, 929, 3063, 4870, 4871, 6848, 8045, 8206)
AND RecordStatus = 1)
AND InternalID IN (SELECT InternalID
FROM PastHistory
WHERE RecordStatus = 1
and StatusCode = 1
and summarycode = 1
group by internalid
having count(internalid) >= 5)
ORDER BY usualdoctor, surname, firstname