1

Say I have the following table:

ID T R
1 2
1 3 Y
1 4
1 5
1 6 Y
1 7

I would like to add a column which equals the value from column T based on the last non-null value from column R. This means the following:

ID T R GOAL
1 2
1 3 Y
1 4 Y 3
1 5 4
1 6 Y 4
1 7 6

I do have many ID's so I need to make use of the OVER (PARTITION BY ...) clause. Also, if possible, I would like to use a single statement, like

SELECT *
,      GOAL
FROM TABLE

So without any extra select statement.

Beso
  • 1,176
  • 5
  • 12
  • 26
user11453289
  • 67
  • 6
  • Please make sure to tag correctly. (MS) SQL Server <> Snowflake. I removed the SQL Server tag for you. – squillman Oct 25 '21 at 16:30
  • Shouldn't the 4's in your output be 3 instead? Or at least the `R` value needs to be corrected for 4 in the input table? – shawnt00 Oct 25 '21 at 17:26
  • No the output is correct, I want the value of T corresponding to last non-null value from column R – user11453289 Oct 26 '21 at 13:24

1 Answers1

1

T is in ascending order so just null it out according to R and take the maximum looking backward.

select *,
    max(case when R is not null then T end)
      over (
        partition by id
        order by T
        rows between unbounded preceding and 1 preceding
    ) as GOAL
from TBL

http://sqlfiddle.com/#!18/c927a5/5

shawnt00
  • 16,443
  • 3
  • 17
  • 22