重叠的日期范围-仅识别重叠

Question

我已经看到了许多解决方案来识别日期范围重叠的记录，还有其他一些合并重叠范围的示例。

但是，我对显示仅重叠发生范围的结果感兴趣。实际上，我有3个ProductID（并且只有3个将存在），并且我试图为每个客户查找所有3个日期的日期范围。

SET NOCOUNT ON;

CREATE TABLE #tmp
(
   CustomerID integer
  ,ProductID varchar(12)
  ,Eff_Dt DATE
  ,End_Dt DATE
);

-- Customer 1000: Expecting results to show 2 rows:   1/1 - 1/5   and    1/10 - 1/15
INSERT INTO #tmp VALUES (1000,'PRODUCT_A','01-01-2013' ,'01-31-2013' );
INSERT INTO #tmp VALUES (1000,'PRODUCT_B','01-01-2013' ,'01-05-2013' );
INSERT INTO #tmp VALUES (1000,'PRODUCT_B','01-10-2013' ,'01-15-2013' );
INSERT INTO #tmp VALUES (1000,'PRODUCT_C','01-01-2013' ,'01-31-2013' );

-- Customer 2000: Expecting results to show 1 row:    1/19 - 1/31
INSERT INTO #tmp VALUES (2000,'PRODUCT_A','01-01-2013' ,'01-31-2013' );
INSERT INTO #tmp VALUES (2000,'PRODUCT_B','01-01-2013' ,'01-31-2013' );
INSERT INTO #tmp VALUES (2000,'PRODUCT_C','01-19-2013' ,'01-31-2013' );

-- Customer 3000: Expecting results to show no rows (or nulls)
INSERT INTO #tmp VALUES (3000,'PRODUCT_A','01-01-2013' ,'01-10-2013' );
INSERT INTO #tmp VALUES (3000,'PRODUCT_A','01-16-2013' ,'01-31-2013' );
INSERT INTO #tmp VALUES (3000,'PRODUCT_B','01-01-2013' ,'01-12-2013' );
INSERT INTO #tmp VALUES (3000,'PRODUCT_C','01-15-2013' ,'01-31-2013' );

-- Customer 4000: Expecting results to show 1 row:    1/15 - 1/23
INSERT INTO #tmp VALUES (4000,'PRODUCT_A','01-15-2013' ,'01-31-2013' );
INSERT INTO #tmp VALUES (4000,'PRODUCT_B','01-01-2013' ,'01-31-2013' );
INSERT INTO #tmp VALUES (4000,'PRODUCT_C','01-01-2013' ,'01-23-2013' );

-- Customer 5000: Expecting results to show 0 rows
INSERT INTO #tmp VALUES (5000,'PRODUCT_A','01-17-2013' ,'01-31-2013' );
INSERT INTO #tmp VALUES (5000,'PRODUCT_B','01-01-2013' ,'01-10-2013' );
INSERT INTO #tmp VALUES (5000,'PRODUCT_C','01-07-2013' ,'01-19-2013' );

-- Customer 6000: Expecting results to show 3 rows:    1/11 - 1/12   1/17 - 1/22    1/26 - 1/27
INSERT INTO #tmp VALUES (6000,'PRODUCT_A','01-01-2013' ,'01-04-2013' );
INSERT INTO #tmp VALUES (6000,'PRODUCT_A','01-09-2013' ,'01-12-2013' );
INSERT INTO #tmp VALUES (6000,'PRODUCT_A','01-17-2013' ,'01-22-2013' );
INSERT INTO #tmp VALUES (6000,'PRODUCT_A','01-26-2013' ,'01-31-2013' );
INSERT INTO #tmp VALUES (6000,'PRODUCT_B','01-04-2013' ,'01-28-2013' );
INSERT INTO #tmp VALUES (6000,'PRODUCT_C','01-11-2013' ,'01-27-2013' );

SET NOCOUNT OFF;
/* ======   EXPECTED RESULTS  =======================

CustomerID    EFF_DT        END_DT
1000          1/1/2013      1/5/2013
1000          1/10/2013     1/15/2013
2000          1/19/2013     1/31/2013
4000          1/15/2013     1/23/2013
6000          1/11/2013     1/12/2013
6000          1/17/2013     1/22/2013
6000          1/26/2013     1/27/2013

===================================================*/

Answer 1

答案如下：

select t.customerid, t.eff_dt, count(distinct t2.productId),
       MIN(t2.end_dt) as end_dt
from #tmp t join
     #tmp t2
     on t.CustomerID = t2.CustomerID and
        t.Eff_Dt between t2.Eff_Dt and t2.End_Dt
group by t.CustomerID, t.eff_dt
having count(distinct t2.productId) = 3

这是使用自连接来计算每个产品上不同产品的数量eff_dt。您需要三种截然不同的产品，因此该having子句正在执行此操作。

有三种截然不同的产品，直到其中一种结束。这将是end_dt生效日期之后的第一个日期-由日期计算min(end_dt)。