Modify SQL to include cumulative sum of all enrollments

I am able to get members cancelled in a quarter with the below query –

SELECT DATEPART(YEAR, Canceldate) [Year],
DATEPART(QUARTER, Canceldate) [Quarter], COUNT(1) [id Count]
FROM Subscription
where DATEPART(YEAR, Canceldate) > 2016
GROUP BY DATEPART(YEAR, CancelDate),DATEPART(QUARTER, Canceldate)
ORDER BY 1,2

The output is

Year    Quarter  Count
2017    1       2406866
2017    2       1161904
2017    3       3432214
2017    4       10905218
2018    1       1416848
2018    2       258146
2018    3       2996401
2018    4       639415
2019    1       3425557

If we started out with 100 members and 1 member enrolled every quarter. How do I get the cumulative number of members enrolled during these periods. For example, I need this output

Year    Quarter  Count     Enrolled
2017    1       2406866    100
2017    2       1161904    101
2017    3       3432214    102
2017    4       10905218   103
2018    1       1416848    104
2018    2       258146     105
2018    3       2996401    106
2018    4       639415     107
2019    1       3425557    108

The following sql can be used to calculate enrollments for every quarter.

SELECT DATEPART(YEAR, EnrollmentDt) [Year],
DATEPART(QUARTER, EnrollmentDt) [Quarter], COUNT(1) [id Count]
FROM Subscription
where DATEPART(YEAR, EnrollmentDt) > 2016
GROUP BY DATEPART(YEAR, EnrollmentDt),DATEPART(QUARTER, EnrollmentDt)
ORDER BY 1,2

Answer

You would use window functions:

SELECT DATEPART(YEAR, Canceldate) as [Year],
       DATEPART(QUARTER, Canceldate) as [Quarter], COUNT(1) as [id Count],
       99 + ROW_NUMBER() OVER (ORDER BY MIN(CancelDate)) as Enrolled
FROM Subscription
WHERE DATEPART(YEAR, Canceldate) > 2016
GROUP BY DATEPART(YEAR, CancelDate),DATEPART(QUARTER, Canceldate)
ORDER BY 1,2

Advertisement

Answer