比较重叠日期

Question

这是我在这里提出的几个问题的后续，但更多的信息使事情变得更加复杂。

我有客户名单，他们拥有的帐户，以及帐户被更新的日期。Nth Renewal表示第n次更新帐户.因此，如果Nth Renewal = '1'，这是第一次更新/打开帐户。如果Nth Renewal =n不等于1，那么这是第n次也是最后一次更新帐户.

下面是一个复杂客户的例子：

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

请注意，帐户/信息按第一个Renewal Date排序，其中Nth Renewal = '1‘

我的目标是添加一篇专栏，告诉我John 是同时切换帐户还是同时拥有两个帐户。

逻辑比较每个Renewal Date的Account。如果帐户的第一个Renewal Date是前一个帐户的最后一个<=，那么它是一个重叠，因为它意味着客户同时拥有两个帐户。但是，如果帐户的第一个Renewal Date >上一个帐户的最后一个Renewal Date，那么它就是一个开关，因为客户只是简单地切换了帐户。我用下面的代码实现了这个逻辑，它产生了下面的结果示例：

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

因此，结果表明，约翰在打开Account =‘Account = '789’时仍然拥有Overlap = '123‘，所以这是一个Overlap。但是当他打开Account = '456‘时，他已经关闭了Account = '789’，所以它说它是一个Switch。

但是，我意识到这个逻辑是不完整的，因为我不能仅仅将第一个Renewal Date与前一个帐户的最后一个Renewal Date进行比较。它应该与最后一个Renewal Date of 所有以前的帐户。在这个例子中，即使约翰放弃了Account = '789'，当他打开Account = '456‘时，他仍然拥有Account = '123’，所以应该是Overlap，而不是Switch。但我不知道如何在SQL中实现这一点。

有什么想法吗？

谢谢。

Answer 1

您可以使用EXISTS操作符尝试以下操作：

现有运算符是一个逻辑运算符，允许您检查子查询是否返回任何行。如果子查询返回一行或多行，则存在运算符返回TRUE。

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

参见db<>fiddle的演示。

要解释这是如何工作的：

• EXISTS的工作方式类似于外部查询的每一行的for循环，这意味着对于表中的每一行，存在都会检查子查询中每一行的指定条件。
• CTE用于查找帐户的第一次和最后一次更新日期，每当在此期间内开设新帐户时，就会发生重叠。
• WHEN T.[Nth Renewal] = 1用于检查这是一个新帐户还是更新一个现有帐户。如果满足这一条件，那么：

- 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'

Answer 2

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