Propagating The Last Non-Null Value With T-SQL

Kevin Feasel

2018-08-08

T-SQL

Tomaz Kastrun shows a great use of window functions in T-SQL:

So you have NULL values in your SQL Server table and you want to populate those NULL values with the last non-NULL value, based on a particular order. Once you have only one NULL values encapsulated between two populated values, there are quick and fast solutions. But what if you find a larger gap of NULL values and you want to populate these values as well?

Click through for a partial solution, followed by the real solution.

Related Posts

ISNUMERIC And Unexpected Results

Jen Stirrup explains why ISNUMERIC isn’t all that great: I noted that one of the columns failed to convert VARCHAR to DECIMAL. The error message is below, and it’s usually fairly easy to sort:Error converting data type varchar to numeric Normally, I’d use ISNUMERIC to identify the rows that fail to have a value in that column that could be […]

Read More

sp_executesql WITH RECOMPILE

Erik Darling points out that running sp_executesql with the WITH RECOMPILE setting doesn’t really change anything: This’ll give us the key lookup plan you see above. If I re-run the query and use the 2010-12-30 date, we’ll re-use the key lookup plan. That’s an example of how parameters are sniffed. Sometimes, that’s not a good thing. […]

Read More

Categories

August 2018
MTWTFSS
« Jul Sep »
 12345
6789101112
13141516171819
20212223242526
2728293031