select 	max(sites.SiteName) 		as 'SiteName', 
	isnull(patch.Status0,'unknown') as 'Status', 
	count(wave.name0) 		as 'Site Count' 
from 
( 
	select 	dbo.v_R_System.ResourceID, 
		dbo.v_R_System.Name0 
	from 	dbo.v_R_System inner join dbo.v_GS_TOPAZ_CLIENT 
		on 	dbo.v_R_System.ResourceID = dbo.v_GS_TOPAZ_CLIENT.ResourceID 
	where	dbo.v_GS_TOPAZ_CLIENT.ActivePatchGroup0 = 'WAVE3' 
) wave 
left outer join 
( 
	select 	dbo.v_GS_TOPAZ_CLIENT.ResourceID, 
		dbo.v_GS_PATCHSTATE.Status0 
	from 	dbo.v_GS_TOPAZ_CLIENT inner join dbo.v_GS_PATCHSTATE 
		on 	dbo.v_GS_TOPAZ_CLIENT.ResourceID = dbo.v_GS_PATCHSTATE.ResourceID 
		inner join dbo.v_GS_WORKSTATIon_STATUS 
		on 	dbo.v_GS_TOPAZ_CLIENT.ResourceID = dbo.v_GS_WORKSTATIon_STATUS.ResourceID 
	where	dbo.v_GS_TOPAZ_CLIENT.ActivePatchGroup0 = 'WAVE3' 
	and 	dbo.v_GS_PATCHSTATE.ID0 		= 'MS05-053' 
	and 	(datediff([Day], dbo.v_GS_WORKSTATIon_STATUS.LastHWScan, getdate()) <= 35) 
) patch 
	on 	wave.ResourceID = patch.ResourceID 
left outer join dbo.System_SMS_Instal_ARR 
	on 	wave.ResourceID = dbo.System_SMS_Instal_ARR.ItemKey 
left outer join dbo.Sites 
	on 	dbo.System_SMS_Instal_ARR.SMS_Installed_Sites0 = dbo.Sites.SiteCode 
group by sites.SiteName, status0 
order by sites.SiteName, Status0