SQL query to get patch compliance reports

SELECT DISTINCT
ps.Bulletin AS Bulletin_No,
ps.Retrying + ps.PreSuccess + ps.Uninstalled + ps.PendReboot + ps.Verified + ps.NoStatus + ps.Failed - ps.Verified AS Unpatched,
ps.Retrying + ps.PreSuccess + ps.Uninstalled + ps.PendReboot + ps.Verified + ps.NoStatus + ps.Failed AS 'Total with Status',
ROUND((100 * (ps.Verified + .00000001)) / (.00000001 + ps.Retrying + ps.PreSuccess + ps.Uninstalled + ps.PendReboot + ps.Verified + ps.NoStatus + ps.Failed), 0) AS '% Compliant',
ps.Verified, ps.NoStatus, ps.Retrying, ps.PreSuccess, ps.Uninstalled, ps.PendReboot, ps.Failed, real_total.total, ps.CollectionID
FROM (
SELECT fcm.CollectionID,
pse.ID AS Bulletin,
SUM(CASE WHEN pse.LastStateName = 'No Status' THEN 1 ELSE 0 END) AS NoStatus,
SUM(CASE WHEN pse.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) / 2 AS Verified,
SUM(CASE WHEN pse.LastStateName = 'Retrying' THEN 1 ELSE 0 END) AS Retrying,
SUM(CASE WHEN pse.LastStateName = 'Preliminary Success' THEN 1 ELSE 0 END) AS PreSuccess,
SUM(CASE WHEN pse.LastStateName = 'Uninstalled' THEN 1 ELSE 0 END) AS Uninstalled,
SUM(CASE WHEN pse.LastStateName = 'Reboot pending' THEN 1 ELSE 0 END) AS PendReboot,
SUM(CASE WHEN pse.LastStateName = 'Failed' THEN 1 ELSE 0 END) AS Failed
FROM
v_ApplicableUpdatesSummaryEx INNER JOIN
v_GS_PatchStatusEx pse ON v_ApplicableUpdatesSummaryEx.UpdateID = pse.UpdateID RIGHT OUTER JOIN
v_FullCollectionMembership fcm ON pse.ResourceID = fcm.ResourceID
WHERE
(pse.QNumbers NOT LIKE 'None')
AND (pse.ID NOT LIKE 'None')
AND (fcm.CollectionID = 'SMS000ES' )
GROUP BY pse.ID
, v_ApplicableUpdatesSummaryEx.Type
, fcm.CollectionID
HAVING
(v_ApplicableUpdatesSummaryEx.Type = 'Microsoft Update')) ps
INNER JOIN
(
SELECT DISTINCT ID0
FROM v_GS_PATCHSTATEEX
WHERE (Language0 = 'English' Or LocaleID0 In ('0','9'))
AND ID0 <> 'none'
AND Type0 = 'Microsoft Update'
AND Severity0 = '10') As PatchList
ON ps.Bulletin = PatchList.ID0
CROSS JOIN
(SELECT CollectionID, COUNT(ResourceID) AS total
FROM v_FullCollectionMembership
GROUP BY CollectionID
HAVING (CollectionID = 'SMS000ES' )) real_total
ORDER BY ps.Bulletin DESC

-- specify collectionID to get respective compliance rate
 
Thanks!