EJF EJF - 5 months ago 6
SQL Question

Pulling results from another result set

I'm working on a query where I need to pull a list of all patients from a certain clinic who smoke or use some sort of tobacco. I then need to pull a list that shows any of those patients who have had tobacco cessation counseling within a given time frame.

I think I'm doing this right, but I'm not sure. The front-end system is such that we often end up with inaccurate data (don't even get me started). When I run my query the way it is, I get a number that I'm pretty sure is way too low, but I'm not sure if it's just something with the data or if there's an issue with my query. Here's what I've got.

First of all, this is my query to find all the tobacco users. The "status" column indicates the type of use -- you'll see here that I'm filtering out 3, 4, and 6, which stand for "former user," "never used," and "unknown" (I'm just looking at people who are for sure currently using tobacco - these entries get updated, if applicable, every time the patient visits).

SELECT DISTINCT sh.PatientID, sh.Description, sh.Category, sh.Status, pd.Physician, vi.VisitDate

FROM SocialHistory sh JOIN PatientDemographic pd ON sh.PatientID = pd.PatientID
JOIN VisitInfo vi ON vi.PatientID = pd.PatientID

WHERE sh.Description LIKE '%tobacco%'
AND sh.Status != 3
AND sh.Status != 4
AND sh.Status != 6


That query gives me a little over 3000 results, which seems about right considering the total patient population at the clinic.

Now I need to pull everyone from that result set who has had cessation counseling (which is entered as a procedure code - I added the field to the select list) between January 1, 2016 and June 30, 2016. Here's what I have:

SELECT DISTINCT sh.PatientID, vi.ProcedureCode, pd.Physician, vi.VisitDate

FROM
(SELECT DISTINCT sh.PatientID, sh.Description, sh.Category, sh.Status,
pd.Physician, vi.VisitDate, vi.ProcedureCode

FROM SocialHistory sh JOIN PatientDemographic pd ON sh.PatientID = pd.PatientID
JOIN VisitInfo vi ON vi.PatientID = pd.PatientID

WHERE sh.Description LIKE '%tobacco%'
AND sh.Status != 3
AND sh.Status != 4
AND sh.Status != 6

) VisitInfo

WHERE vi.ProcedureCode IN ('counseling1','counseling2','counseling3')

AND VisitDate BETWEEN '01/01/2016' AND '06/30/2016'


I'm only getting about 190 results with this, which seems really low given the 3000+ tobacco users. But it could be accurate. I just wanted to make sure my query was done right. Is that nested
SELECT
statement structured correctly?

Answer

Here is another way to write your query that is a little more readable and removes need of sub select.

SELECT DISTINCT
   sh.PatientID
   ,vi.ProcedureCode
   ,pd.Physician
   ,vi.VisitDate
 FROM
   SocialHistory sh
   INNER JOIN PatientDemographic pd
   ON sh.PatientID = pd.PatientID 
   INNER JOIN VisitInfo vi
   ON vi.PatientID = pd.PatientID
   AND vi.ProcedureCode IN ('counseling1','counseling2','counseling3')
   AND VisitDate BETWEEN '01/01/2016' AND '06/30/2016'
 WHERE
   sh.Description LIKE '%tobacco%'
   AND sh.Status NOT IN (3,4,6)

The 6 months is more likely why you have a lower number of records, expand that time frame or comment that line altogether to test what your result set would be and see if it is in line with what you believe the results should be. If so then you know it is the 6 months.