Return Value of Previous Row Column

Question

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.

Answer 1

If you are using SQL Server 2022 then you can use LAST_VALUE with a conditional expression while ignoring nulls e.g.

SELECT *
    , CASE WHEN Col2 IN (1, 3) THEN Col1
    ELSE
        LAST_VALUE(CASE WHEN Col2 IN (1, 3) THEN Col1 ELSE NULL END) IGNORE NULLS
        OVER (ORDER BY Record_Number ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    END AS Col3  
FROM tbl1
ORDER BY Record_Number;

Returns:

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

db<>fiddle

With inspiration from this answer

Answer 2

Here a working solution with an online version:

It simply loops over the records from start and works nearly identically like a human would do.

If it comes to a record where Col2 IN (1,3) (then @Inside = 1), it copies the value from Col1 to Col3 (and updates the @lastCol3). In the other case it just copies the @LastCol3 into Col3.

-- INIT database
CREATE TABLE tbl1 (
  Record_Number INT,
  Col1 INT,
  Col2 INT,
  Col3expected INT,
  Col3calculated INT
);

INSERT INTO tbl1(Record_Number, Col1, Col2, Col3expected) 
VALUES 
(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);

DECLARE @lastCol3 as int;
DECLARE @RecNumber as int;
DECLARE @Col1 as int;
DECLARE @Inside as int;
select @lastCol3 = null;

DECLARE tbl1_Cursor CURSOR FOR
  select record_number, col1, case when col2 in (1,3) then 1 else 0 end 
  from tbl1 
  order by record_number;
OPEN tbl1_Cursor;

FETCH NEXT from tbl1_Cursor into @RecNumber, @Col1, @Inside;

WHILE @@FETCH_STATUS = 0
   BEGIN 

      IF @Inside = 1 -- means same as "Col2 IN (1, 3)"
         BEGIN
            update tbl1 set col3calculated = @Col1
            where record_number = @RecNumber;
            select @lastCol3 = @Col1; 
         END
      ELSE 
        BEGIN
            update tbl1 set col3calculated = @lastCol3
            where record_number = @RecNumber;
        END

     FETCH NEXT from tbl1_Cursor into @RecNumber, @Col1, @Inside;
   END;

CLOSE tbl1_Cursor;
DEALLOCATE tbl1_Cursor;
GO

SELECT *
FROM tbl1
WHERE col3expected = col3calculated
ORDER BY record_number

And the output:

Record_Number	Col1	Col2	Col3expected	Col3calculated
1	123	1	123	123
2	456	1	456	456
3	789	2	456	456
4	147	2	456	456
5	258	3	258	258
6	852	4	258	258
7	963	2	258	258
8	213	1	213	213

Answer 3

This was my first answer, but I got an easier one at the end with Transact-SQL.

Here was my first idea with cascading CTEs, that is solveable, but needs lot of brain and solution is quite complex, and not yet done to the end:

What about this:

WITH groupLimits AS ( -- all places where neighboring rows change (col2 in (1,3) vs. col2 not in (1,3))
    SELECT A.Record_Number arn, A.Col1 ac1, A.Col2 ac2,
           B.Record_Number brn, B.Col1 bc1, B.Col2 bc2,
           CASE WHEN A.Col2 IN (1, 3) THEN 1 ELSE 0 END ain,
           CASE WHEN B.Col2 IN (1, 3) THEN 1 ELSE 0 END bin
    FROM tbl1 A
    JOIN tbl2 B
      ON A.Record_Number + 1 = B.Record_Number
         AND (
               (A.Col2 IN (1, 3) AND B.Col2 NOT IN (1, 3))
               OR
               (A.Col2 NOT IN (1, 3) AND B.Col2 IN (1, 3))
             )
    order by A.Record_Number
), groups as (
   select ...
   from groupLimits C
   join groupLimits D
     on ...
...
select ...
...

With above groupLimits you get the idea where groups of (neighbor) rows, that have the same condition, either Col2 IN (1, 3) or Col2 NOT IN (1, 3) end (even if the group contains only 1 row).

The (neighboring) entries of groupLimits could be joined again with each other to get the minimum and maximum record_number of a group of tbl1 entries, that have all either Col2 IN (1, 3) or Col2 NOT IN (1, 3) (ain, bin can help). Perhaps you have to add an additional entry for first and last of record_number in tbl1.

Then you can calculate the col3 value for a group of rows with Col2 NOT IN (1, 3) (e. g. record_number 3 and 4), that is the value of record_number 2 (before 3) = 456. The col3 value for all records in a group with rows with Col2 IN (1, 3) is anyway its col1 value and easy to handle.

Are these enough ideas/hints to solve it?

Answer 4

Here's a way to do it using a subquery:

CREATE TABLE #ver
(
    Record_Number INT
  , Col1 INT
  , Col2 INT
);

INSERT INTO #ver
(
    Record_Number
  , Col1
  , Col2
)
VALUES
(1, 123, 1)
, (2, 456, 1)
, (3, 789, 2)
, (4, 147, 2)
, (5, 258, 3)
, (6, 852, 4)
, (7, 963, 2)
, (8, 213, 1);

SELECT ov.Record_Number
    , ov.Col1
    , ov.Col2
    , CASE
        WHEN ov.Col2 IN ( 1, 3 ) THEN
            ov.Col1
        ELSE (
            SELECT TOP 1
                iv.Col1
            FROM #ver iv
            WHERE iv.Record_Number < ov.Record_Number
            AND iv.Col2 IN( 1, 3)
            ORDER BY iv.Record_Number DESC
        )
        END AS Col3
FROM #ver ov;

Answer 5

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

Answer 6

This is actually a simplified gaps and islands problem, so you can avoid the recursion altogether:

WITH data AS (
    SELECT  *
    FROM    (
        VALUES  (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)
    ) t (Record_Number,Col1,Col2,[Col3 expected])
)
SELECT  *
,   MAX(CASE WHEN col2 IN(1,3) THEN col1 END) OVER(PARTITION BY grouping) AS [Col3 actual]
FROM    (
    SELECT  *
    ,   SUM(CASE WHEN col2 IN(1,3) THEN 1 ELSE 0 END) OVER(ORDER BY Record_Number rows unbounded preceding) AS grouping
    FROM    data
    ) x

CASE WHEN col2 IN(1,3) THEN 1 ELSE 0 END calculates the desired values in form of flags, which are the summarized ordered by Record Number. This creates a rolling group counter.

Then by taking the maximum of desired value per group (MAX(CASE WHEN col2 IN(1,3) THEN col1 END) OVER(PARTITION BY grouping)) you get the result you're looking for.

Output:

Record_Number	Col1	Col2	Col3 expected	grouping	Col3 actual
1	123	1	123	1	123
2	456	1	456	2	456
3	789	2	456	2	456
4	147	2	456	2	456
5	258	3	258	3	258
6	852	4	258	3	258
7	963	2	258	3	258
8	213	1	213	4	213

Answer 7

I do not believe there is any way to reference another row using a window function from within a calculated column definition. However, you can wrap the operation up in a user-defined function using a TOP 1 lookup.

CREATE FUNCTION GetCol3 (@Record_Number INT, @Col1 INT, @Col2 INT)
RETURNS INT
AS
BEGIN
    RETURN CASE 
        WHEN @Col2 in (1, 3) THEN @Col1
        ELSE (
            SELECT TOP 1 Col1
            FROM tbl1
            WHERE Record_Number < @Record_Number
            AND Col2 in (1, 3)
            ORDER BY Record_Number DESC
        )
    END
END

Based on your expected results, I also added the condition AND Col2 in (1, 3) to the above.

We have a chicken-and-egg problem between the table and function definitions, so the table will first need to be defined without the computed column, the function created, and then the computed column added.

CREATE TABLE tbl1 (
    Record_Number INT,
    Col1 INT,
    Col2 INT
)

-- Define dbo.GetCol3 as above

ALTER TABLE tbl1
    ADD Col3 AS dbo.GetCol3(Record_Number, Col1, Col2)

SELECT * FROM tbl1

Results:

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

See this db<>fiddle for a demo.