作者jengting (~~)
看板Database
标题Re: [SQL ] 请问如何计算重复日期区间的总实际天数
时间Thu Sep 18 17:18:42 2014
请参考看看 ~~
DECLARE @Temp TABLE (ID char(1),StartDate date , EndDate date)
INSERT INTO @Temp VALUES
('A','20140101','20140115'),
('A','20140114','20140117'),
('B','20140215','20140220'),
('B','20140220','20140225'),
('C','20140301','20140305'),
('C','20140320','20140321')
;
WITH cteStartDate AS
(
SELECT DISTINCT ID, startdate
FROM @Temp AS S1
WHERE NOT EXISTS
(
SELECT * FROM @Temp AS S2
WHERE S2.ID = S1.ID
AND S2.startdate < S1.startdate
AND S2.enddate >= S1.StartDate
)
)
,
cteEndDate AS
(
SELECT DISTINCT ID, enddate
FROM @Temp AS S1
WHERE NOT EXISTS
(
SELECT * FROM @Temp AS S2
WHERE S2.ID = S1.ID
AND S2.enddate > S1.enddate
AND S2.startdate <= S1.enddate)
)
SELECT
T.ID ,
SUM(datediff(dd,startdate,enddate)+1)
FROM
(
SELECT
ID,
startdate,
(
SELECT MIN(enddate)
FROM cteEndDate AS E
WHERE E.ID = S.ID
AND enddate >= startdate
) AS enddate
FROM cteStartDate AS S
) AS T
GROUP BY T.ID
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 118.163.158.7
※ 文章网址: http://webptt.com/cn.aspx?n=bbs/Database/M.1411031924.A.B92.html
1F：推 Mutex: 感谢感谢 结果是对的 还在研究後半段的语法 再次感谢 09/18 20:23