sqlsql-servercalculated-columns

Return Value of Previous Row Column


I have the dataset below

Record_Number Col1 Col2 Col3 (Calculated Column)
1 123 1 123
2 456 1 456
3 789 2 456
4 147 2 456
5 258 3 258
6 852 4 258
7 963 2 258
8 213 1 213

I can't figure out a way to create a calculated column that does the following:

If Col2 is equal to 1 or 3, THEN return Col1 of current row ELSE return value of previous row column (Col3/Calculated Column)

Is there a way to pull a value from a calculated column? I've tried using LAG() and went down the road of using recursive CTE but couldn't figure out how to get it to work.

I tried (I know its incorrect):

SELECT Record_Number, Col1, Col2,
    CASE 
        WHEN Col2 IN (1, 3) THEN Col1
        ELSE LAG(Col3) OVER (ORDER BY Record_Number)
    END AS 'Col3'
FROM tbl1

Unsuccessfully tried using the LAG() function.


Solution

  • First create a group to find the rows that satisfy the condition versus the ones that does not. This is done as part of the query

    select *,
        sum(case  when col2 in (1, 3) then 1 else 0  end) 
        over (order by record_number) AS grp
    from test ;
    

    grp effectively creates groups of rows where the col3 value needs to be the same.

    At this stage output is

    record_number col1 col2 grp
    1 123 1 1
    2 456 1 2
    3 789 2 2
    4 147 2 2
    5 258 3 3
    6 852 4 3
    7 963 2 3
    8 213 1 4

    And then you can partition based on each group created, for example for group 2, it will look at the first value in that partition which is 456 ordered by row_number.

    unbounded preceding and current row ensures it looks back at the start of each partition till current row.

    select
        record_number,col1,col2,
        case 
            when col2 IN (1, 3) then col1
            else first_value(col1) 
            over (partition by grp order by record_number
                rows between unbounded preceding and current row
            )
        end as col3
    from (
        select *,
            sum(case  when col2 in (1, 3) then 1 else 0  end) 
            over (order by record_number) AS grp
        from test
    ) t
    order by record_number;
    

    Fiddle

    Output

    record_number col1 col2 col3
    1 123 1 123
    2 456 1 456
    3 789 2 456
    4 147 2 456
    5 258 3 258
    6 852 4 258
    7 963 2 258
    8 213 1 213