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
34
2,270 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
Comment
Question by:jxharding
  • 16
  • 8
  • 8
34 Comments
 
LVL 6

Expert Comment

by:PaultheBroker
Comment Utility
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:
auke_t earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 6

Expert Comment

by:PaultheBroker
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
PaultheBroker earned 250 total points
Comment Utility
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
Comment Utility
hahahaha - both the answer and assist to auke! - well I guess you lose some and win some !! :)
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:jxharding
Comment Utility
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
Comment Utility
Brilliant posts from both users.
0
 
LVL 6

Expert Comment

by:PaultheBroker
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
--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
Comment Utility
genius!!
0
 
LVL 6

Expert Comment

by:PaultheBroker
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
my last SQL statement (with the SUM(Credit), SUM(DEBIT) is not right....will repost soon
0
 
LVL 6

Expert Comment

by:PaultheBroker
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

6 Experts available now in Live!

Get 1:1 Help Now