排除当天的删除\添加，同时保留真实的开始和结束日期

Question

我们有一个表，其中包含产品订阅的状态更新。订阅开始时，一条记录会插入到表中，订阅结束时，该记录会更新为结束日期。我们的一个系统（不知道是哪一个）有时会执行“同一天删除\添加”，结束订阅然后再次开始（创建新记录）。因此，即使没有真正发生任何变化，同一个订户 ID 也会附加到多个记录。

示例数据如下：

recID subID   start           end        prodtype
1     19    01/11/2001  01/15/2001    A
2     19    01/15/2001  01/16/2001    A
3     19    01/16/2001  01/20/2001    A
4     19    01/30/2001  01/31/2001    A

此人于 1/11 开始，于 1/20 结束。记录 2 和 3 是由系统放入的（同一天添加，但不是真的）。记录4是19先生后来开始的另一次订阅。

我有一些代码尝试仅解析每个不同订阅的第一个（真实）记录，但如果不使用 max() 并按订阅者分组，它就无法找到真正的结束日期。这当然会显示两个订阅，1/11 - 1/31 和 1/30 - 1/31，这是错误的。

我正在绞尽脑汁地试图将此模式解析为如下两条记录：

subID   start           end        prodtype
 19    01/11/2001   01/20/2001    A
 19    01/30/2001   01/31/2001    A

这是在 Teradata 中，但我相信它只是 ANSI SQL。

Answer 1

我相信这是 ANSI SQL，但我只在 SQL Server 上测试过它。

基本上，查询能够找到彼此独立的真实开始日期和真实结束日期。然后，要关联开始日期和结束日期，请将开始日期与大于开始日期的结束日期关联...然后显示最小的结束日期。

SELECT
    startDates.subId,
    startDates.startDate,
    MIN(endDates.endDate) AS endDate,
    startDates.prodType
FROM
(
    SELECT
        recID, subID, startDate, prodType
    FROM yourTable s1
    WHERE NOT EXISTS (
        SELECT 1
        FROM yourTable s2
        WHERE 
            s1.startDate = s2.endDate
            AND s1.subId = s2.subId
    )
) startDates JOIN
(
    SELECT
        recID, subID, endDate, prodType
    FROM yourTable s1
    WHERE NOT EXISTS (
        SELECT 1
        FROM yourTable s2
        WHERE 
            s1.endDate = s2.startDate
            AND s1.subId = s2.subId
    )
) endDates ON
    startDates.subID = endDates.subID 
    AND startDates.startDate < endDates.endDate
GROUP BY
    startDates.subId,
    startDates.startDate,
    startDates.prodType

这是正在执行的查询...

Answer 2

您可以使用如下代码查找具有实际结束日期的所有记录：

select t1.*
from myTable t1 left outer join myTable t2 on
t1.SubID = t2.SubID and  
t1.end = t2.start and t2.start is null

当然，您可以以类似的方式查找开始记录。然后也许你可以将它们修补在一起。

也就是说，有时需要放弃对 select 语句进行所有处理，而使用存储过程，或者将所有数据带回客户端并在那里进行处理。