CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
danish tahir
danish tahir

Reputation: 11

Handling month and sum in SQL Server 2008

I have a table with 3 columns: ID, AmountCollected, Date. I need to build a stored procedure that returns me data like this.

Month--CollectionSum
Jan2011|500
Feb2011|549
Mar2011|678
............

Jan 2011's Collection Sum should be the Sum of AmountCollected with dates of Jan2011 or before, Similarly Feb2011's Collection Sum should be the Sum of AmountCollected with dates of Feb2011 or before and so on....

Can you help me out on this? I need to build the query on SQL Server 2008.

Upvotes: 1

Views: 1261

Answers (3)

Robo
Robo

Reputation: 4706

WITH T AS (
    SELECT SUM(AmountCollected) AS SubTotal, YEAR([Date]) AS Y, MONTH([Date]) AS M,
    ROW_NUMBER() OVER (ORDER BY YEAR([Date]), MONTH([Date])) AS OrderID 
    FROM TestTable
    GROUP BY YEAR([Date]), MONTH([Date]) 
)
SELECT Y, M, SubTotal, 
 (SELECT SUM(T.SubTotal) FROM T WHERE OrderID <= A.OrderID) AS RunningTotal
FROM T AS A
ORDER BY Y, M

Here's another version without using sub-query, which should make it faster:

WITH T AS (
    SELECT SUM(AmountCollected) AS SubTotal, YEAR([Date]) AS Y, MONTH([Date]) AS M,
    ROW_NUMBER() OVER (ORDER BY YEAR([Date]), MONTH([Date])) AS OrderID 
    FROM TestTable
    GROUP BY YEAR([Date]), MONTH([Date]) 
)
SELECT A.Y, A.M, A.SubTotal, A.SubTotal + ISNULL(B.SubTotal, 0) AS RunningTotal
FROM T AS A
LEFT JOIN T AS B ON A.OrderID = B.OrderID + 1
ORDER BY A.Y, A.M

The RunninigTotal field adds current row's SubTotal with previous row's SubTotal. By not using another SUM to calculate the RunningTotal, you improve speed by avoiding each row having to add the values of all previous rows.

Upvotes: 1

Raihan
Raihan

Reputation: 10405

First create the regular monthwise sum table. Join two instances of this table with conditions to match each row of first table with rows from other table containing amount for this and all previous months. Finally group by the months. 

with SumTbl (YrMon, SumAmt) as
(
   select Year(dte)*100+Month(dte), sum(amount)
   from Tbl
   group by Year(dte)*100+Month(dte)
)
select t1.YrMon, sum (t2.SumAmt) as CumAmt
from SumTb1 t1, SumTbl t2
where t2.YrMon <= t1.YrMon
group by t1.YrMon
order by t1.YrMon;

Upvotes: 0

Brandon Moore
Brandon Moore

Reputation: 8780

Here's an example you can modify to fit your needs:

http://www.sqlservercentral.com/Forums/Topic460765-338-1.aspx

The key to the running total is creating a statement that joins the table to an expression that groups on the date. But instead of saying table.date = groupeddata.date, you say table.date <= groupeddata.date in order to pick up not just the current date, but all previous dates as well. Anyway, it will make more sense when you read the answer in the link.

Upvotes: 0

Related Questions