A SQL QUERY ?

[vcavusoglu]

I HAVE GOT A TABLE AS

NO DEBT CLAIM
1 100 0
2 0 40
3 50 15

AND A WANT THIS RESULT QUERY
DEBT CLAIM REMAINDER
100 0 100
0 40 60
0 15 95


REMAINDER = PREVIOUS REMAINDER + LAST DEPT - LAST CLAIM

EXAMPLES

100= 0 + 100 - 0
60 = 100 + 0 - 40
95 = 60 + 50 - 15

THANKS

 

[chiph]

I don't know of a way to do this through straight SQL. Any answer I can come up with involves a cursor, or using client-side code to loop through the result set.

Chip H.

 

[tlbroadbent]

Here is a SQL solution. It involves a correlated subquery. You will need to change the sample to fit your table and column names. I intruduced a column you didn't have in your sample - DebtID. If your table only tracks one set of numbers and you want to summarize accross the entire table this column won't be needed. If you have some sort of breakout by DebtID or other identifier, you'll need a similar colummn to separate the data by the identifier.

Select
DebtAmt, ClaimAmt,
RemainderAmt=
(Select isnull(Sum(DebtAmt-ClaimAmt),0)
From tblDebt
Where DebtID=Curr.DebtID
And RecNo<Curr.RecNo)+
isnull(DebtAmt,0)-isnull(ClaimAmt,0)
From tblDebt As Curr

Hope this helps get you started. Let me know if you have any questions about the query. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[vcavusoglu]

thanks a lot Terry