Recursive Query and Count (SQL Server)

I'm try开发者_StackOverflow中文版ing to write a query using these four simplified tables:

Organization

(pk) OrganizationID

OrganizationName

(fk) ParentOrganizationID

Personnel

(pk) PersonnelID

(fk) OrganizationID

Name

Event

(pk) EventID

EventName

EventLog

(pk) PersonnelID

(pk) EventID

TimeOfParticipation

I want to create a query that takes an eventID and an organizationID as a paramater and returns a table that returns all of the organization's name, the total number in the organization and children organizations, and the total number of participants in the event for the organization and it's children. An example return might be:

OrganizationName   | TotalNumberInOrganization | TotalParticipatingInEvent  
TopOrganization    |         200               |            150      
 SecondTier1       |         150               |            100 
  Tier1Child       |          50               |             50
  Tier1Child2      |          50               |             25
 SecondTier2       |          25               |             25

The top organization is the sum of all of its children, SecondTier1 and SecondTier2, and itself. SecondTier1 is the sum of all of it's children, Teir1Child and Tier1Child2, and itself. This would go on calculating all of the children and totals.

I know how to return just one entry using a recursive CTE, for example just the total for the top organization, but I'm not sure how to go about getting the totals for all of the organizations and their children. Any help would be appreciated.

As requested here is the procedure I use to return a single row about an organization.

For some reason if I add the 'U' in UNION it throws me a network error and won't let me edit it.

@OrganizationID uniqueidentifier
@EventID uniqueidentifier

WITH OrganizationList(OrganizationID) AS
    (SELECT Organization.OrganizationID
    FROM Organization
    WHERE OrganizationID = @OrganizationID
    NION ALL
    SELECT Organization.OrganizationID
    FROM Organization 
    INNER JOIN OrganizationList ON Organization.ParentOrganizationID = OrganizationList.OrganizationID)

SELECT OrganizationAbbreviation,

       (SELECT COUNT(*)
        FROM Personnel
        WHERE Personnel.OrganizationID IN (SELECT OrganizationID FROM OrganizationList))
        AS OrganizationTotal,

        (SELECT COUNT(*)
         FROM Personnel 
         INNER JOIN EventLog ON EventLog.PersonnelID = Personnel.PersonnelID
         WHERE Personnel.OrganizationID IN (SELECT OrganizationID FROM OrganizationList)
               AND EventLog.EventID = @EventID)
         AS TotalPresent
FROM Organization
WHERE OrganizationID = @OrganizationID

I think this will work for you:

WITH OrganizationTree (RootOrganizationID, OrganizationID)
AS
(
--Anchor
    SELECT  O.OrganizationID, O.OrganizationID
    FROM    Organization O
    UNION ALL
--Recurse
    SELECT  T.RootOrganizationID, O.OrganizationID
    FROM    OrganizationTree T
    JOIN    Organization O
        ON  O.ParentOrganizationId = T.OrganizationID
)
--execute
SELECT  P.OrganizationName, 
        SUM(ISNULL(PPL.NumberInOrganization, 0)) AS TotalNumberInOrganization, 
        SUM(ISNULL(EVT.NumberParticipatingInEvent, 0)) AS TotalNumberParticipatingInEvent
FROM    OrganizationTree T
JOIN    Organization P
        ON T.RootOrganizationID = P.OrganizationID
LEFT
JOIN    
(
        SELECT  P.OrganizationID, 
                COUNT(*) AS NumberInOrganization
        FROM    Personnel P
        GROUP BY P.OrganizationID
) PPL
        ON  PPL.OrganizationID = T.OrganizationID
LEFT
JOIN
(
        SELECT  P.OrganizationID,
                COUNT(*) AS NumberParticipatingInEvent
        FROM    EventLog EL
        JOIN    Personnel P
                ON  EL.PersonnelID = P.PersonnelID
        GROUP BY P.OrganizationID
) EVT
        ON  EVT.OrganizationID = T.OrganizationID
GROUP BY T.RootOrganizationID, P.OrganizationName

If you need to have the indentation like in your example output, then this should work:

WITH OrganizationTree (RootOrganizationID, OrganizationID)
AS
(
--Anchor
    SELECT  O.OrganizationID, O.OrganizationID
    FROM    Organization O
    UNION ALL
--Recurse
    SELECT  T.RootOrganizationID, O.OrganizationID
    FROM    OrganizationTree T
    JOIN    Organization O
        ON  O.ParentOrganizationId = T.OrganizationID
)
--execute
SELECT  SPACE(L.OrganizationLevel) + P.OrganizationName AS FormattedOrganizationName,
        P.OrganizationName, 
        SUM(ISNULL(PPL.NumberInOrganization, 0)) AS TotalNumberInOrganization, 
        SUM(ISNULL(EVT.NumberParticipatingInEvent, 0)) AS TotalNumberParticipatingInEvent
FROM    OrganizationTree T
JOIN    
(
        SELECT  L.OrganizationID,
                (COUNT(*) - 1) AS OrganizationLevel
        FROM    OrganizationTree L
        GROUP BY L.OrganizationID
) L
    ON  T.RootOrganizationID = L.OrganizationID
JOIN    Organization P
        ON T.RootOrganizationID = P.OrganizationID
LEFT
JOIN    
(
        SELECT  P.OrganizationID, 
                COUNT(*) AS NumberInOrganization
        FROM    Personnel P
        GROUP BY P.OrganizationID
) PPL
        ON  PPL.OrganizationID = T.OrganizationID
LEFT
JOIN
(
        SELECT  P.OrganizationID,
                COUNT(*) AS NumberParticipatingInEvent
        FROM    EventLog EL
        JOIN    Personnel P
                ON  EL.PersonnelID = P.PersonnelID
        GROUP BY P.OrganizationID
) EVT
        ON  EVT.OrganizationID = T.OrganizationID
GROUP BY T.RootOrganizationID, L.OrganizationLevel, P.OrganizationName