重叠日期范围 - 仅识别重叠 [英] Overlapping Date Ranges - Identifying Only the Overlap
问题描述
我已经看到很多解决方案来识别日期范围重叠的记录,以及合并重叠范围的其他示例.
I've seen a lot of solutions to identify records where date ranges overlap, and still other examples of merging overlapping ranges.
但是,我对显示仅发生重叠的范围的结果感兴趣.事实上,我有 3 个 ProductID(并且只有 3 个将永远存在)并且我正在尝试为每个客户找到他们拥有所有三个的日期范围.
However I am interested in results that show the range where ONLY the overlap occurs. In fact, I have 3 ProductIDs (and only 3 will ever exist) and I'm trying to find the date range for each customer of when they had ALL THREE.
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
===================================================*/
推荐答案
答案如下:
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
上不同产品的数量.您需要三种不同的产品,这就是 sharing
子句的作用.
This is using a self-join to count the number of different products on each eff_dt
. You want three distinct products, so that is what the having
clause is doing.
有三种不同的产品,直到其中一种结束.这将是生效日期之后的第一个 end_dt
-- 由 min(end_dt)
计算.
There are three distinct products until one of them ends. That would be the first end_dt
after the effective date -- which is calculated by the min(end_dt)
.
这篇关于重叠日期范围 - 仅识别重叠的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!