Try this

WITH CTE1 AS (

SELECT Filename,SuccessFailIndicator,checkdate,

ROW_NUMBER() OVER(PARTITION BY Filename ORDER BY checkdate DESC) AS rn

FROM TABLE2)

SELECT t1.jobname

FROM TABLE1 t1

LEFT OUTER JOIN CTE1 c ON c.Filename = t1.Filename

AND c.SuccessFailIndicator = 1

AND c.rn=1

GROUP BY t1.jobname

HAVING COUNT(t1.Filename) = COUNT(c.Filename);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537