Cumulative sum in sql, in case of dates
TakenOn - datetime column
Using the procedure
;WITH TBL AS
(SELECT CONVERT(date,TakenOn) AS Date,
COUNT(*) AS 'NewSubs'
FROM Urls (NOLOCK)
WHERE FId = 11
AND (TakenOn >= @StartDate)
AND (TakenOn <= @EndDate)
GROUP BY CONVERT(date,TakenOn))
SELECT t1.Date, t1.NewSubs,
SUM(t1.NewSubs) AS 'TotalSubs'
FROM TBL t1
INNER JOIN TBL t2 ON t1.Date >= t2.Date
GROUP BY t1.Date, t1.NewSubs
output: // (which is wrong for me)
Date NewSubs TotalSubs
2014-09-19 2 2
2014-09-20 3 6
2014-09-21 1 3
2014-09-22 4 16
What should I change to withdraw
Date NewSubs TotalSubs
2014-09-19 2 2
2014-09-20 3 5
2014-09-21 1 6
2014-09-22 4 10
+3
source to share
1 answer
Try the following: SUM(t1.NewSubs) over(order by t1.Date)
For example:
;WITH TBL AS
(SELECT CONVERT(date,TakenOn) AS Date,
COUNT(*) AS 'NewSubs'
FROM Urls (NOLOCK)
WHERE FId = 11
AND (TakenOn >= @StartDate)
AND (TakenOn <= @EndDate)
GROUP BY CONVERT(date,TakenOn))
SELECT t1.Date, t1.NewSubs,
SUM(t1.NewSubs) over(order by t1.Date) AS 'TotalSubs'
FROM TBL t1
INNER JOIN TBL t2 ON t1.Date >= t2.Date
GROUP BY t1.Date, t1.NewSubs
+3
source to share