Is it possible to ignore NULL values when using LAG() and LEAD() functions in SQL Server?
Its possible using window functions.Have a read of this article by Itzik Ben-Gan for more details.
In the code below, the cte is getting the most recent NOT NULL id value, then the next select gets the actual column value. This example uses LAG.eg.
-- DDL for T1SET NOCOUNT ON;USE tempdb;IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL DROP TABLE dbo.T1;GOCREATE TABLE dbo.T1(id INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,col1 INT NULL);-- Small set of sample dataTRUNCATE TABLE dbo.T1;INSERT INTO dbo.T1(id, col1) VALUES( 2, NULL),( 3, 10),( 5, -1),( 7, NULL),(11, NULL),(13, -12),(17, NULL),(19, NULL),(23, 1759);;WITH C AS(SELECT id, col1, MAX(CASE WHEN col1 IS NOT NULL THEN id END) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS grpFROM dbo.T1)SELECT id, col1, (SELECT col1 FROM dbo.T1 WHERE id = grp) lastval FROM C;
Oracle 11 supports the option ignore nulls which does exactly what you want. Of course, your question is about SQL Server, but sometimes it is heartening to know that the functionality does exist somewhere.
It is possible to simulate this functionality. The idea is to assign null values to a group, based on the preceding value. In essence, this is counting the number of non-null values before it. You can do this with a correlated subquery. Or, more interestingly, with the difference of two row numbers. Then within the group, you can just use max().
I think the following does what you want. Assume that col contains NULL values and ordering has the ordering for the rows:
select t.*, max(col) over (partition by grp) as LagOnNullfrom (select t.*, (row_number() over (order by ordering) - row_number() over (partition by col order by ordering) ) as grp from table t ) t;The lead() is similar but the ordering is reversed. And, this will work with additional partitioning keys, but you need to add them to all the window expressions.