Solved

SQL Server 2000 ; have 10 rows of DEBIT + CREDIT columns, can SQL tally up the balances for each account?

Posted on 2007-11-30
2,194 Views
Last Modified: 2010-04-21
hi, this is my table. is it possible do work out the balance via SQL, im currently doing it in .net.
Account   Date                         Debit        Credit      Balance
10139      2007-08-31       2025.91      0.0      0.0
10139      2007-08-31       0.0      3620.11      0.0
10139      2007-09-30       4631.52      0.0      0.0
10139      2007-09-30       0.0      11336.71      0.0
10139      2007-09-30       0.0      14.8801      0.0
12211      2007-08-31       1352.76      0.0      0.0
12211      2007-08-31       0.0      3872.5      0.0

e.g. will be
Account   Date                         Debit        Credit      Balance
10139      2007-08-31       205      0.0      -205
10139      2007-08-31       0.0      305      +100
10139      2007-09-30       0.0      15      +115
12211      2007-08-31       135      0.0      -135
12211      2007-08-31       0.0      325      +190
0
Question by:jxharding
    32 Comments
     
    LVL 6

    Expert Comment

    by:PaultheBroker
    This is a classic running sum problem.  Basic approach is to do a triangular join with the table to itself.  This will be better if there is a time on the entries to make them unique.
    0
     
    LVL 9

    Accepted Solution

    by:
    This should do it
    CREATE TABLE
    
    	#mutations
    
    (	
    
    	Account INTEGER,
    
    	Date DATETIME,
    
    	Debit DECIMAL(9,2),
    
    	Credit DECIMAL(9,2)
    
    )
     
    
    INSERT INTO #mutations VALUES (10139, '2007-08-31', 2025.91, 0.0)
    
    INSERT INTO #mutations VALUES (10139, '2007-08-31', 0.0, 3620.11)
    
    INSERT INTO #mutations VALUES (10139, '2007-09-30', 4631.52, 0.0)
    
    INSERT INTO #mutations VALUES (10139, '2007-09-30', 0.0, 11336.71)
    
    INSERT INTO #mutations VALUES (10139, '2007-09-30', 0.0, 14.8801)
    
    INSERT INTO #mutations VALUES (12211, '2007-08-31', 1352.76, 0.0)
    
    INSERT INTO #mutations VALUES (12211, '2007-08-31', 0.0, 3872.5)
     
    
     /*
    
    e.g. will be
    
    Account   Date                         Debit        Credit      Balance
    
    10139      2007-08-31       205      0.0      -205
    
    10139      2007-08-31       0.0      305      +100
    
    10139      2007-09-30       0.0      15      +115
    
    12211      2007-08-31       135      0.0      -135
    
    12211      2007-08-31       0.0      325      +190
    
    */
     
     
    
    SELECT
    
    	account,
    
    	date,
    
    	SUM(Debit) AS Debit,
    
    	- SUM(Credit) AS Credit,
    
    	(SELECT ISNULL(SUM(m2.Debit),0) - ISNULL(SUM(m2.Credit),0) FROM #mutations m2 WHERE m1.account = m2.account AND m2.date <= m1.date AND 
     
    
    CASE WHEN m2.debit <> 0 THEN 0 ELSE 1 END <= CASE WHEN m1.debit <> 0 THEN 0 ELSE 1 END
     
    
    ) AS Balance
    
    FROM
    
    	#mutations m1
    
    GROUP BY
    
    	account,
    
    	date,
    
    	CASE WHEN debit <> 0 THEN 0 ELSE 1 END
    
    ORDER BY
    
    	account,
    
    	date,
    
    	CASE WHEN debit <> 0 THEN 0 ELSE 1 END
     
     
    
    DROP TABLE #mutations
    

    Open in new window

    0
     
    LVL 6

    Expert Comment

    by:PaultheBroker
    10139      2007-08-31 00:00:00.000      2025.91      .00      2025.91
    10139      2007-08-31 00:00:00.000      .00      -3620.11      -1594.20
    10139      2007-09-30 00:00:00.000      4631.52      .00      6657.43
    10139      2007-09-30 00:00:00.000      .00      -11351.59      -8314.27
    12211      2007-08-31 00:00:00.000      1352.76      .00      1352.76
    12211      2007-08-31 00:00:00.000      .00      -3872.50      -2519.74

    You accepted that???
    0
     
    LVL 6

    Expert Comment

    by:PaultheBroker
    This however, DOES work - however, as I said before you NEED the time on the datetime (or unique days) else you willl get the daily balance, not the strict running sum.

    create table mytable (account int, [date] datetime, Debit money, Credit  money, balance money)
    insert into mytable (account, [date],debit,credit)
    select 10139,'20070830',205,0.0
    union select 10139,'20070831',0.0,305
    union select 10139,'20070930',0.0, 15
    union select 12211,'20070830',135,0.0
    union select 12211,'20070831',0.0,325
    -------------------------------------

    select
          account = a.account,
          date = a.date,
          Debit = min(a.Debit),
          Credit = min(a.Credit),
          Balance = sum(b.credit - b.debit)
    from       MyTable a
    LEFT JOIN myTable b
          on a.account = b.account
          and b.date <= a.date
    GROUP BY a.account, a.date
    ORDER BY a.account, a.date
    0
     
    LVL 6

    Expert Comment

    by:PaultheBroker
    10139      2007-08-30 00:00:00.000      205.0000      .0000      -205.0000
    10139      2007-08-31 00:00:00.000      .0000      305.0000      100.0000
    10139      2007-09-30 00:00:00.000      .0000      15.0000      115.0000
    12211      2007-08-30 00:00:00.000      135.0000      .0000      -135.0000
    12211      2007-08-31 00:00:00.000      .0000      325.0000      190.0000
    0
     
    LVL 9

    Expert Comment

    by:auke_t
    It seemed to me he wanted the daily balance with the credit and the debit in two different lines.

    I work at the financial department of our compagny so I tend to understand the weird ways financial oriented people want their information ;-)
    0
     
    LVL 9

    Expert Comment

    by:auke_t
    Actually it's probably not even daily totals, but the month totals as the days mentioned are the final days of their respective months
    0
     
    LVL 6

    Expert Comment

    by:PaultheBroker
    0
     
    LVL 6

    Expert Comment

    by:PaultheBroker
    hi auke - maybe I'm not understnading something here, but I don't understand your solution at all...the first two rows seem to work fine - I have a put in $2025, and my balance is then $2025.  I then withdraw $3620, so now have -$1594 - ok so far.  However, the next month I put in $4,631 - but my balance is now $6657 - seems to me the balance should be about $3,000 in your example (-$1595 + $4631) but you have ($2025 + $4631)....anyway, I don't want to spend any more time on this - I'm interested in exercising my SQL, not my math !!!!!   Take care :) ... Paul
    0
     
    LVL 9

    Expert Comment

    by:auke_t
    Ah, true! Thanks Paul!
    --------------------
    So it should be:

    SELECT
          account,
          date,
          SUM(Debit) AS Debit,
          - SUM(Credit) AS Credit,
          (SELECT ISNULL(SUM(m2.Debit),0) - ISNULL(SUM(m2.Credit),0) FROM #mutations m2 WHERE m1.account = m2.account AND
    (
    (
          m2.date = m1.date  
    AND
          CASE WHEN m2.debit <> 0 THEN 0 ELSE 1 END <= CASE WHEN m1.debit <> 0 THEN 0 ELSE 1 END
    )
    OR m2.date < m1.date
    )
    ) AS Balance
    FROM
          #mutations m1
    GROUP BY
          account,
          date,
          CASE WHEN debit <> 0 THEN 0 ELSE 1 END
    ORDER BY
          account,
          date,
          CASE WHEN debit <> 0 THEN 0 ELSE 1 END
    0
     
    LVL 6

    Expert Comment

    by:PaultheBroker
    ok - I see you've used:

          m2.date = m1.date  
    AND
          CASE WHEN m2.debit <> 0 THEN 0 ELSE 1 END <= CASE WHEN m1.debit <> 0 THEN 0 ELSE 1 END

    which I think will allow there to be one debit and one credit on any particular day, and still give you a runnings sum across those two entries.  Presumably you could extend this to account for unique doollar amounts or something....still its (necessarily) all a bit nasty with the the time isn't it !!! :)
    0
     
    LVL 9

    Expert Comment

    by:auke_t
    As date includes time different times will result in different rows and as such everything will be fine.
    Actually I think it would be better to aggregate to months, but that might be a bit too much reading between the lines as I suspect it beeing the "end of month account balances" or something.

    For something totally different: It took me some time to realize why financial people have a Credit and a Debet column instead of just a positive and a negative value in the same column. Then I realized that negative values are used, but only to correct faulty entries. Just deleting the faulty record seems not to be allowed. But that's probably cause financial people can't be trusted!

    (Just hope my colleagues never get to read this comment) ;-)
    0
     

    Author Comment

    by:jxharding
    you guys are both brilliant. period.
    can i split the points between

    Ah, true! Thanks Paul!
    --------------------
    So it should be:
    SELECT
          account,                  ----------AND------------

    This however, DOES work - however, as I said before you NEED the time on the datetime (or unique days) else you willl get the daily balance, not the strict running sum.

    ?
    0
     
    LVL 9

    Expert Comment

    by:auke_t
    I guess you can't accept things in the Open Discussion, so I'll post it as a comment again:

    Ah, true! Thanks Paul!
    --------------------
    So it should be:

    SELECT
          account,
          date,
          SUM(Debit) AS Debit,
          - SUM(Credit) AS Credit,
          (SELECT ISNULL(SUM(m2.Debit),0) - ISNULL(SUM(m2.Credit),0) FROM #mutations m2 WHERE m1.account = m2.account AND
    (
    (
          m2.date = m1.date  
    AND
          CASE WHEN m2.debit <> 0 THEN 0 ELSE 1 END <= CASE WHEN m1.debit <> 0 THEN 0 ELSE 1 END
    )
    OR m2.date < m1.date
    )
    ) AS Balance
    FROM
          #mutations m1
    GROUP BY
          account,
          date,
          CASE WHEN debit <> 0 THEN 0 ELSE 1 END
    ORDER BY
          account,
          date,
          CASE WHEN debit <> 0 THEN 0 ELSE 1 END
    0
     
    LVL 6

    Assisted Solution

    by:PaultheBroker
    This however, DOES work - however, as I said before you NEED the time on the datetime (or unique days) else you willl get the daily balance, not the strict running sum.

    Thanks JX
    create table mytable (account int, [date] datetime, Debit money, Credit  money, balance money)
    
    insert into mytable (account, [date],debit,credit)
    
    select 10139,'20070830',205,0.0
    
    union select 10139,'20070831',0.0,305
    
    union select 10139,'20070930',0.0, 15
    
    union select 12211,'20070830',135,0.0
    
    union select 12211,'20070831',0.0,325
    
    -------------------------------------
     
    
    select 
    
          account = a.account, 
    
          date = a.date, 
    
          Debit = min(a.Debit), 
    
          Credit = min(a.Credit),
    
          Balance = sum(b.credit - b.debit)
    
    from       MyTable a
    
    LEFT JOIN myTable b
    
          on a.account = b.account
    
          and b.date <= a.date 
    
    GROUP BY a.account, a.date
    
    ORDER BY a.account, a.date
    

    Open in new window

    0
     
    LVL 6

    Expert Comment

    by:PaultheBroker
    hahahaha - both the answer and assist to auke! - well I guess you lose some and win some !! :)
    0
     

    Author Comment

    by:jxharding
    man i really screwed up on this one... i'm asking community again.
    i apologise. i'm sitting on a 33.6K dial-up line and it takes ages to load.
    0
     

    Author Closing Comment

    by:jxharding
    Brilliant posts from both users.
    0
     
    LVL 6

    Expert Comment

    by:PaultheBroker
    Don't worry about it - it was nice of you to even try - if the points actually meant something it would be one thing :) , but just the 'thank you' is nice to have...
    0
     

    Author Comment

    by:jxharding
    i cannot tell you how important this thread has been in the current situation that i'm trying to sort out.
    thank you very very much for your time and effort.
    it has been very complete and definitely a source of regular reference.
    0
     

    Author Comment

    by:jxharding
    hi, i know this thread is finished, i have just 1 seemingly very simple amendment that i kindly ask help on. I reckoned this is the most effective way of asking it to the main thread experts, else obviously i'll create a new thread and everyone is happy.


    ..."As date includes time different times will result in different rows and as such everything will be fine"
    that is true but i have a problem now, i have more than 1 transaction happening on 1 day and i only receive a DATE from the user .e.g

    union all select 10139,'MyNote','20070831',0.0,305
    union all select 10139,'MyNote2','20070831',0.0,305

    now the boss isn't happy with me, he says i must display both items:
    10139      2007-08-30       205.0000      .0000      MyNote      205.0000
    10139      2007-08-31      .0000      -305.0000      MyNote      -405.0000
    10139      2007-08-31       .0000      -305.0000      MyNote2      -405.0000

    but the running total must be:
    10139      2007-08-30       205.0000      .0000      MyNote      205.0000
    10139      2007-08-31      .0000      -305.0000      MyNote      -100.0000
    10139      2007-08-31       .0000      -305.0000      MyNote2      -405.0000

    i have tried and spent an hour on this but i cant get it. if possible?

    here's the template im practising on:

    create table mytable (account int,note nvarchar(50), [date] datetime, Debit money, Credit  money, balance money)
    insert into mytable (account,note, [date], debit,credit)
    select 10139,'MyNote','20070830',205,0.0
    union all select 10139,'MyNote','20070831',0.0,305
    union all select 10139,'MyNote2','20070831',0.0,305
    union all select 10139,'MyNote','20070930',0.0, 15
    union all select 12211,'MyNote','20070830',135,0.0
    union all select 12211,'MyNote1','20070830',134,0.0
    union all  select 12211,'MyNote','20070831',0.0,325
    -------------------------------------
    SELECT
          account,
          date,
          SUM(Debit) AS Debit,
          - SUM(Credit) AS Credit,Note,
          (SELECT ISNULL(SUM(m2.Debit),0) - ISNULL(SUM(m2.Credit),0) FROM mytable m2 WHERE m1.account = m2.account AND
    (
    (
          m2.date = m1.date  
    AND
          CASE WHEN m2.debit <> 0 THEN 0 ELSE 1 END <= CASE WHEN m1.debit <> 0 THEN 0 ELSE 1 END
    )
    OR m2.date < m1.date
    )
    ) AS Balance
    FROM
          mytable m1
    GROUP BY
          account,note,
          date,
          CASE WHEN debit <> 0 THEN 0 ELSE 1 END
    ORDER BY
          account,
          date,
          CASE WHEN debit <> 0 THEN 0 ELSE 1 END

    drop table mytable
    0
     

    Author Comment

    by:jxharding
    this is a big feat i know, i looked at
    http://www.sqlservercentral.com/articles/T-SQL/61539/
    because i picked up on the term "triangular join" here.
    it seems easier if there were ID's involved.
    0
     
    LVL 9

    Expert Comment

    by:auke_t
    Indeed, so you could just temporary add ID's!

    create table #mytable (account int,note nvarchar(50), [date] datetime, Debit money, Credit  money, balance money)
    insert into #mytable (account,note, [date], debit,credit)
    select 10139,'MyNote','20070830',205,0.0
    union all select 10139,'MyNote','20070831',0.0,305
    union all select 10139,'MyNote2','20070831',0.0,305
    union all select 10139,'MyNote','20070930',0.0, 15
    union all select 12211,'MyNote','20070830',135,0.0
    union all select 12211,'MyNote1','20070830',134,0.0
    union all  select 12211,'MyNote','20070831',0.0,325
    -------------------------------------

    SELECT
          *
    INTO
          #temptable
    FROM
          #mytable

    ALTER TABLE #temptable ADD MutationId INT Identity


    SELECT
          account,
          date,
          SUM(Debit) AS Debit,
          - SUM(Credit) AS Credit,
          Note,
          (SELECT ISNULL(SUM(m2.Debit),0) - ISNULL(SUM(m2.Credit),0) FROM #temptable m2 WHERE m1.account = m2.account AND

          m2.MutationId <= m1.MutationId)  AS Balance
    FROM
          #temptable m1
    GROUP BY
          account,
          note,
          MutationId,
          date
    ORDER BY
          account,
          date

    drop table #temptable
    drop table #mytable
    0
     

    Author Comment

    by:jxharding
    thank you, i have learned you and add a column of type identity and it'll insert ID's . great stuff.

    if i do this: i can see the Id's
    --------------------------------------
    create table #mytable (account int,note nvarchar(50), [date] datetime, Debit money, Credit  money, balance money)
    insert into #mytable (account,note, [date], debit,credit)
    select 10139,'MyNote','20070830',205,0.0
    union all select 10139,'MyNote','20070831',0.0,305
    union all select 10139,'MyNote2','20070831',0.0,305
    union all select 10139,'MyNote','20070930',0.0, 15
    union all select 12211,'MyNote','20070830',135,0.0
    union all select 12211,'MyNote1','20070830',134,0.0
    union all  select 12211,'MyNote','20070831',0.0,325
    -------------------------------------
    create table #temptable (account int,note nvarchar(50), [date] datetime, Debit money, Credit  money, balance money)
    INSERT INTO   #temptable
    SELECT      *FROM      #mytable

    ALTER TABLE #temptable ADD MutationID INT Identity

    select * from #TempTable

    drop table #temptable
    drop table #mytable
    -------------------------------------
    but if i replace  select * from #TempTable  WITH

    SELECT
          account,
          date,
          SUM(Debit) AS Debit,
          - SUM(Credit) AS Credit,
          Note,
          (SELECT ISNULL(SUM(m2.Debit),0) - ISNULL(SUM(m2.Credit),0) FROM #temptable m2 WHERE m1.account = m2.account AND

          m2.MutationID <= m1.MutationID)  AS Balance
    FROM
          #temptable m1
    GROUP BY
          account,
          note,
          MutationID,
          date
    ORDER BY
          account,
          date

    ----------------
    Server: Msg 207, Level 16, State 3, Line 18
    Invalid column name 'MutationID'.
    this is weird stuff, i can see the mutationid if i do a normal select

    0
     
    LVL 9

    Expert Comment

    by:auke_t
    --Ah, sql2000 does parse the temptable while parsing the script.
    --Add a GO after the ALTER TABLE statement so:

    ALTER TABLE #temptable ADD MutationID INT Identity
    GO

    --That would do the trick!
    0
     

    Author Comment

    by:jxharding
    genius!!
    0
     
    LVL 6

    Expert Comment

    by:PaultheBroker
    Please remember that you NEED to specify an order when creating the IDENTITY column - so you should ORDER BY when you create the temporary table.  I also suggest that you order not only by date, but also across the debit and credit columns, so that the results have a chance of being the same each time you run the report...

    INSERT INTO   #temptable
    SELECT      *FROM      #mytable
    --> ORDER BY account,date ASC,credit+debit DESC

    I presume you are using MSSQL 2000, as you didn't use the SELECT * INTO that auke suggested.  2005 has this cool syntax which would obviate the need to create an IDENTITY column:

    SELECT
    -->      id = ROW_NUMBER() OVER (order by account,date ASC,credit+debit DESC)
          ,*
    INTO #sortedtable
    FROM #mytable


    0
     
    LVL 6

    Expert Comment

    by:PaultheBroker
    and auke's final query is overly complex - two methods are being used together where one would do.  EITHER you put the subquery in the SELECT statement like this:

    SELECT
          account,
          date,
          Debit,
          Credit,
          Note,
          Balance = (
                SELECT SUM(isnull(m2.Credit,0) - ISNULL(m2.Debit,0))
                FROM      #sortedtable m2
                WHERE      m1.account = m2.account
                AND            m2.id <= m1.id)
    FROM
          #sortedtable m1
    ORDER BY
          account, date, (Credit + Debit) DESC
    0
     
    LVL 6

    Expert Comment

    by:PaultheBroker
    or you put it in the FROM statement like this....

    SELECT
          account      = m1.account,
          date            = MAX(m1.date),
          Debit            = SUM(m1.Debit),
          Credit      = SUM(m1.Credit),
          Note            = MAX(m1.Note),
          Balance      = SUM(isnull(m2.Credit,0) - ISNULL(m2.Debit,0))
    FROM    #sortedtable m1, #sortedtable m2
    WHERE      m1.account = m2.account
    AND            m2.id <= m1.id
    GROUP BY m1.account, m1.id
    ORDER BY
          m1.account, MAX(m1.date), MAX(m1.Credit + m1.Debit) DESC
    --------------------
    The two statements are equivalent - the choice would be dependent on if you wanted more than one column aggregated - in your case, as you only want the balance, then the first mehtod (in the SELECT column) would be most appropriate.
    0
     
    LVL 6

    Expert Comment

    by:PaultheBroker
    ORDER BY  m1.id  

    would have been better than repeating the ORDER BY used to create the id...both are equivalent.
    0
     
    LVL 6

    Expert Comment

    by:PaultheBroker
    my last SQL statement (with the SUM(Credit), SUM(DEBIT) is not right....will repost soon
    0
     
    LVL 6

    Expert Comment

    by:PaultheBroker
    sorry - stupid error - in the second version, you need to select a single row from table m1 (i.e with MAX, MIN etc), as this is the table which will be duplicated up...so this (like my very first answer) will work....I've included two more fields, RunningCredit and RunningDebit which gives you the more realistic scenario where this solution would be preferable to doing the triangular join in the SELECT  statement.  Hope this explains things a little better....

    SELECT
          m1.id,
          account      = m1.account,
          date      = MAX(m1.date),
          Debit      = MAX(m1.Debit),
          Credit      = MAX(m1.Credit),
          Note      = MAX(m1.Note),
         RunningCredit = SUM(isnull(m2.Credit,0)),
         RunningDebit= SUM(isnull(m2.Debit,0)),
          Balance      = SUM(isnull(m2.Credit,0) - ISNULL(m2.Debit,0))
    FROM    #sortedtable m1, #sortedtable m2
    WHERE      m1.account = m2.account
    AND      m2.id <= m1.id
    GROUP BY     m1.id, m1.account
    ORDER BY      m1.id
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Product Review - Android Remix

    Come along for the ride with our Senior Product Manager, Brian Matis, as he reviews the Android Remix.

    In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    677 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    28 Experts available now in Live!

    Get 1:1 Help Now