CODEWITHSUNDEEP
databasesql-server-2008
sukesh
sukesh

Reputation: 2437

Cumulative Addition in SQL Server 2008

Sample data in tblData:

RowID   SID  Staken   DateTaken
---------------------------------------------
1       1    1        2014-09-15 14:18:11.997
2       1    1        2014-09-16 14:18:11.997
3       1    1        2014-09-17 14:18:11.997

I would like to get the daywise count of SIDs and also a cumulative sum like

Date       ThisDayCount  TotalCount
-----------------------------------
2014-09-15     1             1
2014-09-16     10            11
2014-09-17     30            41

This is what I have now in my stored procedure with the start & end date parameters. Is there a more elegant way to do this?

;WITH TBL AS 
(
    SELECT 
       CONVERT(date, asu.DateTaken) AS Date, 
       COUNT(*) AS 'ThisDayCount'
    FROM 
       tblData asu 
    WHERE 
       asu.SID = 1 
       AND asu.STaken = 1 
       AND asu.DateTaken >= @StartDate 
       AND asu.DateTaken <= @EndDate
    GROUP BY 
       CONVERT(date, asu.DateTaken)
 )
 SELECT 
     t1.Date, t1.ThisDayCount, SUM(t1.ThisDayCount) AS 'TotalCount'
 FROM 
     TBL t1
 INNER JOIN 
     TBL t2 ON t1.date >= t2.date
 GROUP BY
     t1.Date, t1.ThisDayCount

Upvotes: 0

Views: 50

Answers (1)

Ann L.
Ann L.

Reputation: 13975

I am not aware of a more elegant way to do that, other than perhaps with a subquery for your running total. What you have is pretty elegant by T-SQL standards.

But, depending on how many records you have to process and what your indexes look like, this could be very slow. You don't say what the destination of this information is, but if it's any kind of report or web page, I'd consider doing the running total as part of the processing at the destination rather than in the database.

Upvotes: 1

Related Questions