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

我的数据库中有以下一组匹配日期的日期(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，我希望得到以下结果:

无事件:2009年1月4日至2009年5月5日
重叠:2009年8月5日-2009年10月5日
重叠:2009年10月5日-2009年12月5日
无事件:2009年5月13日-2009年5月20日
无事件:22/05/2009-24/05/2009
重叠:2009年1月6日-2009年3月6日

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

任何人都可以用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。

最佳答案

在SQL Server中展平相交时间跨度的功能略有不同:

Flattening timespans: 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

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

关于sql - 计算两个日期之间缺少的日期范围和重叠的日期范围，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/1025688/