do you mean this for first scenario?SELECT p.PackageID,MAX(i.Date)FROM Item iINNER JOIN Package pON p.PackageID = i.PackageIDGROUP BY p.PackageIDHAVING SUM(CASE WHEN i.StatusId <> COALESCE(p.StatusId,-1) THEN 1 ELSE 0 END) =0
SELECT p.PackageID,MAX(i.Date)FROM Item iINNER JOIN Package pON p.PackageID = i.PackageIDGROUP BY p.PackageIDHAVING SUM(CASE WHEN i.StatusId = COALESCE(p.StatusId,-1) THEN 1 ELSE 0 END) =0
and for secondSELECT p.PackageID,MAX(i.Date)FROM Item iINNER JOIN Package pON p.PackageID = i.PackageIDGROUP BY p.PackageIDHAVING SUM(CASE WHEN i.StatusId <> COALESCE(p.StatusId,-1) THEN 1 ELSE 0 END) > 0
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs