LAG and LEAD based on parameter-CodePudding

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.

CodePudding user response：

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