Correlated subquery advice?

Transact-SQL
1

Hi everyone.

I have this setup for my SELECT statement, where I get all payments * all times that a customer has accepted a campaign:
|customer.Customer ID| campaignImport.name | campaignImport.date | payment.id| payment.date |
|customer.Customer ID| campaignImport.name | campaignImport.date | payment.id| payment.date |
|customer.Customer ID| campaignImport.name | campaignImport.date | payment.id| payment.date |

Example:
|123| FirstCampaignID| 03-03-2021| Payment 1| 03-03-2021|
|123| FirstCampaignID| 03-03-2021| Payment 2| 04-04-2021|
|123| FirstCampaignID| 03-03-2021| Payment 3| 05-05-2021|
|123| FirstCampaignID| 03-03-2021| Payment 4| 06-06-2021|
|123| SecondCampaignID| 05-05-2021| Payment 1| 03-03-2021|
|123| SecondCampaignID| 05-05-2021| Payment 2| 04-04-2021|
|123| SecondCampaignID| 05-05-2021| Payment 3| 05-05-2021|
|123| SecondCampaignID| 05-05-2021| Payment 4| 06-06-2021|

As you can see, I have duplicate payments for each campaign.

I want to only get the payments between the CampaignImportDate and the next CampaignImportDate .

The result should be this:

|123| FirstCampaignID| 03-03-2021| Payment 1| 03-03-2021|
|123| FirstCampaignID| 03-03-2021| Payment 2| 04-04-2021|
|123| SecondCampaignID| 05-05-2021| Payment 3| 05-05-2021|
|123| SecondCampaignID| 05-05-2021| Payment 4| 06-06-2021|

Do you have any advice on how to do this?

2

In future please supply data in consumable format with dates in ISO format:

CREATE TABLE #t
(
	CustomerID int NOT NULL
	,CampaignName varchar(30) NOT NULL
	,CampaignDate date NOT NULL
	,PaymentID varchar(30) NOT NULL
	,PaymentDate date NOT NULL
);
INSERT INTO #t
VALUES (123, 'FirstCampaignID', '20210303', 'Payment 1', '20210303')
	,(123, 'FirstCampaignID', '20210303', 'Payment 2', '20210404')
	,(123, 'FirstCampaignID', '20210303', 'Payment 3', '20210505')
	,(123, 'FirstCampaignID', '20210303', 'Payment 4', '20210606')
	,(123, 'SecondCampaignID', '20210505', 'Payment 1', '20210303')
	,(123, 'SecondCampaignID', '20210505', 'Payment 2', '20210404')
	,(123, 'SecondCampaignID', '20210505', 'Payment 3', '20210505')
	,(123, 'SecondCampaignID', '20210505', 'Payment 4', '20210606');

Try something like:

WITH NextDates
AS
(
	SELECT CustomerID, CampaignDate
		,LEAD(CampaignDate, 1, '9999') OVER (PARTITION BY CustomerID ORDER BY CampaignDate) AS NextCampaignDate
	FROM #t
	GROUP BY CustomerID, CampaignDate
)
SELECT T.CustomerID, T.CampaignName, T.CampaignDate, T.PaymentID, T.PaymentDate
FROM #t T
	JOIN NextDates N
		ON T.CustomerID = N.CustomerID
			AND T.CampaignDate = N.CampaignDate
WHERE T.PaymentDate >= T.CampaignDate
	AND T.PaymentDate < N.NextCampaignDate;
2 Likes
3

Thank you very much!!
I will supply data in consumable format next time!