jxharding
asked on
SQL Server 2000 ; have 10 rows of DEBIT + CREDIT columns, can SQL tally up the balances for each account?
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
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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???
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
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
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 ;-)
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
--------------------
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 !!! :)
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) ;-)
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) ;-)
ASKER
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.
?
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hahahaha - both the answer and assist to auke! - well I guess you lose some and win some !! :)
ASKER
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.
i apologise. i'm sitting on a 33.6K dial-up line and it takes ages to load.
ASKER
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...
ASKER
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.
thank you very very much for your time and effort.
it has been very complete and definitely a source of regular reference.
ASKER
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
..."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',
union all select 10139,'MyNote2','20070831'
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',
union all select 10139,'MyNote','20070831',
union all select 10139,'MyNote2','20070831'
union all select 10139,'MyNote','20070930',
union all select 12211,'MyNote','20070830',
union all select 12211,'MyNote1','20070830'
union all select 12211,'MyNote','20070831',
--------------------------
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
ASKER
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.
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
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',
union all select 10139,'MyNote','20070831',
union all select 10139,'MyNote2','20070831'
union all select 10139,'MyNote','20070930',
union all select 12211,'MyNote','20070830',
union all select 12211,'MyNote1','20070830'
union all select 12211,'MyNote','20070831',
--------------------------
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
ASKER
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
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',
union all select 10139,'MyNote','20070831',
union all select 10139,'MyNote2','20070831'
union all select 10139,'MyNote','20070930',
union all select 12211,'MyNote','20070830',
union all select 12211,'MyNote1','20070830'
union all select 12211,'MyNote','20070831',
--------------------------
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!
--Add a GO after the ALTER TABLE statement so:
ALTER TABLE #temptable ADD MutationID INT Identity
GO
--That would do the trick!
ASKER
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
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
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.
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.
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
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