Calculating value using previous value of a row in T-SQL

Question

I got following table and want to calculate value of Column2 on each row using the value of the same column (Column2) from the previous row in a sql without using cursor or while loop.

Id   Date             Column1    Column2
1    01/01/2011       5          5 => Same as Column1
2    02/01/2011       2          18 => (1 + (value of Column2 from the previous row)) * (1 + (Value of Column1 from the current row)) i.e. (1+5)*(1+2)
3    03/01/2011       3          76 => (1+18)*(1+3) = 19*4
and so on

Any thoughts?

---

Solution 1

Assuming at least SQL Server 2005 for the recursive CTE:

;with cteCalculation as (
    select t.Id, t.Date, t.Column1, t.Column1 as Column2
        from YourTable t
        where t.Id = 1
    union all
    select t.Id, t.Date, t.Column1, (1+t.Column1)*(1+c.Column2) as Column2
        from YourTable t
            inner join cteCalculation c
                on t.Id-1 = c.id
)
select c.Id, c.Date, c.Column1, c.Column2
    from cteCalculation c

Solution 2

I solved the problem, just mentioned.

This is my code:

;with cteCalculation as (
    select t.Id, t.Column1, t.Column1 as Column2
        from table_1 t
        where t.Id = 1
    union all
    select t.Id, t.Column1, (1+t.Column1)*(1+c.Column2) as Column2
        from table_1 t
            inner join cteCalculation c
                on t.Id-1 = c.id
),
cte2 as(
select t.Id, t.Column1 as Column3
        from table_1 t
        where t.Id = 1
    union all
    select t.Id, (select column2+1 from cteCalculation c where c.id = t.id)  as Column3
        from table_1 t
            inner join cte2 c2
                on t.Id-1 = c2.id
)

select c.Id, c.Column1, c.Column2, c2.column3
    from cteCalculation c
inner join cte2 c2 on c.id = c2.id

The result is as I was expected:

1           5   5   5
2           2   18  19
3           3   76  77

Comments

[mellamokb]

Just curious. I was trying to implement a recursive CTE myself, but wondering how this would be done if you couldn't assume the Id's were contiguous?

[gjvdkamp]

@mellamokb I think you can have a with cluase that does an row_num() over (order by ...) and then refer to that in the recursive CTE, but I'm not sure, don't have SQL here. Also you can look into lead() and lag(). Actually you don;t need the CTE if you use Lead and Lag, they sould be faster too.

[Joe Stefanelli]

@gjvdkamp: Nice idea for the row_number()! Unfortunately, SQL Server doesn't have LEAD and LAG.

[gjvdkamp]

Hi sorry about lead and lag, spent too much time on oracle, they don't seem exist on SQL.

[mellamokb]

I posted the example I had been working on, and implemented your ROW_NUMBER() idea.

[mellamokb]

@gjdkamp: This would only set values based on two consecutive rows. The calculation needs to accumulate over the entire data set, which AFAIK can only be accomplished with a recursive CTE or something like a cursor, with the OP doesn't want to use.

[mellamokb]

To further elaborate: you will need a reference to d2.Column2 so you can use the previous record's value, which will not exist. The query needs to look something like this: select d1.Ord, d1.Id, d1.Date, d1.Column1, (1+d1.column1)*(1+d2.column2) as column2 from data d1 left join data d2 on d1.Ord = d2.Ord + 1, but where does d2.Column2 come from because the underlying table doesn't have a Column2 source column?

[gjvdkamp]

Ok sorry, I didn't scroll to the right in the code view on the original post. We're joining the table against itself, then we have both rows of data we need (current and previous) together. Then you should be able to do the computation. I renamend the aliases to make the point.

[mellamokb]

No. You can't calculate Column2 from the current and previous row. You need the current and all of the previous rows put together. You don't have access to the Column2 calculation of the previous row either in your case, because each combination of two rows are taken together in isolation from the rest. Feel free to prove me wrong with a working example tested in SQL Server.

[gjvdkamp]

Ah I get it, no i don't think so either, CTE is the way to go.

[Tim Post]

If you are the person who asked this question, please flag this answer for moderator attention so your accounts can be merged.

[Konstantin]

Guys tell me can i use this implementing for computed column?