Comparing Overlapping Dates

Question

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.

userGpjR · Answer

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:

EXISTS works 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] = 1 used 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'
```

shawnt00 · Answer

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);

https://dbfiddle.uk/EP-3UfO6

Comparing Overlapping Dates

2 Answers