Recursive Query and Count (SQL Server)

Question

I'm trying 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

The problem is, recursive CTEs in SQL Server have got some limitations. In particular the recursive part is not allowed to contain grouping and, consequently, aggregates. For that reason I would really like to know what your solution for a single entry, especially the one of a higher level, could be. Would you mind posting it? Someone here might have a good idea about how to develop it into a complete solution for your case. — Andriy M
– Andriy M, Commented Mar 21, 2011 at 21:59

Fergus Bown · Accepted Answer · 2011-03-25 16:48:30Z

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

I'm not sure whether you want TotalNumberParticipatingInEvent to be 'Number of separate individuals participating in one or more event' or 'sum of individuals participating in each event'. The query above does the latter, but would be easy to change into the former - just modify the sub query...
I'm not sure that technique gets a specific name - at least not one I know of...!

Collectives™ on Stack Overflow

Recursive Query and Count (SQL Server)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related