计算两个日期之间缺少的日期范围和重叠的日期范围

Question

我的数据库中有以下一组匹配日期的日期（dd / MM / yyyy）：

eventId startDate endDate
1 02/05/2009 10/05/2009
2 08/05/2009 12/05/2009
3 10/05/2009 12/05/2009
4 21/05/2009 21/05/2009
5 25/05/2009 NULL
6 01/06/2009 03/06/2009

事件具有开始和结束日期（时间无关紧要），并且endDate为NULL表示事件仍在进行中。

我想确定的是两个任意日期之间的日期范围，其中a）没有事件，b）事件重叠。

因此，对于输入日期范围01/04/2009-30/06/2009，我希望得到以下结果：

no event: 01/04/2009 - 01/05/2009
overlap : 08/05/2009 - 10/05/2009
overlap : 10/05/2009 - 12/05/2009
no event: 13/05/2009 - 20/05/2009
no event: 22/05/2009 - 24/05/2009
overlap : 01/06/2009 - 03/06/2009

注意，作为结果，两个相邻的重叠范围是可以接受的。

谁能用SQL算法帮助我生成此结果集吗？

编辑：目标平台数据库是SQL Server2005。日期记录为10/05/2009 00:00:00，这意味着事件在10/5/2009
00:00:00和10/5 /之间的某个时间结束2009 23:59:59。开始日期也是如此。因此，输入日期范围也可以读取为01/04/2009
00:00:00-30/06/2009 23:59:59。

Answer 1

展平相交时间跨度的功能略有变化SQL Server：

• 展平时间跨度：SQL Server

这是一种罕见的情况，其中基于游标的方法比基于集的方法SQL Server更快：

CREATE FUNCTION mytable(@p_from DATETIME, @p_till DATETIME)
RETURNS @t TABLE
        (
        q_type VARCHAR(20) NOT NULL,
        q_start DATETIME NOT NULL,
        q_end DATETIME NOT NULL
        )
AS
BEGIN
        DECLARE @qs DATETIME
        DECLARE @qe DATETIME
        DECLARE @ms DATETIME
        DECLARE @me DATETIME
        DECLARE cr_span CURSOR FAST_FORWARD
        FOR
        SELECT  startDate, endDate
        FROM    mytable
        WHERE   startDate BETWEEN @p_from AND @p_till
        ORDER BY
                startDate 
        OPEN    cr_span
        FETCH   NEXT
        FROM    cr_span
        INTO    @qs, @qe
        SET @ms = @qs
        SET @me = @qe
        WHILE @@FETCH_STATUS = 0
        BEGIN
                FETCH   NEXT
                FROM    cr_span
                INTO    @qs, @qe
                IF @qs > @me
                BEGIN
                        INSERT
                        INTO    @t
                        VALUES ('overlap', @ms, @me)
                        INSERT
                        INTO    @t
                        VALUES ('gap', @me, @qs)
                        SET @ms = @qs
                END
                SET @me = CASE WHEN @qe > @me THEN @qe ELSE @me END
        END
        IF @ms IS NOT NULL
        BEGIN
                INSERT
                INTO    @t
                VALUES  (@ms, @me)
        END
        CLOSE   cr_span
        RETURN
END
GO

此函数将每个连续范围的相交范围压缩为一个范围，并返回范围和后面的间隙。