asked on
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable(
OrderBy int,
TotalUsers int,
FinancialYear int,
CombYear varchar(20),
Measure varchar(150))
INSERT INTO #TempTable (OrderBy, TotalUsers, FinancialYear, CombYear, Measure)
;With myTable as
(SELECT '1' AS OrderBy, count( D1.UserID) AS TotalUsers, FinYear, CombYr, 'Number of Active Accounts Created in Period' AS Name
FROM ext_User AS D1
INNER JOIN Dates
ON CONVERT(VARCHAR, UserCreated, 110) = CONVERT(VARCHAR, Date, 110)
where UserCreated < GetDate() and UserStatusCode = 'A'
GROUP BY FinYear, CombYr)
Select OrderBy, TotalUsers, FinYear, Sum(TotalUsers) OVER(ORDER BY FinYear) AS RollingTotal, CombYr, Name
from myTable
SELECT * FROM #TempTable
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable(
OrderBy int,
TotalUsers int,
FinancialYear int,
CombYear varchar(20),
Measure varchar(150))
INSERT INTO #TempTable (OrderBy, TotalUsers, FinancialYear, CombYear, Measure)
;With myTable as
(SELECT '1' AS OrderBy, count( D1.UserID) AS TotalUsers, FinYear, CombYr, 'Number of Active Accounts Created in Period' AS Name
FROM ext_User AS D1
INNER JOIN Dates
ON CONVERT(VARCHAR, UserCreated, 110) = CONVERT(VARCHAR, Date, 110)
where UserCreated < GetDate() and UserStatusCode = 'A'
GROUP BY FinYear, CombYr)
Select OrderBy, TotalUsers, FinYear, Sum(TotalUsers) OVER(ORDER BY FinYear) AS RollingTotal, CombYr, Name
from myTable
SELECT * FROM #TempTable