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

1. Registered User
Join Date
Dec 2006
Posts
3

## Unanswered: 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. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
What happens when you have you get to January and you want to calculate last years debt? Is there a business rule that dictates storing just the month is good enough, or is that an actual date? Also, what are debt1 and debt2? Your proposal appears to violate basic relational concepts.

It's important to remember that the physical order of the records in your table is arbitrary. No one row is "above" another when it comes to analysis. You could run SELECT * FROM yourTable 999 times, and on the 1000th time, it might come back in a different order unless you specify an order by clause.

What you're proposing is a great example of where you would want to use an Excel spreadsheet...

3. another indirection layer
Join Date
May 2004
Location
Seattle
Posts
1,313

4. Registered User
Join Date
Feb 2007
Posts
62
check out the OVER/PARTION BY clause in conjunction with SUM

5. Registered User
Join Date
Dec 2006
Posts
3
Originally Posted by Teddy
Is there a business rule that dictates storing just the month is good enough, or is that an actual date? Also, what are debt1 and debt2? Your proposal appears to violate basic relational concepts.
I've done it for the sake of simplicity.
It's more complex and there are full dates
Originally Posted by Teddy
It's important to remember that the physical order of the records in your table is arbitrary. No one row is "above" another when it comes to analysis. You could run SELECT * FROM yourTable 999 times, and on the 1000th time, it might come back in a different order unless you specify an order by clause.

What you're proposing is a great example of where you would want to use an Excel spreadsheet...
yes. I have to reproduce smth from excel sheet.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•