I am able to get members cancelled in a quarter with the below query –
x
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
Advertisement
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