Search

Friday, March 9, 2012

Generate running total

Use below query to generate running total:


Method 1

/*
 SQL Server Running Total Calculation
*/
DECLARE @DateStart date='2004-01-01'
;WITH CTE
     AS (SELECT ID = ROW_NUMBER() OVER(ORDER BY PurchaseOrderID),
                PurchaseOrderID, OrderDate = CONVERT(DATE,OrderDate),SubTotal
         FROM   AdventureWorks2008.Purchasing.PurchaseOrderHeader
         WHERE  OrderDate >= @DateStart)
SELECT   PurchaseOrderID,  OrderDate, SubTotal,
         RunningTotal = (SELECT SUM(SubTotal)
                         FROM   CTE
                         WHERE  ID <= A.ID)
FROM     CTE AS A
ORDER BY ID


Method 2

DECLARE @SalesTbl TABLE (DayCount smallint, Sales money, RunningTotal money)

DECLARE @RunningTotal money

SET @RunningTotal = 0

INSERT INTO @SalesTbl 
SELECT DayCount, Sales, null
FROM Sales
ORDER BY DayCount

UPDATE @SalesTbl
SET @RunningTotal = RunningTotal = @RunningTotal + Sales
FROM @SalesTbl

SELECT * FROM @SalesTbl



3 comments:

  1. brother,... i couldnot understand../

    ReplyDelete
  2. You can calculate running total of a column.
    For example you want to calculate the ledger balance after each entry.

    ReplyDelete
  3. Method 2 is elegant! Nicely done, Arun.

    ReplyDelete