CODEWITHSUNDEEP
sqlrolling-computation
Pedro Laginha
Pedro Laginha

Reputation: 21

Unique IDs per 3 month rolling period in SQL

I have an Appointments table with the columns 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? I am using SSMS 20.2 with Azure Synapse.

I tried joining the table onto itself, where the date of consultation is within the last 3 months and that does seem to give me the list of unique IDs, but I can't figure out a way to merge it with the list of unique IDS that have had more than 1 consultation (row in the original table), where both values are showing against the latest month. Hope that made any sense..

[EDIT] I added the tool I am using and sample data. 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).

Here's the sample data:

MemberID DateOfConsultation MonthStart
170092800 22/10/2024 01/10/2024
163861342 21/10/2024 01/10/2024
170623737 21/10/2024 01/10/2024
170759670 21/10/2024 01/10/2024
170092800 21/10/2024 01/10/2024
170759670 03/12/2024 01/12/2024
163861342 06/12/2024 01/12/2024
170759670 17/12/2024 01/12/2024
170759670 04/11/2024 01/11/2024
163861342 04/11/2024 01/11/2024
170759670 15/11/2024 01/11/2024
170759670 18/11/2024 01/11/2024
170759670 26/11/2024 01/11/2024
170759670 29/11/2024 01/11/2024

Upvotes: 1

Views: 47

Answers (0)

Related Questions