SQL Server: Maximum Date and Internal Connection

Question

I have two tables, one of them is a task list. Another containing historical significance for these tasks.

I need to create a list of the last event (and its description) for each check, until its Date_Executed less than the current datetime minus Timeframe ( Timeframe as a clock within the task must be completed, formatted for use in DATEADD ). But only if they have 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 that I have so far:

 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 

None of them get me what I need, I really could use some help.

Answer 1

Since you are a SQL Server that supports Common Table Expression and Window Function . Try it,

 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 RDBMSs that support Window Functions . Alternatively, use the query below, which works on most DBMSs.

 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