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

Question

我需要查找最近 30 天或更长时间没有发生事件之后发生的所有事件。我当前的查询仅找到第一个 30 天的间隔。如果没有 30 天或更长的间隔，则我使用默认日期返回所有行。

我还应该小心，如果事件早于 30 天，则不要返回该事件的单个实例。

鉴于以下事件，最近 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
                   )

Answer 1

试试这个：

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
)

Answer 2

专为您的 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