how to get balance/saldo? 1

[russland]

hello, hello.

i have need to create an account sheet. listing the bookings row by row (of course). at the end of each row i need the balance/saldo (of this account, in this row). Since this is an intermediate value I struggle to calculate it. I thought I simply could set the new saldo value in every line and i would be fine.... doesn't work that way.

So here's my question: How can i calculate intermediate values that change line by line?

Thanks heaps.

my failing example
=====================
DECLARE @saldo float
SELECT ID, Active, Passive, Text, Date, (@saldo = @saldo - Active) as saldo
FROM F_AA_BalanceSheet
WHERE accountID = 4020 AND year(date) = year(getdate())
ORDER BY DATE DESC

 

[JarlH]

Something like:

SELECT ID, Active, ... ,
(SELECT SUM(something) FROM F_AA_BalanceSheet
WHERE date < FMAIN.date
AND accountID = ...)
FROM F_AA_BalanceSheet FMAIN
WHERE...
ORDER BY...

I.e. for each row in the result, calculate the SALDO as the SUM of all previous transactions.

 

[dnoeth]

Sounds like a Cumulative Sum/Running Total, if your DBS supports SQL:1999 OLAP functions it's easy:

sum(column) over
(partition by anothercolumn
order by datecol rows
rows unbounded preceding)

Dieter

 

[russland]

jarlH

I tried to apply what you said. it works neat without using the date comparision ([date] < FMAIN.date) - of course without true saldo values. Adding the date comparision returns NULL to me? Why so?

thanks heaps

code WITH date comparision
======================

SELECT FMAIN.ID, Active, Passive, [Text], [Date], FirstName, LastName, Organization, F_UA_Users.ID AS UserID, OrderID,
(SELECT SUM(isNULL(Active,0)) FROM F_AA_BalanceSheet WHERE [date] < FMAIN.date AND accountID = 1) As Saldo
FROM F_AA_BalanceSheet FMAIN
LEFT OUTER JOIN F_UA_Users ON FMAIN.UserID = F_UA_Users.ID
WHERE accountID = 1 AND year(date) = year(getdate()) AND AccountID = 1

1
384.0000 NULL 2005-10-07 00:00:00.000 Brian Pepper 1002 NULL NULL 3
210.0000 NULL 2005-10-07 00:00:00.000 Thobias Walty 1003 NULL NULL





code WITHOUT date comparision
==================================================

SELECT FMAIN.ID, Active, Passive, [Text], [Date], FirstName, LastName, Organization, F_UA_Users.ID AS UserID, OrderID,
(SELECT SUM(isNULL(Active,0)) FROM F_AA_BalanceSheet WHERE) As Saldo
FROM F_AA_BalanceSheet FMAIN
LEFT OUTER JOIN F_UA_Users ON FMAIN.UserID = F_UA_Users.ID
WHERE accountID = 1 AND year(date) = year(getdate()) AND AccountID = 1

1 384.0000 NULL 2005-10-07 00:00:00.000 Brian Pepper 1002 NULL 594.0000
3 210.0000 NULL 2005-10-07 00:00:00.000 Thobias Walty 1003 NULL 594.0000

 

[JarlH]

Oops, maybe you should change "WHERE [date] < FMAIN.date" to "WHERE [date] <= FMAIN.date", to include the current row when doing the SUM().

Or, if you know that ID is increasing for every row, you can use that column instead. (I know MS SQL Server treats dates in a non-ANSI compliant manner...)

BTW, ISNULL isn't ANSI. Check out the ANSI function COALESCE instead.

 

[russland]

What JarlH stated is absolutely fine. (if you look at the date values that came out of my report you will notice why it would return null .... dump fkuc...) no date values must be the same. otherwise jarlH's second proposal - choosing unique id - must be selected.

Thanks again JarlH,

russland