Daniël Cronk Daniël Cronk - 2 months ago 9
SQL Question

Nested Query to Show Results where > 1

I've written a query to show me a Patients MRN, Demographics, Procedure Description and Complete Date WHERE certain criteria exist.

What I'd like to figure out is how I would write it so that it only shows me the results where there are two or more

SELECT pif.MRN, o.ReasonForStudy, o.ProcedureDescList, o.CompleteDate
FROM Report r
INNER JOIN [order] o
ON r.reportID = o.reportID
INNER JOIN Visit v
ON o.VisitID = v.VisitID
INNER JOIN PatientInfo pif
ON v.PatientID = pif.PatientID
WHERE PlacerFld2 = 'CT'
AND r.ContentText Like '%mass%'
Order by MRN ASC


What I'd like to figure out how to do is show me only the results where there are TWO or more CT scans (PlacerFld2) on the same patient MRN.

example, the above query would bring back the following results

123456 CANINE:HUSKY CT THORAX 2015-01-01
123456 CANINE:HUSKY CT THORAX 2015-05-01
123456 CANINE:HUSKY CT THORAX 2015-07-01
123123 FELINE: DSH CT SKULL 2015-01-01
321654 FELINE: DSH CT THORAX 2015-01-01
987654 FELINE: DSH CT NECK 2015-01-01
987654 FELINE: DSH CT ABDOMEN 2015-03-01
888888 FELINE: DMH CT ABDOMEN 2015-03-01
777777 FELINE: DSH CT ABDOMEN 2015-03-01
666666 FELINE: DLH CT ABDOMEN 2015-03-01


What I would like is to show only those results where there are two or more of the same MRN (column 1).

resulting in:

123456 CANINE:HUSKY CT THORAX 2015-01-01
123456 CANINE:HUSKY CT THORAX 2015-05-01
123456 CANINE:HUSKY CT THORAX 2015-07-01
987654 FELINE: DSH CT NECK 2015-01-01
987654 FELINE: DSH CT ABDOMEN 2015-03-01

Answer

Use your query as a temp table and draw from that to select only records with > 1 MRN. Find these records with HAVING on your temp table:

with c as (
  SELECT pif.MRN, o.ReasonForStudy, o.ProcedureDescList, o.CompleteDate
  FROM Report r
  INNER JOIN [order] o
    ON r.reportID = o.reportID 
  INNER JOIN Visit v
    ON o.VisitID = v.VisitID 
  INNER JOIN PatientInfo pif
    ON v.PatientID = pif.PatientID 
  WHERE PlacerFld2 = 'CT'
  AND r.ContentText Like '%mass%'
  Order by MRN ASC
)
select * from c where c.MRN in (select MRN from c group by MRN having count(*) >1)

Here is a simple example that you can play around with to see how the query is working