SELECT 
	   up.CI_ID
	  ,'KB' + up.ArticleID as 'KB Article'
	  ,up.BulletinID
	  ,Product = stuff ((Select ', ' + d.CategoryInstanceName
	   From v_CICategoryInfo_All D 
		where CategoryTypeName = 'Product'
		  and D.CI_ID = up.CI_ID
		for XML path ('')), 1, 2,''
	   )
	  ,(Select top 1 d.CategoryInstanceName
	   From v_CICategoryInfo_All D 
		where CategoryTypeName = 'UpdateClassification'
		  and D.CI_ID = up.CI_ID
		) as Classification
	  ,Case when up.IsExpired = 0 then ''
	   Else 'Expired'
	   end as 'Expired'
	  ,Case when up.IsSuperseded = 0 then ''
	   else 'Superseeded'
	   end as 'Superseeded'
	  ,Case when up.CustomSeverity = 0 then ''
	        when up.CustomSeverity = 1 then '1'
			when up.CustomSeverity = 2 then '2'
			when up.CustomSeverity = 3 then '3'
	   end as 'Custom Severity'
	  ,Case when up.CustomSeverity = 0 then ''
	   else 'Excluded'
	   end as 'Manual Exclusion'
	  ,up.DateRevised as Date
	  ,Comp.NumMissing as 'Required by Systems'
	  ,'DM Collections' = stuff ((Select DISTINCT ', ' + cra.AssignmentName
	  from v_CIAssignmentToCI cr
	   Left Join vSMS_UpdateGroupAssignment cra on cra.AssignmentID = cr.AssignmentID
	   where cr.CI_ID = up.CI_ID
	     and cra.AssignmentName like 'DM Report %'
		for XML path ('')), 1, 2,''
	   )
	  ,up.Title
	  ,up.InfoURL
	  ,up.Description
	  
  FROM v_UpdateInfo UP
  Left Join vUpdate_ComplianceSummary Comp on comp.CI_ID = UP.CI_ID

  where CIType_ID <> '9'

  order by Comp.NumMissing desc