請參考看看 ~~
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