TSQL-日期重叠-天数

Question

我在SQL Server上有下表：

9999.12.31<1>><1>3<1>

ID	FROM	TO	报价编号
1	202.01.02	1
1	2022.01.02	2022.02.10	2
2	2022.01.05	2022.02.15
3	2022.01.02	9999.12.31
3	2022.0115	2022.0220	2
3	2022.02.03	22022.02.25
4	2022.01.16	2022.02.05
5	2022.01.17	2022.02.13	1
5	2022.02.05	2022.02.13	2

以下内容将(针对每个ID(提取所有不同的日期，构建不重叠的日期范围进行测试，并统计每个范围的报价数量。最后一步是求和和和格式化。

事实上，开始日期是包容性的，结束日期是排他性的，而有时对人类来说是不直观的，实际上在这样的算法中效果很好。

DECLARE @Data TABLE (Id INT, FromDate DATETIME, ToDate DATETIME, OfferNumber INT)
INSERT @Data
VALUES
(1, '2022-01-02', '9999-12-31', 1),
(1, '2022-01-02', '2022-02-10', 2),
(2, '2022-01-05', '2022-02-15', 1),
(3, '2022-01-02', '9999-12-31', 1),
(3, '2022-01-15', '2022-02-20', 2),
(3, '2022-02-03', '2022-02-25', 3),
(4, '2022-01-16', '2022-02-05', 1),
(5, '2022-01-17', '2022-02-13', 1),
(5, '2022-02-05', '2022-02-13', 2)
;
WITH Dates AS ( -- Gather distinct dates
SELECT Id, Date = FromDate FROM @Data
UNION --(distinct)
SELECT Id, Date = ToDate FROM @Data
),
Ranges AS ( --Construct non-overlapping ranges (The ToDate = NULL case will be ignored later)
SELECT ID, FromDate = Date, ToDate = LEAD(Date) OVER(PARTITION BY Id ORDER BY Date)
FROM Dates
),
Counts AS ( -- Calculate days and count offers per date range
SELECT R.Id, R.FromDate,  R.ToDate,
Days = DATEDIFF(DAY, R.FromDate, R.ToDate),
Offers = COUNT(*)
FROM Ranges R
JOIN @Data D ON D.Id = R.Id
AND D.FromDate <= R.FromDate
AND D.ToDate >= R.ToDate
GROUP BY R.Id, R.FromDate,  R.ToDate
)
SELECT Id
,[Days with 1 Offer] = SUM(CASE WHEN Offers = 1 THEN Days ELSE 0 END)
,[Days with 2 Offers] = SUM(CASE WHEN Offers = 2 THEN Days ELSE 0 END)
,[Days with 3 Offers] = SUM(CASE WHEN Offers = 3 THEN Days ELSE 0 END)
FROM Counts
GROUP BY Id

WITH子句引入了公共表表达式(CTE(，它逐步建立中间结果，直到可以做出最终选择。

结果：

有2个优惠的日期

Id	有1个优惠的天数	有3个优惠的日子
1	2913863	39	0
2		41	0	0
3	2913861	24	17
4	20	0
5	19	8	0

查找每个ID的所有日期点。对于每个日期点，查找重叠的数量。

参考查询中的注释

with 
dates as
(
-- get all date points
select ID, theDate = FromDate from offers
union   -- union to exclude any duplicate
select ID, theDate = ToDate from offers
),
cte as
(
select ID         = d.ID, 
Date_Start = d.theDate, 
Date_End   = LEAD(d.theDate) OVER (PARTITION BY ID ORDER BY theDate), 
TheCount   = c.cnt
from   dates d
cross apply
(
-- Count no of overlapping
select cnt         = count(*)
from   offers x
where  x.ID        = d.ID
and    x.FromDate <= d.theDate
and    x.ToDate   >  d.theDate
) c
)
select ID, TheCount, days = sum(datediff(day, Date_Start, Date_End))
from   cte
where  Date_End is not null
group by ID, TheCount
order by ID, TheCount

结果：

ID
1	2913863
1	2		2		3		3	29
3		4	1	20
5	1	19
5	2	8