Count of Total Appointments within 30 days of each Appointment Date

Question

This topic has been reported for inappropriate content

Count of Total Appointments within 30 days of each Appointment Date

Reh23

Old Hand

Points: 353
More actions
January 9, 2025 at 12:38 pm

#4512608
Good Afternoon.
I have a Table where each Record:
1. Represents an Appointment.
2. Has a field Appointment Date.
What I am trying to achieve is this: For each Appointment date in the dataset I wish to count up all the Appointments which occur within 30 days. For example, for 1st July 2024, I wish to have a total count of all appointments which occurred between 1st July 2024 and 30th July 2024 (and assign this count to 1st July 2024).
And then continue this process for each Appointment Date within the Dataset.
Kind Regards

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Reh23 Old Hand Points: 353 More actions · Answer 1

So the Appointment Table will contain records with multiple entries for the same date (i.e. different clients having their appointments on the same day). I have given a sample list of dates and the assumption that we are only seeking the count of appointments within 3 days for each date.

The output we would expect for 1st and 2nd July would be:

This reply was modified 4 hours, 55 minutes ago by Reh23.

SoCal_DBD Hall of Fame Points: 3386 More actions · Answer 2

This should get you pointed in the right direction. Fully expect the heavy hitters to come up with some other great solutions.

-- Create a sample table
DROP TABLE IF EXISTS #SampleAppointments

CREATE TABLE #SampleAppointments
(AppointmentID INT IDENTITY(1,1) NOT NULL,
 AppointmentDate DATETIME NOT NULL)

-- Fill in 2 months' worth of dates (6/2024-7/2024)
IF OBJECT_ID('tempdb..#Tally', 'U') IS NOT NULL
    DROP TABLE #Tally

SELECT TOP(61) IDENTITY(INT, 0,1) AS 'N'
INTO #Tally
FROM sys.[columns] c1
CROSS JOIN sys.[columns] c2

INSERT INTO #SampleAppointments (AppointmentDate)
SELECT DATEADD(D, t.N, '6/1/2024 8:00')
FROM #Tally t

-- Review what we have
SELECT *
FROM #SampleAppointments

-- Count all appointments +/- 30 days for each date
; WITH ApptAnalysis AS
(SELECT appt.AppointmentID, appt.AppointmentDate,
        MatchingAppointmentID = apptmatch.AppointmentID,
        MatchingDate = apptmatch.AppointmentDate,
        MatchingDateDiff = DATEDIFF(D, appt.AppointmentDate, apptmatch.AppointmentDate)
 FROM #SampleAppointments appt
 CROSS JOIN #SampleAppointments apptmatch
 WHERE DATEDIFF(D, appt.AppointmentDate, apptmatch.AppointmentDate) BETWEEN -30 AND 30
)

SELECT AppointmentID, 
       AppointmentDate,
       Within30Days = COUNT(MatchingAppointmentID)
FROM ApptAnalysis
WHERE AppointmentID <> MatchingAppointmentID
GROUP BY AppointmentID, AppointmentDate
ORDER BY AppointmentDate

Jonathan AC Roberts SSCoach Points: 18378 More actions · Answer 3

Using SoCal_DBD's data setup:

SELECT sa1.*, sa2.Count
  FROM #SampleAppointments sa1
 CROSS APPLY(SELECT COUNT(*) Count
               FROM #SampleAppointments sa2
              WHERE sa2.AppointmentDate BETWEEN sa1.AppointmentDate AND DATEADD(dd,30,sa1.AppointmentDate)) sa2

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 4

First of all, please stop posting graphics for the test data. See the article at the first link in my signature line below for why and one method for producing readily consumable output. At least copy and paste the data a violable text that we can copy and paste into our test code.

Second, in your original post, you said 30 days. In your most recent post you say 3 days.

Reh23 wrote:

SThe output we would expect for 1st and 2nd July would be:

Which one do you want AND are you actually using SQL Server 2022 or not? If not, the which version?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)