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

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

我在我的数据库中有以下日期(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

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

事件具有开始和结束日期(时间无关紧要),NULL endDate表示事件仍在进行中。

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.

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

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

因此,对于输入日期范围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

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

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

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

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

EDIT: 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 Server 2005.日期记录为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。

3 个解决方案

#1

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

这是在SQL Server中展平相交时间跨度的函数的一点变化:

Flattening timespans: SQL Server

展平时间跨度:SQL Server

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

在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

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

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

#2

Without really understanding what problem you're trying to solve, here's my solution to some problem off the top of my head:

如果没有真正理解你想要解决的问题,这就是我解决一些问题的方法:

Create table function (UDF) "all-dates" that would return all dates in a year.

创建表函数(UDF)“all-dates”,它将返回一年中的所有日期。

Convert your events to separate dates (one event row would become as many rows as there are days in it) by inner-joining events to the all-dates where the date is between event's start and end dates... Retain original eventId.

通过将事件内部加入到事件的开始日期和结束日期之间日期的所有日期,将您的事件转换为单独的日期(一个事件行将变为与其中的天数一样多的行)...保留原始eventId。

Do an outer join of event-dates with all-dates (again) to find the gaps or misses.

事件日期的外部联接与所有日期(再次)进行,以找到差距或未命中。

Join event-dates with themselves on where dates are same but eventId is not to find overlaps.

将事件日期与自己一起加入日期相同但eventId不会找到重叠的日期。

#3

my poor, with postgresql, you could do that simply :

我的穷人,使用postgresql,你可以这样做:

(start1, end1) OVERLAPS (start2, end2) (start1, length1) OVERLAPS (start2, length2)

(start1,end1)OVERLAPS(start2,end2)(start1,length1)OVERLAPS(start2,length2)

This expression yields true when two time periods (defined by their endpoints) overlap, false when they do not overlap. The endpoints can be specified as pairs of dates, times, or time stamps; or as a date, time, or time stamp followed by an interval.

当两个时间段(由其端点定义)重叠时,此表达式产生true,当它们不重叠时为false。端点可以指定为日期,时间或时间戳对;或作为日期,时间或时间戳后跟一个间隔。

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'); Result: true SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'); Result: false

选择(日期'2001-02-16',日期'2001-12-21')重叠(日期'2001-10-30',日期'2002-10-30');结果:真正的SELECT(DATE'2001-02-16',INTERVAL'100天')重叠(日期'2001-10-30',日期'2002-10-30');结果:错误

but under sql server, i don't know... sorry

但在sql server下,我不知道......抱歉

#1