quote:

SELECT   SiteCode,
        CollectionID,
        COUNT(ResourceID) AS [Count]
FROM     dbo.v_FullCollectionMembership
WHERE    CollectionID = 'ABC0001F' OR CollectionID = 'ABC00062'
GROUP BY SiteCode, CollectionID
ORDER BY SiteCode, CollectionID

quote:

SELECT   DISTINCT colmem1.SiteCode,
                (SELECT COUNT(colmem2.ResourceID)
                 FROM   dbo.v_FullCollectionMembership colmem2
                 WHERE  colmem2.sitecode = colmem1.sitecode
                        AND colmem2.collectionid = 'ABC0001F') AS [Collection ABC0001F Count],
                (SELECT COUNT(colmem2.ResourceID)
                 FROM   dbo.v_FullCollectionMembership colmem2
                 WHERE  colmem2.sitecode = colmem1.sitecode
                        AND colmem2.collectionid = 'ABC00062') AS [Collection ABC00062 Count],
                ((SELECT COUNT(colmem2.ResourceID)
                  FROM   dbo.v_FullCollectionMembership colmem2
                  WHERE  colmem2.sitecode = colmem1.sitecode
                         AND colmem2.collectionid = 'ABC00062')
              - (SELECT COUNT(colmem2.ResourceID)
                  FROM   dbo.v_FullCollectionMembership colmem2
                  WHERE  colmem2.sitecode = colmem1.sitecode
                  AND colmem2.collectionid = 'ABC0001F')) AS [Difference],
                CASE
                  WHEN (SELECT COUNT(colmem2.ResourceID)
                        FROM   dbo.v_FullCollectionMembership colmem2
                        WHERE  colmem2.sitecode = colmem1.sitecode
                               AND colmem2.collectionid = 'ABC00062') = 0 THEN 100
                  ELSE (((SELECT COUNT(colmem2.ResourceID)
                          FROM   dbo.v_FullCollectionMembership colmem2
                          WHERE  colmem2.sitecode = colmem1.sitecode
                                 AND colmem2.collectionid = 'ABC00062')
                      - (SELECT COUNT(colmem2.ResourceID)
                          FROM   dbo.v_FullCollectionMembership colmem2
                          WHERE  colmem2.sitecode = colmem1.sitecode
                          AND colmem2.collectionid = 'ABC0001F'))
                       * 100
                      / (SELECT COUNT(colmem2.ResourceID)
                          FROM   dbo.v_FullCollectionMembership colmem2
                          WHERE  colmem2.sitecode = colmem1.sitecode
                          AND colmem2.collectionid = 'ABC00062'))
                END AS [Percentage]
FROM     dbo.v_FullCollectionMembership colmem1
ORDER BY SiteCode

quote:

set nocount on

/* order sites by hierarchy */
create table #SiteOrder
(
SiteCode char(3),
SiteLevel    int,
PreOrder int identity,
PRIMARY KEY (SiteLevel,SiteCode)
)

declare @NextS char(3)
declare @SiteLevel int

set @SiteLevel=1
set @NextS=''
/* peform a depth first search of the site table, list the sites in pre-order */
while @NextS is not null
begin
  /* find the next child node */
  select @NextS=MIN(SiteCode) from v_Site
  where ReportingSiteCode=@NextS
    and SiteCode not in (select SiteCode from #SiteOrder
                                     where SiteLevel=@SiteLevel)

  if @NextS IS NULL
  begin
     set @SiteLevel=@SiteLevel-1
     /* pop up one level and find the last parent we were working with  */
     select @NextS = SiteCode from #SiteOrder where PreOrder=
           (select MAX(PreOrder) from #SiteOrder where SiteLevel=@SiteLevel-1)
  end
  else
  begin
     insert into #SiteOrder(SiteCode,SiteLevel) values(@NextS,@SiteLevel)
     /* move down one level and and find the children of this site */
     set @SiteLevel = @SiteLevel + 1
  end 
end

/* run the query, order by PreOrder, use SPACE with SiteLevel for indentation */ 

SELECT SPACE(3*(SiteLevel-1))+so.SiteCode as SiteCode,
      s.SiteName,
      cnt.ABC0001F AS [Collection ABC0001F Count],
      cnt.ABC00062 AS [Collection ABC00062 Count],
      cnt.ABC00062 - cnt.ABC0001F AS [Difference],
CASE
      WHEN cnt.ABC00062 = 0 THEN NULL
      ELSE ROUND((cnt.ABC00062 - cnt.ABC0001F)*100.0/cnt.ABC00062, 2)
END AS [Percentage]
FROM #SiteOrder so
JOIN v_Site s ON so.SiteCode=s.SiteCode
LEFT JOIN (SELECT
             fcm.SiteCode,
             SUM(CASE fcm.CollectionID WHEN 'ABC0001F' THEN 1 ELSE 0 END) as [ABC0001F],
             SUM(CASE fcm.CollectionID WHEN 'ABC00062' THEN 1 ELSE 0 END) as [ABC00062]
          FROM
             dbo.v_FullCollectionMembership AS fcm
          WHERE
             fcm.CollectionID IN('ABC0001F','ABC00062')
          GROUP BY fcm.SiteCode) AS cnt ON so.SiteCode=cnt.SiteCode
ORDER BY so.PreOrder

drop table #SiteOrder

quote:

ORIGINAL: jnelson993

Have you put the SQL query I gave you into a report and tried to run it? 

quote:

 
then I tried 'Microsoft Office Outlook 2007'and the counts are way off

quote:

ORIGINAL: jnelson993

CASE
   WHEN SUM(CASE WHEN tmp.ResourceID IS NOT NULL THEN 1 END) = 0 THEN NULL
   ELSE CAST(SUM(CASE WHEN tmp.ResourceID IS NOT NULL THEN 1 ELSE 0 END) - SUM(CASE WHEN tmp.resourceID IS NULL THEN 1 ELSE 0 END)) * 100.0/SUM(CASE WHEN tmp.ResourceID IS NOT NULL THEN 1 ELSE 0 END) AS DECIMAL(5,2))
