T-SQL: Most recent event after a 30 day gap

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.

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

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

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
                   )

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
)

Made to fit your sql. Fixed a misunderstanding

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