dcsimg
Results 1 to 3 of 3

Thread: inter-row calculations possible?help needed.

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

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