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