Use NOT IN instead of CTE or temp table

Question

select distinct patientID
from [dbo]..HPrecords 
where ptprocess = 'refill'
and pTDescription <> 'Success'
and patientID is not null
and patiententrytime > '2021-04-06'
and patientID not in (
    select distinct patientID
    from [dbo]..HPrecords 
    where ptprocess = 'embossing'
    and ptDescription = 'Success'
    and patientID is not null
    and patiententrytime > '2021-04-06'
)

So I want to use a NOT IN feature in SQL to filter out the patients that haven't received their refill medication yet. A patient can be refilled multiple times, the first time can fail, but the second or third time it can be successful. So there can be multiple rows.

So I just want to write a query that will filter out and get me the patientID that DID NOT SUCCEED in getting refill at all no matter how many times.

Is this the best way to write it, my current query is still running, I think the logic is wrong?

I want to try to write this query without CTE or temp table just as an exercise.

Sample output:

PatientID
151761
151759
151757
151764

1) Please don't splatter `nolock` around unless you are very very sure you need it and know the implications. 2) Why would you restrict a possible solution to not use a CTE or temp table? 3) Provide a [mre] i.e. sample date + expected results. — Dale K, Apr 09 '21 at 23:42
Where is the sample data that would produce your sample output? — Dale K, Apr 09 '21 at 23:51
`Is this the best way to write it` For `NOT IN` there is nothing else to change — Squirrel, Apr 09 '21 at 23:56
Explain how PatientID in your table could possibly be NULL. That seems highly unlikely but your table name provides no clues as to what entity it actually represents. I also suggest you think very carefully about how you write your goal and the associated sql. A patient is not refilled - a prescription is. Some prescriptions are not refillable, some have limited refills. — SMor, Apr 10 '21 at 01:37

Wiaan van Aswegen · Accepted Answer · 2021-04-10T10:05:17.967

I personally prefer joins above not-in. Looks neater, reads better and allows one to access information on both tables if you need to analyse anomalies. A colleague and I once did some very basic performance comparisons and there was no notable difference.

Here's my take on it..

select  distinct hpr.patientID
from    [dbo].HPrecords hpr
        LEFT OUTER JOIN
            [dbo].HPrecords hpr_val ON
                hpr.patientID = hpr_val.patientID
                AND hpr_val.ptprocess = 'embossing'
                AND hpr_val.ptDescription = 'Success'
                and hpr_val.patiententrytime > '20`enter code here`21-04-06'
where   hpr.ptprocess = 'refill'
and     hpr.pTDescription <> 'Success'
--and       hpr.patientID is not null  -- Not necessary because you will always have records in this table in this scenario
and     hpr.patiententrytime > '2021-04-06'
AND     hpr_Val.PatietID IS NULL

And on the extra dot in between the schema and table name... As Smor pointed out it is not necessary (Might even break the query) and rather used when you do not want to reference the schema when pointing to a database and table.

Long way: [database].[schema].[table] -- Example [MyDatabase].[dbo].[MyTable]
Short way: [database]..[table] -- Example [MyDatabase]..[MyTable]

Use NOT IN instead of CTE or temp table

1 Answers1