计算两个日期之间缺少日期范围和重叠的日期范围 [英] Calculate missing date ranges and overlapping date ranges between two dates

问题描述

我有日期（DD / MM / YYYY）匹配我的数据库事件，下面的一组：

I have the following set of dates (dd/MM/yyyy) matching events in my database:

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

的事件具有开始和结束日期（时间并不重要）和空结束日期表示事件仍在进行中。

The events have a start and end date (times don't matter) and a NULL endDate means that the event is still in progress.

我想确定的是日期两个任意日期，那里有一个）任何情况之间的范围，二）事件重叠。

What I would like to determine is the ranges of dates between two arbitrary dates where there was a) no event and b) the events were overlapping.

所以对于01/04/2009输入的日期范围 - 30/06/2009我希望有以下结果：

So for an input date range of 01/04/2009 - 30/06/2009 I would expect to have the following results:

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

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

Note that the two adjacent overlap ranges would be acceptable as one result.

任何人都可以请帮我用SQL的算法来生成这个结果集？

Can anyone please help me with a SQL algorithm to generate this result set?

编辑：目标平台数据库是SQL Server 2005中的日期记录为二○○九年十月五日零时○○分00秒，这意味着该事件结束2009年10月5日00:00:00 10之间的某个时间/二千零九分之五23:59:59。的同样是如此的开始日期。输入日期范围，因此也可以理解为01/04/2009零时零零分00秒 - 30/06/2009 23时59分59秒

The target platform database is SQL Server 2005. The dates are recorded as 10/05/2009 00:00:00, meaning that the event ended some time between 10/5/2009 00:00:00 and 10/5/2009 23:59:59. The same is true for the start dates. The input date range therefore also can be read as 01/04/2009 00:00:00 - 30/06/2009 23:59:59.

推荐答案

这是函数的一个小的变化，以扁平化的 SQL服务器相交的时间跨度：

It's a little variation of the function to flatten intersecting timespans in SQL Server:

• 扁平化时间跨度：SQL服务器

这是当 SQL Server游标为基础的方式更快的罕见病例之一，基于集合的一种：

It's one of the rare cases when cursor-based approach in SQL Server is faster the a set-based one:

---

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

此功能的COM presses每一组连续的相交的范围为一个范围，并返回范围以及以下的间隙。的

This function compresses each contiguous set of intersecting ranges into one range, and returns both the range and the following gap.

这篇关于计算两个日期之间缺少日期范围和重叠的日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！