Re: [SQL ] 請問如何計算重複日期區間的總實際天數

[jengting (~~)]

請參考看看 ~~

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
推 Mutex: 感謝感謝 結果是對的 還在研究後半段的語法 再次感謝