SCCM SQL Query :Patch compliance progression report

select

      CS.Name0,

      CS.UserName0,

case

when (sum(case when UCS.status=2 then 1 else 0 end))>0 then ('Needs '+(cast(sum(case when UCS.status=2 then 1 else 0 end)as varchar(10))+ ' Patches'))

else 'Good Client'

end as 'Status',

      ws.lasthwscan as 'Last HW scan',

      FCM.collectionID--,

from

      v_UpdateComplianceStatus UCS

left outer join dbo.v_GS_COMPUTER_SYSTEM  CS on CS.ResourceID = UCS.ResourceID

join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID

join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification'

left join v_gs_workstation_status ws on ws.resourceid=CS.resourceid

left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid

Where

      UCS.Status = '2'

and FCM.collectionid = 'SMS00001'

Group by

      CS.Name0,

      CS.UserName0,

      ws.lasthwscan,

      FCM.collectionID

Order by

      CS.Name0,

      CS.UserName0,

      ws.lasthwscan,

      FCM.collectionID

output ::

Name0	UserName0	Status	Last HW scan	collectionID
MACHINE1	CONTOSO\USER11	Needs 43 Patches	7/1/2014 9:47	SMS00001
MACHINE2	CONTOSO\USER2	Needs 43 Patches	6/30/2014 14:16	SMS00001
MACHINE3	CONTOSO\USER3	Needs 61 Patches	7/1/2014 12:34	SMS00001
MACHINE4	NULL	Needs 23 Patches	7/2/2014 12:27	SMS00001
MACHINE5	CONTOSO\USER5	Needs 36 Patches	7/1/2014 12:08	SMS00001
MACHINE6	NULL	Needs 51 Patches	6/5/2014 20:05	SMS00001
MACHINE7	CONTOSO\USER8	Needs 30 Patches	7/2/2014 12:54	SMS00001
MACHINE8	NULL	Needs 32 Patches	6/27/2014 8:28	SMS00001
MACHINE9	CONTOSO\USER9	Needs 43 Patches	6/19/2014 9:15	SMS00001
MACHINE10	CONTOSO\USER12	Needs 10 Patches	6/30/2014 9:50	SMS00001