Summing Count of Appointments within 30 days for each Date

Transact-SQL
1

Good Morning,

I have the following Table where each single record:

  1. Represents an Appointment.
  2. Contains a field called Appointment Date.

What I am trying to achieve is this: for each Appointment Date in the Dataset, I wish to show a count of total Appointments which occurs within 30 days (of each Appointment Date). For example, if the Appointment Date is 1st July 2024, I wish to know to how many Appointments occurred between 1st July 2024 to 31st July 2024 (and assign this count to 1st July).

And continue this process for every single Appointment date within the Dataset.

Kind Regards

2

Forum Etiquette: How to post data/code on a forum to get the best help – SQLServerCentral

3 Likes
3

I would guess that using OUTER APPLY to count the rows would work - but that is really just a guess.

SELECT ...
     , cnt.TotalAppointments
  FROM dbo.Appointment   appt
 OUTER APPLY (
    SELECT TotalAppointments = count(*)
      FROM dbo.Appointment    a
     WHERE a.AppointmentDate >= DATEADD(day, -30, appt.AppointmentDate)
       AND a.AppointmentDate < appt.AppointmentDate) AS cnt
5

Use a window function for efficiency:

SELECT AppointmentDate, 
       COUNT(*) OVER (
           ORDER BY AppointmentDate 
           RANGE BETWEEN CURRENT ROW AND INTERVAL 30 DAY FOLLOWING
       ) AS TotalAppointments
FROM dbo.Appointment;

Or a self-join for better indexing:

SELECT a1.AppointmentDate, COUNT(a2.AppointmentDate) AS TotalAppointments
FROM dbo.Appointment a1
JOIN dbo.Appointment a2 
    ON a2.AppointmentDate BETWEEN a1.AppointmentDate AND DATEADD(DAY, 30, a1.AppointmentDate)
GROUP BY a1.AppointmentDate;

Both approaches work, choose based on performance needs.