select CollectionID, COUNT(*) as MemberCount into #TempTable from v_FullCollectionMembership group by CollectionID

SELECT dbo.v_Advertisement.AdvertisementID,
dbo.v_Advertisement.AdvertisementName,
‘PackageType’ =
Case
when v_Package.PackageType = 0 Then ‘Software Distribution Package’
when v_Package.PackageType = 3 Then ‘Driver Package’
when v_Package.PackageType = 4 Then ‘Task Sequence Package’
when v_Package.PackageType = 5 Then ‘Software Update Package’
when v_Package.PackageType = 6 Then ‘Device Setting Package’
when v_Package.PackageType = 7 Then ‘Virtual Package’
when v_Package.PackageType = 8 Then ‘Application’
when v_Package.PackageType = 257 Then ‘Image Package’
when v_Package.PackageType = 258 Then ‘Boot Image Package’
when v_Package.PackageType = 259 Then ‘Operating System Install Package’
Else
‘Unknown’
End,
v_Collection.CollectionID,
v_Collection.Name as CollectionName,
#TempTable.MemberCount,
dbo.v_Advertisement.PackageID,
dbo.v_Package.Name as PackageName,
dbo.v_Package.PkgSourcePath,
dbo.v_Advertisement.ProgramName,
dbo.v_Program.CommandLine

FROM dbo.v_Advertisement INNER JOIN
dbo.v_Program ON dbo.v_Advertisement.PackageID = dbo.v_Program.PackageID AND
dbo.v_Advertisement.ProgramName = dbo.v_Program.ProgramName INNER JOIN
dbo.v_Package ON dbo.v_Advertisement.PackageID = dbo.v_Package.PackageID INNER JOIN
dbo.v_Collection ON dbo.v_Advertisement.CollectionID = dbo.v_Collection.CollectionID INNER JOIN
#TempTable ON dbo.v_Advertisement.CollectionID = #TempTable.CollectionID