SQL Server : max date and inner join

Question

I have two tables, one is a list of tasks. The other containing historical values for those tasks.

I need to generate a list of the latest event (and its description) for each check, as long as long as its Date_Executed is less than the current datetime minus the Timeframe (TimeFrame being hours within the task has to be done, formatted for use in DATEADD). But only if they have an active = 1.

Table: checks

Check_id  description  TimeFrame active
1         Task One     -24       0
2         Task Two     -24       0
3         Task Forty   -48       1
4         Task Somehin -128      1

Table: events

Event_id  Check_id   Comment     Date_Executed             User_Executed
1         1          NULL        2012-09-18 16:10:44.917   admin
2         1          NULL        2012-09-25 11:39:01.000   jeff
3         4          Failed      2012-09-25 13:20:09.930   steve
4         4          Half failed 2012-09-25 13:05:09.953   marsha
5         2          NULL        2012-09-25 14:02:24.000   marsha
6         3          NULL        2012-09-18 16:10:55.023   marsha

The best solutions I have so far is:

SELECT 
    a.[Date_Executed]
    a.[Check_id], 
    a.[Comments],
    b.[frequency], 
    b.[Check_id],
    b.[description]     
FROM 
    [checksdb].[dbo].events as a, 
    [checksdb].[dbo].checks as b
where 
    b.active = 1
    and a.[Date_Executed] < = dateadd(HOUR,b.[frequency],GETDATE())
    and a.Check_id = b.Check_id
order by Check_id, priority

and

select MAX(date_Executed), Task_id from daily_check_events group by Task_id

Neither of which gets me what I need, I could really use some help.

Answer 1

Since you are SQL Server which supports Common Table Expression and Window Function. Try this,

WITH latestEvents
AS
(
  SELECT  Event_id, Check_id, [Comment], Date_Executed, User_Executed,
          ROW_NUMBER() OVER(PARTITION BY Check_ID ORDER BY DATE_Executed DESC) 
              AS RowNum
  FROM    events
)
SELECT  a.[Check_id], a.[description],
        b.[Date_Executed], b.[Comment]
FROM    checks a
        INNER JOIN latestEvents b
            on a.check_ID = b.check_ID
WHERE   b.RowNum = 1 AND
        a.active = 1
        -- other conditions here

SQLFiddle Demo

The above query will only work on RDBMS that supports Window Functions. Alternatively, use the query below that works on most RDBMS

SELECT  a.Check_id, a.description,
        c.Date_Executed, c.Comment
FROM    checks a
        INNER JOIN
        (
          SELECT check_id, MAX(Date_Executed) maxExecuted
          FROM   events
          GROUP BY check_ID
        ) b ON a.check_ID = b.check_ID
        INNER JOIN events c
          ON c.check_ID = b.check_ID AND
             c.date_executed = b.maxExecuted
WHERE   a.active = 1

SQLFiddle Demo