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.
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;
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 |