从结果集中获取开始日期和结束日期的 SQL 查询

Question

我正在处理一项要求，原始数据采用以下格式

要求 - 开始日期应该是状态更改为 1 时的日期，结束日期应该是记录状态从 1 更改为任何其他状态后的第一个日期数字。

客户	状态	日期
A123	0	7/2/2021
A123	0	7/15/2021
A123	0	7/22/2021
A123	1	8/18/2021
A123	1	9/8/2021
A123	0	12/1/2021
A123	0	1/21 /2022
A123	1	3/6/2022
A123	1	3/7/2022
A123	0	3/15/2022
B123	1	1/1/2022
B123	0	1/6/2022
C123	1	1/2/2022
C123	2	1/8/2022
C123	0	2022 年 1 月 9 日

预期输出

客户	StartDate	EndDate
A123	8/18/2021	12/1/2021
A123	9/8/2021	12/1/2021
A123	3/6/2022	3/15/2022
A123	3/7/ 2022	2022 年 3 月 15 日
B123	1/1/2022	1/6/2022
C123	1/2/2022	1/8/2022

我试图获取的输出如下，我得到了客户 B123 和 C123 的输出，但没有得到预期的 A123 的输出。

查询说明 - 在查询的第一部分中，我将获取状态 = 1 的所有记录，在下一部分中仅获取状态不等于 1 的记录，并根据客户和客户连接这两个数据集生成的行号。

SELECT A.[Customer],A.StartDate,B.EndDate
    from
    (
         SELECT [Customer],MIN(Date) AS STARTDATE,[Status],RANK() OVER (PARTITION BY [STATUS] ORDER BY Date ASC) AS ROWNUM
         FROM table1
         WHERE [STATUS] = 1
         GROUP BY Customer,Date,[Status]
     ) A
     LEFT JOIN
     (
        SELECT [Customer],MIN(Date) AS ENDDATE,[Status],RANK() OVER (PARTITION BY [STATUS] ORDER BY Date ASC) AS ROWNUM
         FROM table1
         WHERE [STATUS] != 1 
         AND Date>(
                    SELECT MIN(Date) AS STARTDATE
                    FROM table1
                    WHERE [STATUS] = 1
                )
     GROUP BY Customer,Date,[Status]
     ) B 
        ON 
        (
            A.[Customer] = B.[Customer] 
            AND A.RowNum = B.RowNum
        )
     ORDER BY A.Startdate

Answer 1

首先，您列出status = 1的行，然后使用交叉应用获取状态不等于1的相应最小日期

select s.[Customer],
       StartDate = s.[Date],
       EndDate   = e.[Date]
from   Table1 s
       cross apply
       (
           select [Date] = min(e.[Date])
           from   Table1 e
           where  e.[Customer] = s.[Customer]
           and    e.[Date]     > s.[Date]
           and    e.[Status]   <> 1
       ) e
where  s.[Status] = 1 
order by s.[Customer], s.[Date]

Answer 2

这是一种无需自连接即可更有效地执行此操作的方法。

WITH cte01only AS
  ( SELECT *, CASE Status WHEN 1 THEN 1 ELSE 0 END AS Status1 FROM table1 ),
  cteDifference AS
  (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY Customer  ORDER BY Date, Status1)
        - ROW_NUMBER() OVER (PARTITION BY Customer, Status1  ORDER BY Date) AS StatusGroup
    FROM cte01only
  ),
  cteGroup AS
  (
    SELECT   Customer, StatusGroup, Status1, MIN(Date) As StartDate
    FROM cteDifference
    GROUP BY Customer, StatusGroup, Status1
  ),
  cteNextDate AS
  (
    SELECT Customer, StatusGroup, Status1, StartDate,
        LEAD(StartDate, 1, NULL) OVER (PARTITION BY Customer  ORDER BY StatusGroup) AS EndDate
    FROM cteGroup
  )
SELECT Customer, StartDate, EndDate
FROM cteNextDate
WHERE Status1 = 1
ORDER BY Customer, StateDate

这里的关键技巧是第二个 CTE，它使用两个 ROW_NUMBER() 函数的差异，通过连续运行将客户记录（使用 StatusGroup 列）标记到单独的分区中状态为 1 或非 1 的记录。之后可以根据该标签对它们进行分组以获取开始日期，然后使用 LEAD() 函数获取后面组的StartDate 作为当前分组EndDate。

（可能有更紧凑的方式来表达这一点，但我喜欢将每个阶段布局为单独的 CTE。）