Results 1 to 5 of 5
  1. #1
    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?
    please help!!!
    Thank you very much.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

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

  5. #5
    Join Date
    Dec 2006
    Posts
    3
    Quote 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
    Quote 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
  •