To get patients who had more than 3 appointments in a year

Transact-SQL
1

Hi All,

I am new to sql, I have a requirement to get the patients who have had more than 3 appointments in a year.

My table
patientid appointmentid appointmentdate
1 32 03/07/2015
3 67 06/07/2015
1 23 01/09/2015
5 54 09/17/2015
6 24 01/04/2015
1 75 10/06/2016
5 36 05/12/2015
6 84 01/09/2016

My output should be
patientid appointmentid appointmentdate
1 32 03/07/2015
1 23 01/09/2015
1 75 10/06/2016

Please help me to get this.
Thanks in advance!

2

Your example data can be queried by

WITH cte AS (
SELECT PatientID, AppointmentID, YEAR(AppointmentDate) AS AppointmentYear 
FROM MyTable
GROUP BY PatientID, AppointmentID, YEAR(AppointmentDate) 
HAVING COUNT(*) > = 3
)
SELECT MT.PatientID, MT.AppointmentID, MT.AppointmentDate 
FROM MyTable MT
INNER JOIN cte ON MT.PatientID = cte.PatientID AND MT.AppointmentID = cte.AppointmentID;

Please note this may not be the best way and has not been tested.

3

You don't want the appointment ID in the GROUP BY, and the JOIN needs touched up a bit:

;WITH cte AS (
    SELECT PatientID, YEAR(AppointmentDate) AS AppointmentYear 
    FROM dbo.table_name
    GROUP BY PatientID, YEAR(AppointmentDate) 
    HAVING COUNT(*) >= 3
)
SELECT tn.PatientID, tn.AppointmentID, tn.AppointmentDate 
FROM cte
INNER JOIN dbo.table_name tn ON tn.PatientID = cte.PatientID AND YEAR(tn.AppointmentDate) = cte.AppointmentYear;
1 Like
4

Thanks @ScottPletcher. That's what I get for not double checking.:slight_smile:

5

It worked. Thank you so much for your time and help.