Is it possible to ignore NULL values when using LAG() and LEAD() functions in SQL Server? -

Is it possible to ignore NULL values when using LAG() and LEAD() functions in SQL Server? -

as know lag() & lead() analytic functions access info previous , next row in same result set without utilize of self-join. possible ignore null values until access not null value?

oracle 11 supports alternative ignore nulls want. of course, question sql server, heartening know functionality exist somewhere.

it possible simulate functionality. thought assign null values group, based on preceding value. in essence, counting number of non-null values before it. can correlated subquery. or, more interestingly, difference of 2 row numbers. within group, can utilize max().

i think next want. assume col contains null values , ordering has ordering rows:

select t.*, max(col) on (partition grp) lagonnull (select t.*, (row_number() on (order ordering) - row_number() on (partition col order ordering) ) grp table t ) t;

the lead() similar ordering reversed. and, work additional partitioning keys, need add together them window expressions.

sql sql-server-2012