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

Question

I got the following table and want to calculate the Column2 value for each row using the value of the same column (Column2) from the previous row in sql without using a 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)) ie (1+5)*(1+2) 3 03/01/2011 3 76 => (1+18)*(1+3) = 19*4 and so on 

Any thoughts?

---

Answer 1

Assuming at least SQL Server 2005 for a 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 

Answer 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, as expected:

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

Answer 3

Here is an example of using ROW_NUMBER () if Id is not necessarily ok:

 ;with DataRaw as ( select 1 as Id, '01/01/11' as Date, 5 as Column1 union select 2 as Id, '02/01/11' as Date, 2 as Column1 union select 4 as Id, '03/01/11' as Date, 3 as Column1 ), Data as ( select RowId = ROW_NUMBER() over (order by Id), Id, Date, Column1 from DataRaw ), Data2 as ( select RowId, id, Date, Column1, Column1 as Column2 from Data d where RowId = 1 union all select d1.RowId, d1.id, d1.Date, d1.Column1, (1+d1.column1)*(1+d2.column2) as column2 from Data d1 cross join Data2 d2 where d2.RowId + 1 = d1.RowId ) select Id, Date, Column1, Column2 from Data2 

Answer 4

edit: shoudld better read the question ...

Otherwise it will be:

 ;with DataRaw as ( select 1 as Id, '01/01/11' as Date, 5 as Column1 union select 2 as Id, '02/01/11' as Date, 2 as Column1 union select 4 as Id, '03/01/11' as Date, 3 as Column1 ), Data as ( select Ord = ROW_NUMBER() over (order by Id), Id, Date, Column1 from DataRaw ), select -- formula goes here, using current and prev as datasources. from data current left join data prev on current.Ord = prev.Ord + 1 -- pick the previous row by adding 1 to the ordinal 

I think the normal join to go to the previous line will be faster than the CTE. You have to check yourself, though.

I feel better.

Good luck gj