Running total based on Data column

Question

I have a temp table holding a result of balance changes to an account based on each day.

DateValue  DailyAmount
---------- ---------------------------------------
2014-04-21 0.00
2014-04-22 606.28
2014-04-23 -70.00
2014-04-24 -86.96
2014-04-25 -101.01
2014-04-26 -27.00
2014-04-27 0.00
2014-04-28 -75.00
2014-04-29 -12.00
2014-04-30 0.00
2014-05-01 -164.00
2014-05-02 -49.95
2014-05-03 0.00
2014-05-04 0.00
2014-05-05 -140.00
2014-05-06 538.23
2014-05-07 -70.00
2014-05-08 223.04
2014-05-09 0.00
2014-05-10 -50.00
2014-05-11 0.00
2014-05-12 -140.00
2014-05-13 -12.00
2014-05-14 0.00
2014-05-15 6179.81

I need to select from this table, adding a running balance, based on an opening balance variable I have.

Is it safe to select, and join to it's self somehow with Dateadd(DAY, -1, Datefield)?

My failed attempt (Wrong results) was this:

SELECT DateValue, ISNULL(r.Amount,0) AS DailyAmount, SUM(ISNULL(r.Amount,0))
FROM calendar c
LEFT JOIN @Result r
ON r.TheDate = c.DateValue
LEFT JOIN @Result r2
ON r2.TheDate = DATEADD(day, -1, r.TheDate)
WHERE c.DateValue BETWEEN @Start AND @End
GROUP BY c.DateValue, r.Amount

SQL Version is Microsoft SQL Server 2008 R2 (SP2)

Please clarify your specific problem by adding the code that you've attempted or the desired result. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. — Kermit, Apr 21 '14 at 22:11
May be duplicate of this question: http://stackoverflow.com/a/1154139/3430807 — Andreas, Apr 21 '14 at 22:12
@Craig . . . Also show the desired results you want for the data you have provided. — Gordon Linoff, Apr 21 '14 at 22:18

score 2 · Accepted Answer · answered Apr 21 '14 at 22:20

2

A typical way to get a running total in SQL is to use a correlated subquery:

select r.*,
       (select sum(r2.DailyAmount)
        from @Result r2
        where r2.DateValue <= r.DateValue
       ) as CumSum
from @Result r;

You might want additional conditions in the subquery, such as matching customers. And, SQL Server 2012 has direct support for cumulative sums.

answered Apr 21 '14 at 22:20

Gordon Linoff

1,242,037
58
646
786

Perfect! Thanks Gordon. – Craig Apr 21 '14 at 22:38
Okay. Its called `Corrleated subqueries`. Link - http://beginner-sql-tutorial.com/sql-subquery.htm – Erran Morad Apr 21 '14 at 22:59
Gordon I checked that this query will not work if dates are not in ascending order. Is there a way to still make your query work in that case ? Your @Result = My Sales table. My query `CREATE TABLE ##Sold( [DateValue] [datetime] NULL, [DailyAmount] [decimal](18, 2) NULL ) insert into ##Sold select top 10 * from sales order by DailyAmount desc select * from ##Sold select r.*, (select sum(r2.DailyAmount) from ##Sold r2 where r2.DateValue <= r.DateValue ) as CumSum from ##Sold r;` – Erran Morad Apr 21 '14 at 23:09
@BoratSagdiyev . . . This should have nothing to do with the dates being in a particular order. The condition `r2.DateValue <= r.DateValue` is the only condition needed. – Gordon Linoff Apr 22 '14 at 02:28
@GordonLinoff - I swear I saw some strange output. I'll make a fiddle sometime and check again. – Erran Morad Apr 22 '14 at 04:07

score 0 · Answer 2 · answered Apr 21 '14 at 22:31

There is a pretty extensive discussion of different strategies for running totals on SQLPerformace.com.

If you only have daily entries than pretty much any solution will do as the performance won't be an issue. I'll show something different then Gordon's just for the sake of it.

;WITH x AS
(
    SELECT TOP 1 
        DateValue
       ,DailyAmount
       ,RunningTotal = DailyAmount
         FROM #Result
        ORDER BY DateValue ASC
    UNION ALL
    SELECT y.DateValue
       ,y.DailyAmount
       ,x.RunningTotal + y.DailyAmount
         FROM x 
        INNER JOIN 
        #Result y ON y.DateValue = DATEADD(DAY, 1, x.DateValue)
)
SELECT DateValue
      ,DailyAmount
      ,RunningTotal
    FROM x
    ORDER BY DateValue
OPTION (MAXRECURSION 10000)

Running total based on Data column

2 Answers2