Roll over values if NULL

Transact-SQL
1

Hi ,

I have 3 columns ID ,Week_number and value as shown below:
loop1

What I am trying to do is to create a new column that has null values replaced with the most recent value(based on the week number) for each ID.

for example for ID AP:
The 1st lastest week with a value is week 33 and week 35 and 36 are null so I want the null values for week 35 and 36 to be replace with week 33 value which is 0.97629
ID AP has value for week 37 and null for week 38, so I want to replace the null value for week 38 with 0.98000

loop2

I have no clue on how to achieve this.
Appreciate the help.

Thanks in advance!

2 
SELECT Id, WeekNumber, Value, NewColumn
FROM 
	YourTable t1
	OUTER APPLY
    (
		SELECT TOP (1) Value AS NewColumn
		FROM
			YourTable t2
		WHERE
			t2.Id = t1.id
			AND t2.Weeknumber <= t1.weeknumber
			AND t2.Value IS NOT NULL
		ORDER BY
			t2.weeknumber DESC
    ) t2
ORDER BY id, weeknumber;
3

Thanks a lot James!! , that worked for me :slight_smile:

4

Are you sure it worked?
I ask because if you have more that a singe tears worth of "weeks" in that table, and you didn't add a partition by year...
There's a good chance you just smeared data across all the years.

Also, As long as you're on SQL Server 2012 or later, Itzik Ben-Gan has a much more efficient method for this very situation.