I have an Appointments table with the fields MemberID and DateOfConsultation. Each member may have more than 1 consultation, with the same or different dates. I want to get a rolling 3 month period, where for the latest month, I get the data relevant to all the consultations that happened in that month and the 2 months before and I want to count, for each 3 month period, the number of distinct members and separately the number of distinct members who had more than 1 consultation in that 3 month period. Not the distinct number per month, but for the 3 month rolling periods. How can I achieve this?
February 27, 2025 at 5:51 pm
Welcome! Getting a useful answer can be tricky. So Jeff Moden wrote a great article that explains how to ask a question in such a way that it gets answered. Absolutely recommended reading! If you read and follow the instructions and post back, someone can crank out an answer easily. It's here: https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
In order to help you and provide a tested answer (instead of an educated guess), can you please provide a CREATE TABLE script for that table? (Something like this):
CREATE TABLE Appointments (AppointmentID INT IDENTITY(1,1), MemberID INT NOT NULL, DateOfConsultation DATE NOT NULL);
GO
Also, we need some records. They can be completely made up. We don't need any more than a few MemberIDs and a few appointment records per member.
INSERT INTO Appointments(MemberID, DateOf Consultation) VALUES (1, '3/1/2024'), (1,'6/1/2024'), (1,'9/1/2024'), (2,'2/1/2024'),(2,'3/1/2024');
but to answer your question, you can do this with a windowing function. (Kathy Kellenberger wrote a really good intro book on it when windowing functions were first added to SQL Server). Here's an article she wrote on it: https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/introduction-to-t-sql-window-functions/#:~:text=These%20functions%20are%20part%20of%20the%20ANSI%20SQL
February 28, 2025 at 8:47 am
Thank you for your help! The script is below:
For the reporting month of December 2024 for example, I would expect the results to be 4 unique members and 3 unique members with more than 1 appointment (over the months of October, November and December).
-- Create the table
CREATE TABLE Consultations (
MemberID INT,
DateOfConsultation DATE,
MonthStart DATE
);
-- Insert the data
INSERT INTO Consultations (MemberID, DateOfConsultation, MonthStart) VALUES
(170092800, '2024-10-22', '2024-10-01'),
(163861342, '2024-10-21', '2024-10-01'),
(170623737, '2024-10-21', '2024-10-01'),
(170759670, '2024-10-21', '2024-10-01'),
(170092800, '2024-10-21', '2024-10-01'),
(170759670, '2024-12-03', '2024-12-01'),
(163861342, '2024-12-06', '2024-12-01'),
(170759670, '2024-12-17', '2024-12-01'),
(170759670, '2024-11-04', '2024-11-01'),
(163861342, '2024-11-04', '2024-11-01'),
(170759670, '2024-11-15', '2024-11-01'),
(170759670, '2024-11-18', '2024-11-01'),
(170759670, '2024-11-26', '2024-11-01'),
(170759670, '2024-11-29', '2024-11-01');
February 28, 2025 at 1:08 pm
Something like this ...
DECLARE @StartOfCurrentWindow date = '2024-10-01';
DECLARE @StartOfNextWindow date = DATEADD(MONTH, 3, @StartOfCurrentWindow);
WITH cteBase AS (
SELECT c.MonthStart
, c.MemberID
--, NumAppointMentsInMonth = COUNT(*) OVER (PARTITION BY c.MemberID, c.MonthStart)
, NumAppointMentsInWindow = COUNT(*) OVER (PARTITION BY c.MemberID)
FROM Consultations AS c
WHERE c.MonthStart >= @StartOfCurrentWindow
AND c.MonthStart < @StartOfNextWindow
)
SELECT MembersAny = COUNT(DISTINCT b.MemberID)
, MembersMulti = COUNT(DISTINCT CASE WHEN b.NumAppointMentsInWindow > 1 THEN b.MemberID END)
FROM cteBase AS b
February 28, 2025 at 2:21 pm
This seems to work brilliantly! Do you know how I could get a rolling total, where the values for each reporting month (MembersAny, MembersMulti) correspond to the window in that month in the previous two months (For December, we'd be looking at the window of October, November and December).
Please see below a script for a larger table, including the whole of 2024. Hope this is not too long but please let me know if there is a better way to send it. Thank you very much for your help!
CREATE TABLE Consultations (
MemberID INT,
DateOfConsultation DATE,
MonthStart DATE
);
INSERT INTO Consultations (MemberID, DateOfConsultation, MonthStart) VALUES
(711671811,'2024-01-31','2024-01-01'),
(711671811,'2024-01-31','2024-01-01'),
(621703289,'2024-01-31','2024-01-01'),
(731466866,'2024-01-31','2024-01-01'),
(579762521,'2024-01-31','2024-01-01'),
(593820483,'2024-01-31','2024-01-01'),
(785793177,'2024-01-31','2024-01-01'),
(713833604,'2024-01-31','2024-01-01'),
(732268287,'2024-01-31','2024-01-01'),
(557882528,'2024-01-31','2024-01-01'),
(628416865,'2024-01-31','2024-01-01'),
(561657980,'2024-01-31','2024-01-01'),
(569896923,'2024-01-30','2024-01-01'),
(516447113,'2024-01-30','2024-01-01'),
(736258436,'2024-01-30','2024-01-01'),
(637637237,'2024-01-30','2024-01-01'),
(752785399,'2024-01-30','2024-01-01'),
(546282569,'2024-01-30','2024-01-01'),
(641023353,'2024-01-31','2024-01-01'),
(711671811,'2024-01-30','2024-01-01'),
(523030943,'2024-01-30','2024-01-01'),
(565974145,'2024-01-30','2024-01-01'),
(525303907,'2024-01-30','2024-01-01'),
(666460987,'2024-01-30','2024-01-01'),
(842258792,'2024-01-30','2024-01-01'),
(787939448,'2024-01-30','2024-01-01'),
(650225185,'2024-01-30','2024-01-01'),
(713833604,'2024-01-31','2024-01-01'),
(516447113,'2024-01-24','2024-01-01'),
(561657980,'2024-01-23','2024-01-01'),
(516447113,'2024-01-24','2024-01-01'),
(842258792,'2024-01-30','2024-01-01'),
(713833604,'2024-02-09','2024-02-01'),
(525303907,'2024-02-09','2024-02-01'),
(736258436,'2024-02-14','2024-02-01'),
(787939448,'2024-02-03','2024-02-01'),
(516447113,'2024-02-05','2024-02-01'),
(641023353,'2024-02-01','2024-02-01'),
(641023353,'2024-02-26','2024-02-01'),
(641023353,'2024-02-27','2024-02-01'),
(666460987,'2024-02-18','2024-02-01'),
(641023353,'2024-02-16','2024-02-01'),
(842258792,'2024-03-23','2024-03-01'),
(525303907,'2024-03-25','2024-03-01'),
(561657980,'2024-03-02','2024-03-01'),
(525303907,'2024-03-07','2024-03-01'),
(546282569,'2024-03-04','2024-03-01'),
(569896923,'2024-03-03','2024-03-01'),
(628416865,'2024-03-05','2024-03-01'),
(666460987,'2024-03-15','2024-03-01'),
(713833604,'2024-04-12','2024-04-01'),
(711671811,'2024-04-10','2024-04-01'),
(787939448,'2024-04-25','2024-04-01'),
(711671811,'2024-04-29','2024-04-01'),
(516447113,'2024-04-28','2024-04-01'),
(787939448,'2024-04-18','2024-04-01'),
(557882528,'2024-04-16','2024-04-01'),
(525303907,'2024-04-21','2024-04-01'),
(641023353,'2024-05-07','2024-05-01'),
(525303907,'2024-05-30','2024-05-01'),
(713833604,'2024-05-31','2024-05-01'),
(579762521,'2024-05-16','2024-05-01'),
(557882528,'2024-06-28','2024-06-01'),
(628416865,'2024-06-06','2024-06-01'),
(557882528,'2024-06-19','2024-06-01'),
(628416865,'2024-07-17','2024-07-01'),
(711671811,'2024-07-18','2024-07-01'),
(711671811,'2024-07-28','2024-07-01'),
(546282569,'2024-07-14','2024-07-01'),
(713833604,'2024-08-08','2024-08-01'),
(736258436,'2024-08-29','2024-08-01'),
(579762521,'2024-08-31','2024-08-01'),
(711671811,'2024-08-22','2024-08-01'),
(787939448,'2024-09-08','2024-09-01'),
(628416865,'2024-09-13','2024-09-01'),
(628416865,'2024-09-13','2024-09-01'),
(842258792,'2024-10-24','2024-10-01'),
(593820483,'2024-10-17','2024-10-01'),
(666460987,'2024-10-17','2024-10-01'),
(569896923,'2024-10-30','2024-10-01'),
(525303907,'2024-10-04','2024-10-01'),
(785793177,'2024-10-04','2024-10-01'),
(523030943,'2024-10-03','2024-10-01'),
(713833604,'2024-10-15','2024-10-01'),
(561657980,'2024-10-09','2024-10-01'),
(637637237,'2024-10-10','2024-10-01'),
(650225185,'2024-12-02','2024-12-01'),
(546282569,'2024-12-07','2024-12-01'),
(621703289,'2024-12-10','2024-12-01'),
(628416865,'2024-12-10','2024-12-01'),
(557882528,'2024-12-13','2024-12-01'),
(516447113,'2024-12-15','2024-12-01'),
(641023353,'2024-12-16','2024-12-01'),
(666460987,'2024-12-17','2024-12-01'),
(650225185,'2024-12-17','2024-12-01'),
(666460987,'2024-12-23','2024-12-01'),
(731466866,'2024-12-25','2024-12-01'),
(787939448,'2024-12-28','2024-12-01'),
(579762521,'2024-11-05','2024-11-01'),
(525303907,'2024-11-06','2024-11-01'),
(787939448,'2024-11-07','2024-11-01'),
(565974145,'2024-11-08','2024-11-01'),
(713833604,'2024-11-10','2024-11-01'),
(546282569,'2024-11-11','2024-11-01'),
(711671811,'2024-11-11','2024-11-01'),
(516447113,'2024-11-12','2024-11-01'),
(711671811,'2024-11-18','2024-11-01'),
(732268287,'2024-11-19','2024-11-01'),
(752785399,'2024-11-19','2024-11-01'),
(736258436,'2024-11-26','2024-11-01'),
(713833604,'2024-11-25','2024-11-01')
Something like this ...
WITH cteWindows AS (
SELECT c.MonthStart
, windowStart = DATEADD(mm, -2, c.MonthStart)
, windowEnd = DATEADD(mm, 1, c.MonthStart)
FROM Consultations AS c
GROUP BY c.MonthStart
)
, cteBase AS (
SELECT w.windowStart, w.windowEnd, w.MonthStart, d.MemberID, d.NumAppointMentsInWindow
FROM cteWindows AS w
OUTER APPLY (SELECT m.MemberID, NumAppointMentsInWindow = COUNT(*)
FROM Consultations AS m
WHERE m.DateOfConsultation >= w.windowStart
AND m.DateOfConsultation < w.windowEnd
GROUP BY m.MemberID
) AS d
)
SELECT b.windowStart, b.MonthStart
, MembersAny = COUNT(DISTINCT b.MemberID)
, MembersMulti = COUNT(DISTINCT CASE WHEN b.NumAppointMentsInWindow > 1 THEN b.MemberID END)
FROM cteBase AS b
GROUP BY b.windowStart, b.MonthStart
ORDER BY b.windowStart;
February 28, 2025 at 3:09 pm
That was quick! Such a clean query too. You sir are a true pro. And, just so you know, you beat AI (Co-Pilot) and stack overflow! Couldn't really get the answer I needed from either of those.
Thank you so much, greatly appreciated!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply