获取没有指定子项的父(作业)的SQL查询(状态)

需要帮助来编写涉及表的同一字段上的多个过滤器的SQL查询.

我有2个表,如下所示.

Job 表:

ID    JobId    Name     StartTime              FinishTime
01    001      A        2105:12:10 14:00:00    2105:12:10 14:00:10
02    002      A        2105:12:10 14:00:00    2105:12:10 14:00:00
03    003      A        2105:12:10 14:00:00    2105:12:10 14:00:00
04    004      A        2105:12:10 14:00:00    2105:12:10 14:00:00

和

Status 表:

ID    Status                Timestamp                JobId
01    Started               2105:12:10 14:00:00      001
02    Step_1_Started        2105:12:10 14:00:00      001
03    Step_1_Finished       2105:12:10 14:00:05      001
04    Step_2_Started        2105:12:10 14:00:05      001
05    Step_2_Finished       2105:12:10 14:00:10      001
06    Finished              2105:12:10 14:00:10      001
........................................................
07    Started               2105:12:10 14:00:00      002
08    Step_1_Started        2105:12:10 14:00:00      002
09    Step_1_Failed         2105:12:10 14:00:02      002
........................................................
10    Started               2105:12:10 14:00:00      003
11    Step_1_Started        2105:12:10 14:00:00      003
12    Step_1_Failed         2105:12:10 14:00:02      003
13    Step_1_Canceled       2105:12:10 14:00:04      003
........................................................
14    Started               2105:12:10 14:00:00      004
15    Step_1_Started        2105:12:10 14:00:00      004

从这两个表中,我必须查询状态为FINISHED,CANCELED,FAILED和ACTIVE的作业

完成:工作状态为"已完成".

取消:状态为'%已取消'但未取消('已完成')的作业.

FAILED:状态为'%Failed'但没有('%Cancelled'或'Finished')的作业.

活动:状态为"已启动"但未启用的作业("%失败"或"%已取消"或"已完成").

我有以下SQL查询,Finished这工作正常

SELECT 
    j.jobid 
FROM 
    Job j 
JOIN  
    status js ON j.jobid = js.jobid 
WHERE
    j.startTime >= '2015:12:10' 
    AND j.startTtime < '2015:12:20' 
    AND js.status = 'Finished';

需要其他查询的帮助.

预期产量:

FINISHED: 001
CANCELED: 003
FAILED:   002
Active:   004

提前致谢.