SQL help

Transact-SQL
1

Hello SQL expert,

I am trying to do a total count on the membership_ID every month. For example,
Here is the logic:

  • January count will be just the January membership date only
  • February count will count all January membership dates
  • March count will count all January, and February Membership dates
  • April count will include all January, February, and March Membership dates

The current output as listed below:

image
image430×504 19.6 KB

The output I am trying to achieve is listed below:

image
image432×1040 43.4 KB

I provided the CREATE and INSERT a simple query for the above data listed below:

CREATE TABLE MembersTbl
(
Member_ID VARCHAR(9),
Membership_Date DATE
)

INSERT INTO MembersTbl (Member_ID,Membership_Date)
VALUES ('GRTS0001','01/01/2020'), ('GRTS0002','01/01/2020'), ('GRTS0003','01/01/2020')
,('GRTS0004','02/01/2020'), ('GRTS0005','02/01/2020'), ('GRTS0006','02/01/2020'), ('GRTS0007','02/01/2020')
,('GRTS0008','03/01/2020'), ('GRTS0009','03/01/2020')
,('GRTS00010','04/01/2020'), ('GRTS00011','04/01/2020'), ('GRTS00012','04/01/2020'), ('GRTS00013','04/01/2020'), ('GRTS00014','04/01/2020')

How do I write the SQL statement and insert this new output to the temp table?

Thank you all

2

hi

i hope this helps ..

; with cte as 
(
select distinct Membership_Date from MembersTbl
) 
select     'SQL Output '
         , * 
from 
    MembersTbl a 
	   join  
    cte b 
         on a.Membership_Date <= b.Membership_Date

image
image467×849 11.5 KB

1 Like
3 
;WITH
cte_monthly_memberships AS (
    SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, Membership_Date), 0) AS date) AS Membership_Month,
        COUNT(*) AS Monthly_Membership_Count
    FROM dbo.MembersTbl
    GROUP BY CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, Membership_Date), 0) AS date)
)
SELECT 
    *,
    SUM(Monthly_Membership_Count) OVER(ORDER BY Membership_Month) AS Rolling_Membership_Count
FROM cte_monthly_memberships
1 Like
4

Thank you Scott

5

Thank you Harris