This is a follow-up to a couple of questions I have already asked here, but additional information has made things more complicated.
I have the list of customers, the accounts they owned, and the date the account was renewed. Nth Renewal indicates the n-th time the account was renewed. So if Nth Renewal = '1', this was the
first time the account was renewed/opened. If Nth Renewal = n not equal to 1, then that was the
n-th and last time the account was renewed.
Here is an example of one complicated customer:
CREATE TABLE #Table (
[Renewal Date] date,
[Customer] nvarchar(255),
[Account] nvarchar(255),
[Nth Renewal] nvarchar(255),
)
INSERT INTO #TABLE ([Renewal Date], [Customer], [Account], [Nth Renewal])
VALUES ('2017-09-29', 'John', '123', '1')
INSERT INTO #TABLE ([Renewal Date], [Customer], [Account], [Nth Renewal])
VALUES ('2022-01-31', 'John', '123', '6')
INSERT INTO #TABLE ([Renewal Date], [Customer], [Account], [Nth Renewal])
VALUES ('2017-10-31', 'John', '789', '1')
INSERT INTO #TABLE ([Renewal Date], [Customer], [Account], [Nth Renewal])
VALUES ('2019-01-31', 'John', '789', '3')
INSERT INTO #TABLE ([Renewal Date], [Customer], [Account], [Nth Renewal])
VALUES ('2019-03-29', 'John', '456', '1')
INSERT INTO #TABLE ([Renewal Date], [Customer], [Account], [Nth Renewal])
VALUES ('2022-03-31', 'John', '456', '4')
Note that the accounts/information is sorted by the 1st Renewal Date where Nth Renewal = '1'
My objective is to add a column that tells me whether John switched accounts or owned two
accounts at the same time.
The logic compares the Renewal Date for each Account. If the 1st Renewal Date of the
account <= Last Renewal Date of the previous account, then it is an Overlap because it means the
customer owned 2 accounts at the same time. However, if the 1st Renewal Date of the
account > Last Renewal Date of the previous account, then it is a Switch because the customer
simply switched accounts. I implemented this logic with the following code which produced the following sample of my results:
WITH CTE AS (
SELECT [Renewal Date]
,[Customer]
,[Account]
,[Nth Renewal]
,MIN([Renewal Date]) OVER (PARTITION BY [Account], [Customer]) AS [First Date]
FROM [#FirstLastAnniv]
)
SELECT [Renewal Date]
,[Customer]
,[Account]
,[Nth Renewal]
,CASE WHEN [Customer] = LAG([Customer]) OVER (ORDER BY [Customer], [Renewal Date]) AND
[Nth Renewal] = '1'
THEN CASE WHEN [Renewal Date] > LAG([Renewal Date]) OVER (ORDER BY [Customer], [First Date], [Account], [Renewal Date])
THEN 'Switch'
WHEN [Renewal Date] <= LAG([Renewal Date]) OVER (ORDER BY [Customer], [First Date], [Account], [Renewal Date])
THEN 'Overlap'
END AS [Switch/Overlap]
FROM CTE
ORDER BY [Customer], [First Date], [Account], [Renewal Date]
Renewal Date Customer Account Nth Renewal Switch/Overlap
2017-09-29 John 123 1 NULL
2022-01-31 John 123 6 NULL
2017-10-31 John 789 1 Overlap
2019-01-31 John 789 3 NULL
2019-03-29 John 456 1 Switch
2022-03-31 John 456 4 NULL
So, the results show that John still owned Account = '123' when he opened Account = '789' so
it was an Overlap. But when he opened Account = '456', he had already closed Account = '789' so that's why it says it's a Switch.
However, I realize this this logic is incomplete because I cannot just compare the first Renewal Date with last Renewal Date of the previous account only. It should be with the last Renewal Date of ALL PREVIOUS ACCOUNTS. In this example, even though John dropped Account = '789', he still owned Account = '123' when he opened Account = '456', so it should be an Overlap, not a Switch. But I don't know how implement this in SQL.
Any ideas?
Thank you.
CodePudding user response：
You may try the following using EXISTS operator:
The EXISTS operator is a logical operator that allows you to check whether a subquery returns any row. The EXISTS operator returns TRUE if the subquery returns one or more rows.
WITH CTE AS
(
SELECT [Customer], [Account], MIN([Renewal Date]) FirstAction, MAX([Renewal Date]) LastAction
FROM #Table
GROUP BY [Customer], [Account]
)
SELECT T.[Renewal Date], T.[Customer], T.[Account], T.[Nth Renewal],
CASE
WHEN T.[Nth Renewal] = 1 THEN
CASE
WHEN NOT EXISTS(SELECT 1 FROM #Table D
WHERE D.[Customer] = T.[Customer] AND
D.[Renewal Date] < T.[Renewal Date]
)
THEN '1ST ACCOUNT'
ELSE
CASE
WHEN EXISTS (SELECT 1 FROM CTE D
WHERE D.[Customer] = T.[Customer]
AND D.FirstAction <= T.[Renewal Date]
AND D.LastAction >= T.[Renewal Date]
AND D.[Account] <> T.[Account]
)
THEN 'OVERLAP' ELSE 'SWITCH'
END
END
ELSE 'RENEWAL'
END AS [Switch/Overlap]
FROM #Table T
See demo from db<>fiddle.
To explain how this works:
EXISTSworks like a for loop for each row of the outer query, that means for each row in the table the exists checks the specified condition for each row in the subquery.The CTE is used to find the first and last renewal dates for an account, the overlapping occurs whenever a new account is opened within that period.
WHEN T.[Nth Renewal] = 1used to check if this is a new account or renewal of an existing account. If this condition is met, then:- If there is no account older than the current account this will be the first account for a customer, that's done by the following condition:
WHEN NOT EXISTS(SELECT 1 FROM #Table D WHERE D.[Customer] = T.[Customer] AND D.[Renewal Date] < T.[Renewal Date] ) THEN '1ST ACCOUNT' - Else (not the first account), check if the date of this account is overlapped with other accounts, by comparing the date of this account with every other account date, that's done by the following condition:
WHEN EXISTS (SELECT 1 FROM CTE D WHERE D.[Customer] = T.[Customer] AND D.FirstAction <= T.[Renewal Date] AND D.LastAction >= T.[Renewal Date] AND D.[Account] <> T.[Account] ) THEN 'OVERLAP' ELSE 'SWITCH'
- If there is no account older than the current account this will be the first account for a customer, that's done by the following condition:
CodePudding user response：
with AccountDurations as (
select Customer, Account,
min([Renewal Date]) as StartDate, max([Renewal Date]) as EndDate,
row_number() over (partition by Customer order by min([Renewal Date])) as rn,
row_number() over (partition by Customer, Account
order by min([Renewal Date])) as an
from #Table T
group by Customer, Account
)
select t.*,
case when an = 1 then
case when rn = 1 then 'New'
when flag = 1 then 'Overlap'
when flag = 0 then 'Switch' end
else '' end as [New/Overlap/Switch]
from #Table t left outer join AccountDurations a
on a.Customer = t.Customer and a.Account = t.Account and t.[Nth Renewal] = 1
outer apply (
select case when min(a2.Customer) is not null then 1 else 0 end
from AccountDurations a2
where a2.Customer = a.Customer and a2.Account <> a.Account
and a.StartDate <= a2.EndDate and a.EndDate >= a2.StartDate
) overlapped(flag);