CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
sukesh
sukesh

Reputation: 2425

Cumulative sum in sql, in case of dates

TakenOn is a datetime column

With the procedure as 

;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

the output is : //(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 for the output to be

Date       NewSubs  TotalSubs
2014-09-19  2        2
2014-09-20  3        5
2014-09-21  1        6
2014-09-22  4        10

Upvotes: 4

Views: 3703

Answers (1)

Donal
Donal

Reputation: 32713

Try this: 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

Upvotes: 4

Related Questions