SharePoint Alerts - Failing Alerts Single DB Query

--Scheduled Alerts Query
-- Tom Sheffrey 1/3/2013
-- Find alerts that should have fired
-- Check for missing Join values

-- DB and Server Info
select DB_NAME() as DatabaseName
    ,getdate() as ServerTimeStamp
    ,LockedBy
    ,dateadd(hour, -6,LockedTime) as LockedTimeCST from timerlock;

-- Failing alert summary
Select dbo.Webs.FullUrl
        ,count(CASE dbo.SchedSubscriptions.NotifyFreq
                WHEN 1 THEN 1
            END) as Daily
        ,count(CASE dbo.SchedSubscriptions.NotifyFreq
                WHEN 2 THEN 2
            END) as Weekly
        ,SUM(CASE dbo.SchedSubscriptions.UserEmail
                WHEN N'' THEN 1
                ELSE 0
                END) MissingEmail
        ,MAX(DATEDIFF(hour,EventLog.TimeLastModified,SchedSubscriptions.NotifyTimeUTC)) as OldestHours
FROM         dbo.EventCache with (nolock) INNER JOIN
                      dbo.EventLog with (nolock) ON dbo.EventCache.Id = dbo.EventLog.Id INNER JOIN
                      dbo.EventSubsMatches with (nolock) ON dbo.EventCache.Id = dbo.EventSubsMatches.EventId INNER JOIN
                      dbo.SchedSubscriptions with (nolock) ON dbo.EventSubsMatches.SubId = dbo.SchedSubscriptions.Id INNER JOIN
                      dbo.Webs with (nolock) ON dbo.EventCache.WebId = dbo.Webs.Id
WHERE (DATEDIFF(Hour,EventLog.eventtime,SchedSubscriptions.NotifyTimeUTC) > (24 * (CASE SchedSubscriptions.notifyfreq when 1 then 1 else 7 END)))
GROUP BY dbo.SchedSubscriptions.NotifyFreq, dbo.Webs.FullUrl
ORDER BY dbo.SchedSubscriptions.NotifyFreq, dbo.Webs.FullUrl;

-- Entries with NULL subscriptions
select COUNT(eventsubsmatches.eventid) as NullMatches
            from EventSubsMatches left outer join
                SchedSubscriptions on EventSubsMatches.SubId = SchedSubscriptions.Id
            where SchedSubscriptions.Id is NULL;
-- Detail records with EventType and Filter analysis
select w.FullUrl as WebUrl
    ,ec.SiteId
    ,esm.eventid
    ,esm.subid as SubscriptionID
    ,ec.ItemName
    ,ec.ItemFullUrl
    ,el.eventtime as EventTime
    ,ss.NotifyTime as SubscriptionNotifyTime
    ,w.TimeZone
    ,DATEDIFF(hour,ss.NotifyTime,ss.NotifyTimeUTC) as OffsetHour
    ,DATEDIFF(hour,el.eventtime,ss.NotifyTimeUTC) as Hours
    ,CASE ss.NotifyFreq
        when 1 THEN 'Daily'
        when 2 THEN 'Weekly'
    END as Frequency
    ,ss.UserEmail
    ,CASE ss.AlertType
        when 0 then 'List'
        when 1 then 'Item'
        else CAST(ss.AlertType as varchar(8))
    END as AlertType
    ,CASE ss.EventType
        when -1 THEN 'All Changes'
        when 1 then 'New items are added'
        when 2 then 'Existing items are modified'
        when 4 then 'Items are deleted'
        when 4080 then 'Web discussion updates'
        else 'Unknown'
    END as 'Change Type'
    ,CASE ss.Filter
        when N'' then 'Anything Changes'
        when '%Editor/New%' then 'Someone else changes a document'
        when '%Author%' then 'Someone else changes a document owned by me'
        when '%Editor/Old%' then 'Someone else changes a document modified by me'
        else 'Unknown'
    END as 'Send alerts for these changes'
FROM dbo.EventSubsMatches AS esm LEFT OUTER JOIN
     dbo.EventCache AS ec ON esm.EventId = ec.Id LEFT OUTER JOIN
     dbo.SchedSubscriptions AS ss ON esm.SubId = ss.Id LEFT OUTER JOIN
     dbo.EventLog AS el ON esm.EventId = el.Id LEFT OUTER JOIN
     dbo.Webs AS w ON ec.WebId = w.Id
WHERE (DATEDIFF(hour,el.eventtime,ss.NotifyTimeUTC) > (24 * (CASE ss.notifyfreq when 1 then 1.0 else 7.0 END)))
    or WebUrl is NULL
order by hours desc;