-
inter-row calculations possible?help needed.
Hi there I have a following table
Month| Debt1 | Debt1 |TotalDebtToDate
1 | 1 | 1 | 2
2 | 1 | 2 | 5
3 | 1 | 1 | 7
4 | 2 | 1 | 10
5 | 10 | 5 | 25
Basically I have Debt1 and Debt2 given and I need to calculate TotalDebtToDate
As you see it contains sum of all debts from previous monthes(TotalDebtToDate from a row above)
+ current debt1 + current debt2
Is it possible to write such query in MS SQL 2005 for calculating TotalDebtToDate?
please help!!!
Thank you very much.
-
Try something like this
select t1.[month], t1.debt1, t1.debt2, (select sum(debt1)+sum(debt2) from table1 where [month] <= t1.[month]) as tot
from table1 as t1
-
If you want to use 2005's CTE's, you can do something like this:
WITH TotalDebt([Month], Debt1, Debt2, TotalDebtToDate) AS
(SELECT [Month], Debt1, Debt2, Debt1 + Debt2 AS TotalDebtToDate
FROM YourTable
WHERE [Month] = 1
UNION ALL
SELECT A.[Month], A.Debt1, A.Debt2, (A.Debt1 + A.Debt2 + B.TotalDebtToDate) AS TotalDebtToDate
FROM YourTable A
JOIN TotalDebt AS B
ON A.[Month] = B.[Month] + 1
)
SELECT *
FROM TotalDebt
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|