Re: [SQL ] 請問如何計算重複日期區間的總實際天數
請參考看看 ~~
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://www.ptt.cc/bbs/Database/M.1411031924.A.B92.html
推
09/18 20:23, , 1F
09/18 20:23, 1F
討論串 (同標題文章)