SQL Query to return member count with a membership start date and end date by Month

Question

We would like to get a monthly count of the number of members who have a type of benefit by Month. If the end date for a member and benefit is last month then the active_b_count would decreased by 1 and similarly if the start date for the member and benefit is this month then the active_b_count would increase by one. The year_month column should be consecutive and start with the earliest start date month and year and should end with the last end date month and year Below is table and example data

create table #MemberTable
(memberId int
,benefitId int
,startDate datetime
,endDate dateTime)

insert #MemberTable
values(1,1,'2020-01-15','2022-01-15'),
(1,2,'2019-05-20','2020-10-15'),
(2,1,'2022-12-06','2024-01-20'),
(2,2,'2020-01-05','2020-11-06'),
(1,3,'2021-06-15','2022-07-01'),
(3,3,'2020-02-28','2022-02-28'),
(3,2,'2020-01-15','2020-12-15')

Below is the result set we want for benefit Id 2

/*
YEAR_MONTH  benefitId   active_b_count
201905          2           1
201906          2           1
201907          2           1
201908          2           1
201909          2           1
201910          2           1
201911          2           1
201912          2           1
202001          2           3
202002          2           3
202003          2           3
202004          2           3
202005          2           3
202006          2           3
202007          2           3
202008          2           3
202009          2           3
202010          2           3
202011          2           2
202012          2           1

*/

This was my attempt but I am struggling to get the months consecutive if no activity has taken place and I seem to get 1 active count all the way to 2024 when we know the latest end date for benefit id 2 was December 15th 2020

WITH YearMonths AS (
    SELECT DISTINCT
        YEAR(startDate) AS year,
        MONTH(startDate) AS month
    FROM #MemberTable
    UNION
    SELECT DISTINCT
        YEAR(endDate) AS year,
        MONTH(endDate) AS month
    FROM #MemberTable
),
AllYearMonths AS (
    SELECT DISTINCT year, month
    FROM YearMonths
),
BenefitCounts AS (
    SELECT
        ym.year * 100 + ym.month AS YEAR_MONTH,
        m.benefitId,
        count(m.benefitId) AS active_b_count
    FROM AllYearMonths ym
    CROSS JOIN (SELECT DISTINCT benefitId FROM #MemberTable) m
    LEFT JOIN #MemberTable t ON m.benefitId = t.benefitId
                             AND (ym.year * 100 + ym.month >= YEAR(t.startDate) * 100 + MONTH(t.startDate))
                             AND (ym.year * 100 + ym.month <= YEAR(t.endDate) * 100 + MONTH(t.endDate))
    WHERE ym.year * 100 + ym.month >= (SELECT MIN(YEAR(startDate) * 100 + MONTH(startDate)) FROM #MemberTable)
      AND ym.year * 100 + ym.month <= (SELECT MAX(YEAR(endDate) * 100 + MONTH(endDate)) FROM #MemberTable)
    GROUP BY ym.year, ym.month, m.benefitId
)
SELECT
    bc.YEAR_MONTH,
    bc.benefitId,
    bc.active_b_count
FROM BenefitCounts bc where bc.benefitId = 2
ORDER BY bc.YEAR_MONTH;

How did February 2019 become the start of the range? And should it go to 2024 even for benefit 2 that didn't have a row after December 2020? — Stuck at 1337, Aug 08 '23 at 16:48
@Stuckat1337 you are correct - I was assuming the other benefit Ids included would create the result set as I have it there - I will edit my question to remove the incorrect results — Paul, Aug 08 '23 at 17:10
Why does January 2020 only have 2? By my eyes it should be 3. Same for Feb, Mar, Apr. — Stuck at 1337, Aug 08 '23 at 17:21
Sorry, I'm still not clear what you want. If Martin guessed correctly, cool! — Stuck at 1337, Aug 08 '23 at 19:31
If you did want all of the date ranges the same then a tweak to my answer https://dbfiddle.uk/DQgZyS4N — Martin Smith, Aug 08 '23 at 19:57
Hello @MartinSmith - say we had a cost for each member and each benefit and we were are currently getting then count for each month - how would we include the sum of the costs for each month? so the table looks like this: ```create table #MemberTable (memberId int ,benefitId int, cost decimal (9,2) ,startDate datetime ,endDate dateTime)``` — Paul, Aug 10 '23 at 17:06

Martin Smith · Accepted Answer · 2023-08-08T17:58:54.030

Here's one way DB Fiddle - you need to tweak it slightly to get your desired YEAR_MONTH but basically does what you need.

WITH T10 AS
(
SELECT n
FROM  (VALUES(0), (0),
             (0), (0),
             (0), (0),
             (0), (0),
             (0), (0))n(n) 

), 
Nums(num) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1
FROM   T10 a, T10 b, T10 c 
),
Source AS
(
SELECT memberId,
       benefitId,
       DATEFROMPARTS(YEAR(startDate), MONTH(startDate), 1) AS startMonth,
       DATEFROMPARTS(YEAR(endDate), MONTH(endDate), 1)     AS endMonth
FROM   #MemberTable
), MinMax As
(
SELECT MIN(startMonth) AS MinMonth,
       MAX(endMonth)   AS MaxMonth,
       benefitId
FROM   Source 
GROUP BY benefitId
), Months AS
(
SELECT DATEADD(MONTH, num, MinMonth) AS mnth,
       benefitId
FROM   MinMax
       JOIN Nums
         ON Nums.num <= DATEDIFF(MONTH, MinMonth, MaxMonth) 

), Grouped AS
(
SELECT date      AS mnth,
       SUM(diff) AS diff,
       benefitId
FROM   Source
       CROSS apply (VALUES(startMonth, 1),
                          (DATEADD(MONTH,1,endMonth), -1)) v(date, diff )
GROUP  BY date , benefitId

)
SELECT    M.mnth,
          M.benefitId,
          SUM(G.diff) OVER (PARTITION BY M.benefitId ORDER BY M.mnth ROWS UNBOUNDED PRECEDING)
FROM      Months M    
LEFT JOIN Grouped G
ON        M.mnth = G.mnth AND M.benefitId = G.benefitId

Many thanks for this Martin- this one does exactly what I need — Paul, Aug 10 '23 at 07:58

SQL Query to return member count with a membership start date and end date by Month

1 Answers1