SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Invoice (
    CustomerID INT,
    InvoiceDt DATE
)
INSERT Invoice
VALUES
    (1, '2020-01-02'),
    (1, '2024-01-02'), -- Gao
    (1, '2024-02-02'),
    (2, '2020-12-01'),
    (2, '2021-12-01'),
    (2, '2022-12-01'),
    (2, '2023-12-01'),
    (2, '2024-02-01'),
    (3, '2024-02-12'), -- Gap (no prior, 18 months since start of data)
	(4, '2020-07-01'),
	(4, '2022-01-01'),-- First gap
	(4, '2024-01-01'),-- second gap
	(5, '2022-01-01'),-- First gap (no prior)
	(5, '2022-02-28'),-- second gap
	(5, '2023-09-01'),
    (6, '2021-01-15'),
    (6, '2022-07-14'),  -- No gap: One day <  18 months
    (7, '2021-01-15'),
    (7, '2022-07-15')   -- Gap: Exactly 18 months

-- All 18-month gaps
SELECT CustomerID, InvoiceDt
FROM (
    SELECT
        *,
        LAG(InvoiceDt) OVER(PARTITION BY CustomerID ORDER BY InvoiceDt) PriorInvoiceDt
    FROM Invoice
) IP
WHERE DATEADD(month, 18, ISNULL(PriorInvoiceDt, '2020-07-01')) <= InvoiceDt
ORDER BY CustomerID, InvoiceDt

-- Latest 18-month gap, using GROUP BY and MAX() to get latest per customer
SELECT CustomerID, MAX(InvoiceDt) AS InvoiceDt
FROM (
    SELECT
        *,
        LAG(InvoiceDt) OVER(PARTITION BY CustomerID ORDER BY InvoiceDt) PriorInvoiceDt
    FROM Invoice
) IP
WHERE DATEADD(month, 18, ISNULL(PriorInvoiceDt, '2020-07-01')) <= InvoiceDt
GROUP BY CustomerID
ORDER BY CustomerID

-- Latest 18-month gap, using ROW_NUMBER() to get latest per customer
-- This allows access to the entire invoice row, not just the date.
SELECT CustomerID, InvoiceDt, PriorInvoiceDt
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY InvoiceDt DESC) AS RN
    FROM (
        SELECT
            *,
            LAG(InvoiceDt) OVER(PARTITION BY CustomerID ORDER BY InvoiceDt) PriorInvoiceDt
        FROM Invoice I
    ) IP
    WHERE DATEADD(month, 18, ISNULL(PriorInvoiceDt, '2020-07-01')) <= InvoiceDt
) IPN
WHERE IPN.RN = 1
ORDER BY CustomerID

-- Same as the above, but using CTEs instead of subqueries
;WITH InvoiceAndPriorDate AS (
    SELECT
        *,
        LAG(InvoiceDt) OVER(PARTITION BY CustomerID ORDER BY InvoiceDt) PriorInvoiceDt
    FROM Invoice I
),
InvoiceAndPriorDateNumbered AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY InvoiceDt DESC) AS RN
    FROM InvoiceAndPriorDate IP
    WHERE DATEADD(month, 18, ISNULL(PriorInvoiceDt, '2020-07-01')) <= InvoiceDt
)
SELECT CustomerID, InvoiceDt, PriorInvoiceDt
FROM InvoiceAndPriorDateNumbered IPN
WHERE IPN.RN = 1
ORDER BY CustomerID

CustomerID	InvoiceDt
1	2024-01-02
3	2024-02-12
4	2022-01-01
4	2024-01-01
5	2022-01-01
5	2023-09-01
7	2022-07-15

CustomerID	InvoiceDt
1	2024-01-02
3	2024-02-12
4	2024-01-01
5	2023-09-01
7	2022-07-15

CustomerID	InvoiceDt	PriorInvoiceDt
1	2024-01-02	2020-01-02
3	2024-02-12	null
4	2024-01-01	2022-01-01
5	2023-09-01	2022-02-28
7	2022-07-15	2021-01-15

CustomerID	InvoiceDt	PriorInvoiceDt
1	2024-01-02	2020-01-02
3	2024-02-12	null
4	2024-01-01	2022-01-01
5	2023-09-01	2022-02-28
7	2022-07-15	2021-01-15