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.
我需要为每次检查生成一个最新事件(及其描述)列表，只要它执行的date_execution小于当前datetime减去Timeframe(必须完成任务中的工时，格式化后用于DATEADD)。但前提是它们的有源= 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.
这两种方法都不能满足我的需要，我真的需要一些帮助。
1 个解决方案
#1
8
Since you are SQL Server which supports Common Table Expression and Window Function. Try this,
因为您是支持公共表表达式和窗口函数的SQL Server。试试这个,
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
上面的查询只适用于支持窗口函数的RDBMS。或者，使用下面在大多数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
#1
8
Since you are SQL Server which supports Common Table Expression and Window Function. Try this,
因为您是支持公共表表达式和窗口函数的SQL Server。试试这个,
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
上面的查询只适用于支持窗口函数的RDBMS。或者，使用下面在大多数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