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

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.

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???

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

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

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 ;-)

Actually it's probably not even daily totals, but the month totals as the days mentioned are the final days of their respective months

Please see here https://www.experts-exchange.com/help.jsp#hi17 

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

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

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 !!! :)

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) ;-)

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.

?

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

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

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.

Brilliant posts from both users.

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...

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.

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

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.

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

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

--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!

genius!!

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

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

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.

ORDER BY  m1.id  

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

my last SQL statement (with the SUM(Credit), SUM(DEBIT) is not right....will repost soon

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