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?

score 5 · Answer 1 · answered Feb 23 '11 at 16:42

5

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

answered Feb 23 '11 at 16:42

Joe Stefanelli

132,803
19
237
235

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? – mellamokb Feb 23 '11 at 16:48
4

@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. – gjvdkamp Feb 23 '11 at 16:50
1

@gjvdkamp: Nice idea for the row_number()! Unfortunately, SQL Server doesn't have LEAD and LAG. – Joe Stefanelli Feb 23 '11 at 16:51
Hi sorry about lead and lag, spent too much time on oracle, they don't seem exist on SQL. – gjvdkamp Feb 23 '11 at 16:53
I posted the example I had been working on, and implemented your ROW_NUMBER() idea. – mellamokb Feb 23 '11 at 16:53
Guys tell me can i use this implementing for computed column? – Konstantin Mar 02 '18 at 15:06

score 2 · Answer 2 · edited Jun 17 '11 at 12:32

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

If you are the person who asked this question, please flag this answer for moderator attention so your accounts can be merged. — Tim Post, Jun 17 '11 at 12:33

score 0 · Answer 3 · answered Feb 23 '11 at 16:53

Here is an example using ROW_NUMBER() if the Id's aren't necessarily in order:

;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

gjvdkamp · Answer 4 · 2011-02-23T18:06:18.003

-1

edit: shoudld have read the question better...

Another go woudl be this:

;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 a normal join to get to the previous row would be faster than a CTE. You;d have to check for yourself though.

Looks easier to me.

Good luck, GJ

edited Feb 23 '11 at 18:06

answered Feb 23 '11 at 16:57

gjvdkamp

9,929
3
38
46

@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 Feb 23 '11 at 17:00
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? – mellamokb Feb 23 '11 at 17:02
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. – gjvdkamp Feb 23 '11 at 17:06
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. – mellamokb Feb 23 '11 at 17:11
Ah I get it, no i don't think so either, CTE is the way to go. – gjvdkamp Feb 23 '11 at 18:05

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

4 Answers4

Linked