END AS [Percentage of Product's Installations]

quote:

ORIGINAL: jnelson993

Let me just paste the whole thing then. Oh, and I changed it to DECIMAL(19,2) to take into account those times when you have a really high percentage or if you have a really high negative percentage (I had an example of that when I tested it)

SET NOCOUNT ON

-- PUT RESOURCEIDs THAT HAVE SOFTWARE INTO TEMP TABLE
SELECT DISTINCT
ResourceID
INTO
#tmpHasSoftware
FROM
dbo.v_Add_Remove_Programs
WHERE
DisplayName0 LIKE @ProductName

-- LEFT JOIN TO THAT TEMP TABLE. IF tmp.RESOURCEID IS NULL, MEANS THAT
-- MACHINE DOESN'T HAVE THE PRODUCT, IF NOT NULL, MACHINE HAS THE PRODUCT
SELECT
SUM(CASE WHEN tmp.resourceID IS NULL THEN 1 ELSE 0 END) AS [MissingProduct],
SUM(CASE WHEN tmp.ResourceID IS NOT NULL THEN 1 ELSE 0 END) AS [HasProduct],
SUM(CASE WHEN tmp.ResourceID IS NOT NULL THEN 1 ELSE 0 END) - SUM(CASE WHEN tmp.resourceID IS NULL THEN 1 ELSE 0 END) AS [Difference],
CAST(ROUND((CASE
               WHEN SUM(CASE WHEN tmp.ResourceID IS NOT NULL THEN 1 END) = 0 THEN NULL
               ELSE (SUM(CASE WHEN tmp.ResourceID IS NOT NULL THEN 1 ELSE 0 END) - SUM(CASE WHEN tmp.resourceID IS NULL THEN 1 ELSE 0 END)) * 100.0/SUM(CASE WHEN tmp.ResourceID IS NOT NULL THEN 1 ELSE 0 END)
            END),2) AS DECIMAL(19,2)) AS [Percentage of Product's Installations]
FROM
dbo.v_R_System AS sys
LEFT JOIN  #tmpHasSoftware AS tmp
   ON sys.resourceID = tmp.resourceID
WHERE
sys.obsolete0 = 0
AND sys.decommissioned0 = 0
AND sys.client0 = 1

DROP TABLE #tmpHasSoftware

quote:

ORIGINAL: jnelson993

OK, now I'm rethinking this again.  That version doesn't have the DISTINCT in the right place, making less work for the SQL engine than it should have. 

It should be
SELECT COUNT(DISTINCT ResourceID)...
instead of
SELECT DISTINCT COUNT(ResourceID)...

The difference is, you want to count the distinct resourceIDs, you don't want a distinct overall count because there *IS* only one distinct count.  When you do it that way, now the cost is roughly the same as mine and the difference is negligible. 

Next, the @HAS number isn't removing obsolete or decommissioned records, so that number is likely too high, especially if you have a large environment with lots of decommissioned or obsolete records.

Then I'd change the SUM at the end to round properly, and a couple other things...

OK, gimme a few, I've gotta rewrite this thing, but I've got supper with the family first.

quote:

ORIGINAL: jnelson993

Oh, one last thing, I think there's some confusion on what the % is you're trying to display...do you want
1) the percent of total machines that HAVE the product
example: 10 machines total, 8 have the product, 2 missing, percent should be 80

2) the percent of total machines that are MISSING the product
example: 10 machines total, 8 have the product, 2 missing, percent should be 20

3) something else?