SELECT	 
		RM_ResourceSoftware_Bulletin.Name [Bulletin]
		--,coalesce(swuTask.[Software Updates],0) [Software Updates]
		--,coalesce(activeSWU.[Active Updates],0) [Active Updates]
		,policy.Name [Name of Software Update Policy]
		, TgName.Name [Target]
		, CompByTarget.NumPcs [Computers in Target]
		,SUM(swuTask.Installed) AS Installed
FROM 
	(SELECT COUNT(Hint)[Software Updates]
	,vi.guid AS [BulletinGuid]
	,sua._ResourceGuid
	, ir.ParentItemGuid
	, ita.ResourceTargetGuid
	,vi.NAME
	,CASE WHEN COUNT(sua.UpdateGuid) = SUM(CASE WHEN sui.UpdateGuid IS NULL THEN 0 WHEN ses.PendingSince IS NULL THEN 1 ELSE 0 END)	THEN 1	ELSE 0	END AS [Installed]
	FROM vPMWindows_UpdateApplicable sua
		JOIN ResourceAssociation b2u ON b2u.ChildResourceGuid = sua.UpdateGuid AND b2u.ResourceAssociationTypeGuid = '7EEAB03A-839C-458D-9AF2-55DB6B173293' -- Software Bulletin To Software Update
		join ItemReference ir on ir.ChildItemGuid = b2u.ChildResourceGuid
		JOIN vItem vi ON vi.Guid = b2u.ParentResourceGuid
		LEFT JOIN vPMWindows_UpdateInstalled sui ON sui.UpdateGuid = sua.UpdateGuid AND sui._ResourceGuid = sua._ResourceGuid
		LEFT JOIN vPMCore_ComputersPendingRebootByPackage ses ON ses.SoftwareUpdateGuid = sua.UpdateGuid AND ses._ResourceGuid = sua._ResourceGuid
		LEFT JOIN ItemAppliesTo ita on ita.ItemGuid = sua._ResourceGuid
	WHERE sua.UpdateGuid NOT IN -- filter out supersede applicable updates
		(
		SELECT DISTINCT ChildResourceGuid
		FROM ResourceAssociation
		WHERE ResourceAssociationTypeGuid = '644A995E-211A-4D94-AA8A-788413B7BE5D' --Software Component Supersedes Software Component
		)
GROUP BY 
	ir.ParentItemGuid
	,vi.[Guid] 
	,sua._ResourceGuid
	,vi.NAME
	, ita.ResourceTargetGuid ) swuTask 

/*Obtain Policies*/
left join
	(	SELECT count (Hint) [Active Updates],ParentItemGuid [policy], ChildItemGuid, vi.Name
		FROM ItemReference adv
			inner join ItemActive ia ON ia.[Guid] = adv.ParentItemGuid	and adv.Hint like 'policy_swu'
			inner join vItem vi ON vi.[Guid] = ChildItemGuid
		WHERE ia.[Enabled] = 1
		GROUP BY ParentItemGuid, ChildItemGuid, vi.Name) activeSWU ON activeSWU.policy = swuTask.ParentItemGuid
inner join vItem policy ON policy.[Guid] = swuTask.ParentItemGuid
inner join RM_ResourceSoftware_Bulletin ON RM_ResourceSoftware_Bulletin.[Guid] = swuTask.bulletinGuid

/*Obtain Targets*/
left join
		(	SELECT  iat.ResourceTargetGuid, adv.ParentItemGuid, adv.ChildItemGuid
			FROM ItemReference adv
				inner join ItemAppliesTo iat ON iat.ItemGuid = adv.ChildItemGuid
				inner join vItem vi ON vi.[Guid] = adv.ChildItemGuid
			GROUP BY iat.ResourceTargetGuid, adv.ParentItemGuid, adv.ChildItemGuid )Targeted ON Targeted.ParentItemGuid = policy.[Guid]
inner join vItem TgName ON TgName.[Guid] = targeted.ResourceTargetGuid
/*Computers By Target*/
inner join ( SELECT  vi.Name AS [ComputerName]
							, COUNT(rtm.ResourceGuid) AS [NumPcs]
							, rtm.ResourceTargetGuid 
			 FROM ResourceTargetMembershipCache rtm
				 join vItem vi ON vi.[Guid] = rtm.ResourceTargetGuid
			 GROUP BY  vi.Name, rtm.ResourceTargetGuid ) CompByTarget ON CompByTarget.ResourceTargetGuid = TgName.[Guid]
/*Installed*/
 GROUP BY
		RM_ResourceSoftware_Bulletin.Name 
		,policy.Name 
		, TgName.Name
		, CompByTarget.NumPcs