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