T-SQL：30天之后的最新事件

I need to find all the events occurring after the most recent gap of 30 days or more without an event. My current query only finds the first gap of 30 days. If there aren't any gaps of 30 days or more then I use a default date to return all the rows.

我需要在没有事件的情况下找到最近30天或更长时间间隔之后发生的所有事件。我当前的查询只发现30天的第一个差距。如果没有30天或更长的间隙,那么我使用默认日期返回所有行。

I should also be careful not to return a single instance of an event if that event is older that 30 days.

如果该事件超过30天,我还应该注意不要返回事件的单个实例。

Given the following events, the most recent event after most recent 30 day gap should be 6/30/2011, but my query returns 4/13/2011

鉴于以下事件,最近30天差距后的最新事件应该是6/30/2011,但我的查询将返回4/13/2011

EventDate    EventType
=========    =========
4/13/2011    1
5/20/2011    1
6/30/2011    1

DECLARE @DefaultDate DATETIME 
SET @DefaultDate = '1/1/2011'

SELECT ISNULL(MAX(EventDate), @DefaultDate) 
FROM Events e 
WHERE 
    e.EventType = 1
    AND NOT EXISTS (SELECT 1 
                    FROM Events
                    WHERE  EventType = 1
                    AND DATEDIFF(dd,EventDate, e.EventDate) ) > 30
                   )

2 个解决方案

#1

Try this:

declare @t table(EventDate datetime)

insert @t(EventDate) values('4/13/2011'), ('5/20/2011'), ('6/30/2011'), ('7/1/2011')

select *
from @t
where EventDate >
(
    select max(t1.EventDate)
    from @t t1
    join @t t2 ON t2.EventDate > t1.EventDate
        and not exists (
            select 1
            from @t t3
            where t3.EventDate < t2.EventDate and t3.EventDate > t1.EventDate
        )
    where datediff(day, t1.EventDate, t2.EventDate) > 30
)

#2

Made to fit your sql. Fixed a misunderstanding

适合你的sql。修正了一个误解

SELECT COALESCE(t1.Eventdate, @DefaultDate) from event t1
RIGHT JOIN
(
SELECT max(EventDate) EventDate FROM event t 
WHERE EventType = 1 AND 
 NOT EXISTS 
(SELECT 1 FROM event WHERE EventType = 1 AND
t.eventdate <= eventdate + 30 and t.eventdate > eventdate)
AND EXISTS (SELECT 1 FROM event WHERE EventType = 1
 AND t.eventdate > eventdate)) t2
 on t1.eventdate >= t2.eventdate