LAG and LEAD based on parameter

Question

I have table - Invoices, with such structure:

Id	InvoiceNo	Date
1	10	11-12-21
2	20	12-12-21
3	30	13-12-21
4	40	NULL
5	50	14-12-21
6	60	NULL
7	70	NULL
8	80	15-12-21

What I need to do - I need to find InvoiceNo's, the date field of the next or previous line of which contains null. So, based on provided data - I should receive:

InvoiceNo
30
50
80

But how to do this? One option that I found - LAG() and LEAD() functions, and with these functions I can receive numbers and dates, but cannot use parameters - so cannot provide "Date is not null" check.

@Mureinik if previous or next record of current row contains null in "Date" field - I should return this row. — AtlasPromotion, Dec 20 '21 at 17:44

score 1 · Accepted Answer · answered Dec 20 '21 at 17:49

1

You can use lag and lead to find the previous and next rows, and then wrap the query with another query that returns only the rows where one of them was null. Note that lag of the first row and lead of the last raw will return null by default, so you need to explicitly state a non-null default, such as getdate():

SELECT InvoiceNo
FROM   (SELECT InvoiceNo,
               LAG(Date, 1, GETDATE()) OVER (ORDER BY InvoiceNo) AS lag_date,
               LEAD(Date, 1, GETDATE()) OVER (ORDER BY InvoiceNo) AS lead_date
        FROM   invoices) t
WHERE   lag_date IS NULL OR lead_date IS NULL

answered Dec 20 '21 at 17:49

Mureinik

297,002
52
306
350

Good answer, thank you! But what if I have a situation - two Date = null in a row? Current solution shows them too, and conditions don't work:( I tried to add "date is not null" to inner query, and outer too – AtlasPromotion Dec 20 '21 at 18:05
Updated the description – AtlasPromotion Dec 20 '21 at 18:06
1

Omg, I forgot about brackets - works fine, thank you! – AtlasPromotion Dec 20 '21 at 18:11

LAG and LEAD based on parameter

1 Answers1