This topic has been reported for inappropriate content
Count of Total Appointments within 30 days of each Appointment Date
-
Good Afternoon.
I have a Table where each Record:
- Represents an Appointment.
- 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
-
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 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 -
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 -
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:The 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, then 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) - Jeff Moden wrote:
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:The 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, then which version?
Just to be sure, on the other post in the 7/2000 forum for this same subject (which is another good reason to not double post), the OP says he's using SQL Server 2017. I don't see anything special to 2019 or 2022 for the current answers so we're probably good to go there.
--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) -
Just to ask the question, I haven't tested any of the solutions and so this question is directed at Reh23 ...
Do any of the solutions, so far, do what you need them to do?
--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)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply