Count invoices created in next 6 Months after current invoice for each customer

I have data set where I need to count invoices created within next 6 months a invoice is created including any other invoice created on same day. This is some sample data:

Customer	Invoice	Date	Expected Ans
1	A	1/1/2021	2
1	B	3/1/2021	3
1	C	5/1/2021	3
1	D	8/1/2021	2
1	E	8/1/2021	2
1	F	10/1/2021	0
2	Z	1/1/2021	2
2	Y	5/1/2021	3
2	X	5/1/2021	3
2	W	10/1/2021	1
2	V	10/1/2021	1

CodePudding user response：

So it seems you want to count the number of rows that occur within the next 6 months per customer. Using that logic, I got your expected results.

Counting Future Invoices Within 6 Months Per Customer

DROP TABLE IF EXISTS #Invoice 

CREATE TABLE #Invoice
(
    Customer INT
    ,Invoice CHAR(1)
    ,InvoiceDate DATE
)

INSERT INTO #Invoice
VALUES
(1,'A','2021-01-01')
,(1,'B','2021-03-01')
,(1,'C','2021-05-01')
,(1,'D','2021-08-01')
,(1,'E','2021-08-01')
,(1,'F','2021-10-01')
,(2,'Z','2021-01-01')
,(2,'Y','2021-05-01')
,(2,'X','2021-05-01')
,(2,'W','2021-10-01')
,(2,'V','2021-10-01')


SELECT *
FROM #Invoice AS A
OUTER APPLY (
    SELECT COUNT(*) AS InvoiceCnt
    FROM #Invoice AS DTA
    WHERE A.Customer = DTA.Customer /*Match on same customer*/
    AND A.Invoice <> DTA.Invoice /*Don't join row to self*/
    AND A.InvoiceDate BETWEEN DATEADD(mm,-6,DTA.InvoiceDate) AND DTA.InvoiceDate /*Look for invoices within 6 months*/
) AS B