DECLARE
@StartDate DATE,
@EndDate DATE,
@NumMonths INT,
SET @NumMonths = 12
SET @StartDate = DATEADD(m, -@NumMonths, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
SET @EndDate = DATEADD(m, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
SELECT ROW_NUMBER() OVER (ORDER BY SignUpDate) RowNum, SignUpDate,
TotalSignUp / TotalSignUp M1,
--M2 / TotalSignUp M2,
--M3 / TotalSignUp M3,
--M4 / TotalSignUp M4,
--M5 / TotalSignUp M5,
--M6 / TotalSignUp M6,
--M7 / TotalSignUp M7,
--M8 / TotalSignUp M8,
--M9 / TotalSignUp M9,
--M10 / TotalSignUp M10,
--M11 / TotalSignUp M11,
--M12 / TotalSignUp M12
FROM (
SELECT SignUpDate,
COUNT(DISTINCT ClientID) TotalSignUp,
SUM(CASE WHEN MonthActive = DATEADD(m, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0)) THEN 1.0 ELSE 0.0 END) M2,
SUM(CASE WHEN MonthActive = DATEADD(m, 2, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0)) THEN 1.0 ELSE 0.0 END) M3,
SUM(CASE WHEN MonthActive = DATEADD(m, 3, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0)) THEN 1.0 ELSE 0.0 END) M4,
SUM(CASE WHEN MonthActive = DATEADD(m, 4, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0)) THEN 1.0 ELSE 0.0 END) M5,
SUM(CASE WHEN MonthActive = DATEADD(m, 5, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0)) THEN 1.0 ELSE 0.0 END) M6,
SUM(CASE WHEN MonthActive = DATEADD(m, 6, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0)) THEN 1.0 ELSE 0.0 END) M7,
SUM(CASE WHEN MonthActive = DATEADD(m, 7, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0)) THEN 1.0 ELSE 0.0 END) M8,
SUM(CASE WHEN MonthActive = DATEADD(m, 8, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0)) THEN 1.0 ELSE 0.0 END) M9,
SUM(CASE WHEN MonthActive = DATEADD(m, 9, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0)) THEN 1.0 ELSE 0.0 END) M10,
SUM(CASE WHEN MonthActive = DATEADD(m, 10, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0)) THEN 1.0 ELSE 0.0 END) M11,
SUM(CASE WHEN MonthActive = DATEADD(m, 11, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0)) THEN 1.0 ELSE 0.0 END) M12
How can I perform a loop for the select statement so that I can dynamically produce the number of M select statements as set by the @numMonths variable?
e.g., I will want the user of the report to be able to select the @numMonths as an input variable and they could say, want to go back 6 months, or 24 months. I would want the select statement to dynamically pivot in a loop depending on what the @numMonths value is.
To put it in other words I don't want to type out M1-M36 36 times just in case the user wants to go back 3 years, that will look so messy. I want a 1 line 'template' and a Counter + 1 until it reaches the number of @numMonths
