Question: 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

```

Answer: 
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)

```
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.