How to Make Lag() Ignore Nulls in SQL Server

How to make LAG() ignore NULLS in SQL Server?

SQL Server does not support the ignore nulls option for window functions such as lead() and lag(), for which this question was a nice fit.

We can work around this with some gaps and island technique:

select
    t.*,
    max(past_due_col) over(partition by grp) new_past_due_col
from (
    select 
        t.*,
        sum(case when past_due_col is null then 0 else 1 end)
            over(order by id) grp
    from mytable t
) t

The subquery does a window sum that increments everytime a non null value is found: this defines groups of rows that contain a non-null value followed by null values.

Then, the outer uses a window max() to retrieve the (only) non-null value in each group.

This assumes that a column can be used to order the records (I called it id).

Demo on DB Fiddle:

ID | PAST_DUE_COL            | grp | new_past_due_col       
-: | :---------------------- | --: | :----------------------
 1 | 91 or more days pastdue |   1 | 91 or more days pastdue
 2 | null                    |   1 | 91 or more days pastdue
 3 | null                    |   1 | 91 or more days pastdue
 4 | 61-90 days past due     |   2 | 61-90 days past due    
 5 | null                    |   2 | 61-90 days past due    
 6 | null                    |   2 | 61-90 days past due    
 7 | 31-60 days past due     |   3 | 31-60 days past due    
 8 | null                    |   3 | 31-60 days past due    
 9 | 0-30 days past due      |   4 | 0-30 days past due     
10 | null                    |   4 | 0-30 days past due     
11 | null                    |   4 | 0-30 days past due     
12 | null                    |   4 | 0-30 days past due     

Ignoring Nulls using Lag() in SQL Server 2018

If I understand your question correctly, you are getting null FLAGID for non- null attrib_43 becuase you are using a partition by sea.ATTRIB_43 clause.
Partition by divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
That is why the null values for ATTRIB_43 are grouped into one window and the non-null ATTRIB_43 will have a separate window for each distinct value, therefore giving a NULL for LAG() function in the first row of each window.
you should remove the clause partition by sea.ATTRIB_43 if you want lag values for all rows.

LAG(sea.ROW_ID,1) over (order by sea.CREATED_DTTM) AS 'FLAGID'

LAG(...) IGNORE NULLS OVER (ORDER BY....)

MariaDB does not support the IGNORE NULLS option. But, we can simulate LAG in MariaDB or MySQL using a correlated subquery, e.g.

SELECT
    id,
    col1,
    col2,
    (SELECT t2.col1 FROM yourTable t2
     WHERE t2.id < t1.id AND t2.col1 IS NOT NULL
     ORDER BY t2.id DESC LIMIT 1) col1_lag
FROM yourTable t1;

Demo

Edit:

The output you are expecting in your updated question is not really a lag, but we can use similar logic to get what you want:

SELECT
    claim_ts,
    step,
    step_no,
    desired_results,
    (SELECT t2.step_no FROM testTable t2
     WHERE t2.claim_ts <= t1.claim_ts AND t2.step = 'MAIN'
     ORDER BY t2.claim_ts DESC LIMIT 1) AS actual_results
FROM testTable t1;

Demo

LAG functions and NULLS

if it is null all the way up to the end then can take a short cut

declare @b varchar(20) = (select top 1 b from table where b is not null order by id desc);
declare @c varchar(20) = (select top 1 c from table where c is not null order by id desc); 
select is, isnull(b,@b) as b, insull(c,@c) as c 
from table;

SQL lag function avoid getting first row as null

An option would be to calculate the last known value from the table and use it as a default value in LAG:

DECLARE @Test TABLE (LOAD_DATE DATE, [VALUE] INT);

INSERT INTO @Test (LOAD_DATE, [VALUE])
VALUES
        ('2021-06-25', 25)
    ,   ('2021-06-26', 27)
    ,   ('2021-06-27', null)
    ,   ('2021-06-28', 29)
;

DECLARE @LoadDate DATE = '2021-06-24';

WITH LastValue AS (
    SELECT
        MAX([VALUE]) AS [VALUE] /* This is to make sure, there is at least one record returned (e.g. the date is earlier than the first record in the table. */
    FROM
        (
            SELECT TOP(1)
                [VALUE]
            FROM
                @Test
            WHERE
                LOAD_DATE <= @LoadDate
            ORDER BY
                LOAD_DATE DESC
        ) X
)
SELECT
        T.*
    ,   LAG(T.VALUE, 1, LV.[VALUE]) OVER (ORDER BY T.LOAD_DATE) AS PREV_VALUE
FROM
    @Test T
    CROSS JOIN LastValue LV
WHERE
    T.LOAD_DATE > @LoadDate
;

LAG and the first NULL

last_value is a good idea, just add window clause:

select id, date_, 
       nvl(cust, last_value(cust) ignore nulls over (partition by id order by date_ 
                 rows between unbounded preceding and unbounded following)) cust
  from a
  order by id, date_

^demo

---

Related Topics