Running Total on 2 columns

  • Table A has day to day transactions, Table B has beginning balance. I'd like to get a running total balance day to day. Really what I want to do is use the previous days total to add the current days transaction to, but I don't know how to do it. The basic layout is below, but as you can see, I'm not getting the totals correct.

    Any help is appreciated!

    create table #current(acctNum int,

    dates date,

    transtype char(10),

    amt INT

    )

    insert into #current(acctNum, dates, transtype, amt)

    values (11,'7/1/2014','debit',1),

    (11,'7/2/2014','debit',3),

    (12,'7/3/2014','credit',5)

    create table #balance (acctNum int,

    AsOfDate date,

    balance INT

    )

    insert into #balance(acctNum, asofdate, balance)

    values(11,'7/1/2014',35),

    (12,'7/1/2014',12)

    select a.acctnum,

    a.dates,

    a.transtype,

    b.balance,

    a.amt,

    runningtotal = b.balance + a.amt

    from #current a inner join #balance b

    on a.acctNum = b.acctNum

  • See this article for one way to do this. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]

    Make sure you read and understand what is happening here. There are a number of thing that MUST be in place for this to work correctly.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • If you're using SQL Server 2012, the easiest method (not the fastest) would be something like this:

    select a.acctnum,

    a.dates,

    a.transtype,

    b.balance,

    a.amt,

    runningtotal = b.balance + SUM(a.amt) OVER( PARTITION BY a.acctnum ORDER BY a.dates)

    from #current a

    join #balance b on a.acctNum = b.acctNum AND a.dates >= b.AsOfDate

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thank you - just what I was looking for!

  • Here is a Window function running total example, performs quite nicely;-)

    😎

    USE tempdb;

    GO

    create table #current(acctNum int,

    dates date,

    transtype char(10),

    amt INT

    )

    insert into #current(acctNum, dates, transtype, amt)

    values (11,'7/1/2014','debit',1),

    (11,'7/2/2014','debit',3),

    (12,'7/3/2014','credit',5)

    create table #balance (acctNum int,

    AsOfDate date,

    balance INT

    )

    insert into #balance(acctNum, asofdate, balance)

    values(11,'7/1/2014',35),

    (12,'7/1/2014',12)

    ;WITH BASE_DATA AS

    (

    SELECT

    acctNum

    ,asofdate

    ,'balance' AS transtype

    ,balance

    FROM #balance

    UNION ALL

    SELECT

    acctNum

    ,dates

    ,transtype

    ,amt

    FROM #current

    )

    SELECT

    BD.acctNum

    ,BD.asofdate

    ,BD.transtype

    ,BD.balance

    ,SUM(BD.balance) OVER

    (

    PARTITION BY BD.acctNum

    ORDER BY BD.asofdate

    ROWS BETWEEN UNBOUNDED PRECEDING

    AND CURRENT ROW

    ) AS CURRENT_BALANCE

    FROM BASE_DATA BD;

    DROP TABLE #balance;

    DROP TABLE #current;

    Results

    acctNum asofdate transtype balance CURRENT_BALANCE

    ----------- ---------- ---------- ----------- ---------------

    11 2014-07-01 balance 35 35

    11 2014-07-01 debit 1 36

    11 2014-07-02 debit 3 39

    12 2014-07-01 balance 12 12

    12 2014-07-03 credit 5 17

    And another with a trantype twist

    USE tempdb;

    GO

    create table #current(acctNum int,

    dates date,

    transtype char(10),

    amt INT

    )

    insert into #current(acctNum, dates, transtype, amt)

    values (11,'7/1/2014','debit',1),

    (11,'7/2/2014','debit',3),

    (12,'7/3/2014','credit',5)

    create table #balance (acctNum int,

    AsOfDate date,

    balance INT

    )

    insert into #balance(acctNum, asofdate, balance)

    values(11,'7/1/2014',35),

    (12,'7/1/2014',12)

    ;WITH BASE_DATA AS

    (

    SELECT

    acctNum

    ,asofdate

    ,1 AS transtype

    ,balance

    FROM #balance

    UNION ALL

    SELECT

    acctNum

    ,dates

    ,CASE

    WHEN transtype = 'credit' THEN 1

    ELSE -1

    END AS transtype

    ,amt

    FROM #current

    )

    SELECT

    BD.acctNum

    ,BD.asofdate

    ,BD.transtype

    ,BD.balance

    ,SUM(BD.balance * BD.transtype) OVER

    (

    PARTITION BY BD.acctNum

    ORDER BY BD.asofdate

    ROWS BETWEEN UNBOUNDED PRECEDING

    AND CURRENT ROW

    ) AS CURRENT_BALANCE

    FROM BASE_DATA BD;

    DROP TABLE #balance;

    DROP TABLE #current;

    Results

    acctNum asofdate transtype balance CURRENT_BALANCE

    ----------- ---------- ----------- ----------- ---------------

    11 2014-07-01 1 35 35

    11 2014-07-01 -1 1 34

    11 2014-07-02 -1 3 31

    12 2014-07-01 1 12 12

    12 2014-07-03 1 5 17

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply