On each row, the
VALUEcolumn should either contain the actual value, or the “last_value” preceding the current row, ignoring all the nulls. Note that I specifically wrote this requirement using specific English language. We can now translate that sentence directly to SQL:
last_value (t.value) ignore nulls over (order by d.value_date)
Since we have added an
ORDER BYclause to the window function, the default frame
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWapplies, which colloquially means “all the preceding rows”. (Technically, that’s not accurate. It means all rows with values less than or equal to the value of the current row – see Kim Berg Hansen’s comment)
Only a few database products have this and SQL Server is not one of them.