CODEWITHSUNDEEP
sqlsql-server
Fat Budapest
Fat Budapest

Reputation: 29

SQL for current new patients only

I have a table of Appointments made by patients. I want to fetch those new patients who came to our medical facility in the current month, but that they were never here before, so we can send a welcome letter to new patients. I am not sure how to select. I want to use NOT IN, but not sure how to hold the ones in the current month, and recheck for past appointments. In this table, I would want only patient_id 004.

Patient_ID      Appt_Date time
001             2016-01-01
001             2015-05-09
002             2016-06-01
003             2016-07-01
003             2014-09-03
004             2016-07-02

Upvotes: 0

Views: 276

Answers (2)

Dmitriy Khaykin
Dmitriy Khaykin

Reputation: 5258

I would use group by and min() with the Month() and Year() functions for this. 

select patient_id
from patient_appt -- or whatever your table name is
group by patient_id -- groups all visits together for each patient
having year(min(appt_date_time)) = year(getdate()) -- checks earliest visit year is current year
    and month(min(appt_date_time)) = month(getdate()) -- checks earliest visit month is current month

This yields the patient whose first ever visit matches the current month and year.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269663

I would use aggregation for this:

select patient_id
from t
group by patient_id
having min(appt_date_time) > cast(dateadd(day, - day(getdate()), getdate()) as date)

Upvotes: 3

Related Questions