# Thread: inter-row calculations possible?help needed.

1. Registered User
Join Date
Dec 2006
Posts
1

## 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?
Thank you very much.

2. Experts
Join Date
Nov 2002
Location
New Jersey, USA
Posts
3,918
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

3. Registered User
Join Date
Dec 2004
Posts
502
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
•