How to use SQL LAG function with condition

Question

I have a table as the following rows:

tipoProtocolo numeroProtocolo dataReferencia dataAssinatura dataVencimento
------------- --------------- -------------- -------------- --------------
1             47676           NULL           20150112       20151231
1             47676           20151231       20150209       NULL
1             47676           NULL           20150224       NULL
1             47676           NULL           20151005       NULL
1             47676           NULL           20151021       NULL
1             47676           NULL           20151026       NULL
1             47676           NULL           20151120       NULL

I've implemented a piece of code that gets the value from the dataVencimento column (previous row) to the dataRefencia column (red arrow in the image). However, I would like to check if the column dataVencimento (from the previous row) is NULL. If this condition is true I need to copy the value from the column dataReferencia from the previous row (blue arrow in the image).

SELECT tipoProtocolo,
        numeroProtocolo,
            LAG(dataVencimento, 1 ) OVER(
            PARTITION BY numeroProtocolo, tipoProtocolo
            ORDER BY dataAssinatura
            ) dataReferencia,
            dataAssinatura, 
            dataVencimento
FROM cte_ContratoAditivo 

Here is my SQL code:

SELECT tipoProtocolo, numeroProtocolo,
        LAG(dataVencimento, 1) OVER(
        PARTITION BY numeroProtocolo, tipoProtocolo
        ORDER BY dataAssinatura
        ) dataReferencia,
         dataAssinatura, dataVencimento
FROM cte_ContratoAditivo

Answer 1

What you want is lag(ignore nulls). Unfortunately, SQL Server does not support this.

If the dates are increasing, you can use a cumulative max:

select . . .,
       max(dataVencimento) over (
            partition by numeroProtocolo, tipoProtocolo
            order by dataAssinatura
            rows between unbounded preceding and 1 preceding
        ) as dataReferencia

If this is not the case, you can use two levels of aggregation:

select ca.*,
       max(dataVencimento) over (
            partition by numeroProtocolo, tipoProtocolo
            order by dataAssinatura
      ) as dataReferencia
from (select ca.*,
             count(dataVencimento) over (
                partition by numeroProtocolo, tipoProtocolo
                order by dataAssinatura
               ) as grouping
      from cte_ContratoAditivo ca
     ) ca;

The subquery counts the number of valid values. This is really to assign a group number to the rows. The outer query then spreads the value over the entire group.