Required Patches based on collection ID

SELECT DISTINCT
                      TOP (100) PERCENT SYS.Name0 AS [Machine Name], UCS.Status AS [Patch Status Code],
                      CASE WHEN UCS.Status = '2' THEN 'Applicable' WHEN UCS.Status = '3' THEN 'Installed' ELSE '' END AS 'Patch Status', UI.BulletinID, UI.ArticleID, UI.Title,
                      dbo.v_FullCollectionMembership.ResourceID, dbo.v_Collection.CollectionID
FROM         dbo.v_FullCollectionMembership INNER JOIN
                      dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID INNER JOIN
                      dbo.v_R_System AS SYS LEFT OUTER JOIN
                      dbo.v_Update_ComplianceStatusAll AS UCS ON SYS.ResourceID = UCS.ResourceID INNER JOIN
                      dbo.v_UpdateInfo AS UI ON UCS.CI_ID = UI.CI_ID ON dbo.v_FullCollectionMembership.ResourceID = UCS.ResourceID
WHERE     (UCS.Status IN ('3', '2')) AND (dbo.v_Collection.CollectionID = 'CollectionID')
ORDER BY UI.ArticleID