recursive sql function with rollup logic?

Question

i have a SQL that using a recursive CTE to expand a self-referancing employees table builds a result set of defects aggregated by user and severity level.

here is my CTE:

    ALTER FUNCTION [dbo].[fnGetEmployeeHierarchyByUsername] 
(    
  @NTID varchar(100) = null
)  
RETURNS TABLE  
AS  
RETURN  
(  
  WITH yourcte AS  
  (  
    SELECT EmployeeId, ManagerNTID, ManagerID, NTID, FullName--, Name  
    FROM Employees  
    WHERE NTID = @NTID
    UNION ALL  
    SELECT e.EmployeeId, e.ManagerNTID, e.ManagerID, e.NTID, e.FullName--, e.Name  
    FROM Employees e  
    JOIN yourcte y ON e.ManagerNTID = y.NTID
  )  
SELECT EmployeeId, ManagerID, NTID, FullName--, Name  
FROM yourcte  
)

here is my SQL for aggregating defects by the user:

SELECT e.FullName, Urgent, High, Medium, Low
FROM fnGetEmployeeHierarchyByUsername ('ssalvati') e
LEFT OUTER JOIN(
    SELECT [AssignedTo],
           SUM([1-Urgent]) AS Urgent,
           SUM([2-High]) AS High,
           SUM([3-Medium]) AS Medium,
           SUM([4-Low]) AS Low
      FROM (SELECT [AssignedTo],[BusinessSeverity] FROM Defects WHERE Status <> 'Closed') D
     PIVOT (COUNT([BusinessSeverity]) FOR [BusinessSeverity] IN ([1-Urgent],[2-High],[3-Medium],[4-Low])) V
    GROUP BY [AssignedTo]) AS def
ON e.ntid = def.[AssignedTo]

i want to have a porc that takes a username as a param and generates a result like the SQL above but with 2 enhancements:

1. i need it to list the user passed in as a param to be listed as the first record of the result-set.

2. i need the employees that report into the manager to show only one level deep and not show the full tree. the first level should be a roll up of all the underlying defects assigned to people who roll up into all the level one users. in other words i dont want to show a entire tree under the manager like it is now, i need it to show only one level deep but with a sum of defects for all the levels.

ideas?

Answer 1

This isn't tested as I don't have a mssql install here nor your data, but, I think it should be generally right and at least push you in a useful direction.

First, you need to change the query in your UDF to give two additional pieces of information. The "topmost" employee for your aggregation collapsing (which I think you said is the first direct report, not the very top employee), and the overall depth. As such:

WITH yourcte AS  
  (  
    SELECT EmployeeId, ManagerNTID, ManagerID, NTID, FullName, 0 as Depth, ntid as Topmost  
    FROM Employees  
    WHERE NTID = @NTID
    UNION ALL  
    SELECT e.EmployeeId, e.ManagerNTID, e.ManagerID, e.NTID, e.FullName, y.Depth+1, case when y.depth = 0 then e.ntid else y.Topmost end
    FROM Employees e  
    JOIN yourcte y ON e.ManagerNTID = y.NTID
  )  
SELECT EmployeeId, ManagerID, NTID, FullName, Depth, Topmost  
FROM yourcte

Then, your actual query needs a few extra details to extract that information and use it

SELECT 
  e.FullName, 
  Urgent, 
  High, 
  Medium, 
  Low
FROM fnGetEmployeeHierarchyByUsername ('ssalvati') e
LEFT OUTER JOIN(
    SELECT [AssignedTo],
           SUM([1-Urgent]) AS Urgent,
           SUM([2-High]) AS High,
           SUM([3-Medium]) AS Medium,
           SUM([4-Low]) AS Low
      FROM (SELECT [AssignedTo],[BusinessSeverity] FROM Defects WHERE Status <> 'Closed') D
      join fnGetEmployeeHierarchyByUsername ('ssalvati') e2 on d.AssignedTo = e2.ntid
     PIVOT (COUNT([BusinessSeverity]) FOR [BusinessSeverity] IN ([1-Urgent],[2-High],[3-Medium],[4-Low])) V
     where e2.TopMost = e.ntid
    GROUP BY [AssignedTo]) AS def
ON e.ntid = def.[AssignedTo]
where e.Depth <= 1

The double call to your UDF might be a bit expensive, so you may want to consider putting this into a sproc and using a temp table to catch the results of the UDF to join against.

Also note that the UDF could take an extra parameter as to how deep "topmost" is, making this more general that it currently is in its hardcoded form.

Answer 2

If you modified your cte to include the depth i.e.

WITH yourcte AS  
  (  
    SELECT EmployeeId, ManagerNTID, ManagerID, NTID, FullName, 0 AS Depth
    FROM Employees  
    WHERE NTID = @NTID
    UNION ALL  
    SELECT e.EmployeeId, e.ManagerNTID, e.ManagerID, e.NTID, e.FullName, y.Depth + 1
    FROM Employees e  
    JOIN yourcte y ON e.ManagerNTID = y.NTID
  )

You can then order your output by depth (as the user in the input parameter should be at depth zero). Using this you should also be able to limit the depths you return and aggregate defects where depth >= 1

---

Edit

With the SQL I added above you basically want to rollup all defects to the user at Level 1? So, the NTID of the user at this level becomes the group by item for all records with depth >= 1. Another edit to the cte below adds the NTID as GroupingID which you can use to group by / rollup

WITH yourcte AS  
  (  
    SELECT EmployeeId, ManagerNTID, ManagerID, NTID
          ,FullName, 0 AS Depth, NTID as GroupingID
    FROM Employees  
    WHERE NTID = @NTID
    UNION ALL  
    SELECT e.EmployeeId, e.ManagerNTID, e.ManagerID, e.NTID
          ,e.FullName, y.Depth + 1, CASE
                                       WHEN y.Depth + 1 = 1 THEN e.NTID
                                       ELSE y.GroupingId
                                    END
    FROM Employees e  
    JOIN yourcte y ON e.ManagerNTID = y.NTID
  )

Answer 3

here is the long dummy way of doing it. i have it working but the solution could be much better. i am hoping someone will post a SQL2005 way of getting this done...

    alter PROC sel_DefectReportByManagerNTID_rollup
(@ManagerNTID NVARCHAR(100))    
AS

CREATE TABLE #DefectCounts
(
id INT IDENTITY(1, 1) ,
MgrRolledInto NVARCHAR(100) NULL,
AltBusinessSeverity NVARCHAR(100) NULL,
DefectCount INT NULL
);


CREATE TABLE #directReports
(
pk INT IDENTITY(1, 1) ,
directReportNTID NVARCHAR(100) NULL
);

INSERT INTO #directReports
SELECT NTID FROM Employees WHERE ManagerNTID = @ManagerNTID
--select * from #directReports

DECLARE @maxPK INT;
SELECT @maxPK = MAX(PK) FROM #directReports

DECLARE @pk INT;
SET @pk = 1


INSERT INTO #DefectCounts (MgrRolledInto,AltBusinessSeverity,DefectCount)
SELECT @ManagerNTID, d.AltBusinessSeverity, COUNT(*)
        FROM Defects d
            JOIN StatusCode C ON C.CodeName = d.Status AND c.scid = 10
        WHERE d.AssignedTo = @ManagerNTID
        GROUP BY d.AltBusinessSeverity


WHILE @pk <= @maxPK
BEGIN
    /* Get one direct report at a time to aggregate their defects under them... */
    DECLARE @dirRptNTID NVARCHAR(100);
    SET @dirRptNTID = (SELECT directReportNTID
                        FROM #directReports
                        WHERE PK = @pk)


    INSERT INTO #DefectCounts (MgrRolledInto,AltBusinessSeverity,DefectCount)
        SELECT @dirRptNTID, d.AltBusinessSeverity, COUNT(*)
        FROM Defects d
            JOIN StatusCode C ON C.CodeName = d.Status AND c.scid = 10
            JOIN (SELECT * FROM fnGetEmployeeHierarchyByUsername(@dirRptNTID) ) emp ON emp.NTID = d.AssignedTo
        WHERE d.AssignedTo IS NOT NULL
        GROUP BY d.AltBusinessSeverity

    SELECT @pk = @pk + 1
END



SELECT  e.FullName,     
  isnull(Urgent,0) as Urgent,     
  isnull(High,0) as High,     
  isnull(Medium,0) as Medium,    
  isnull(Medium3000,0) as Medium3000,    
  isnull(Low,0) as Low    
FROM (  select * from fnGetEmployeeHierarchyByUsername (@ManagerNTID) where depth <= 1) e    
left outer join (
                    SELECT  MgrRolledInto,    
                            SUM([1-Urgent]) AS Urgent,    
                            SUM([2-High]) AS High,    
                            SUM([3-Medium]) AS Medium,    
                            SUM([3-Medium (3000)]) AS Medium3000,  
                            SUM([4-Low]) AS Low    
                    FROM #DefectCounts dfs
                    PIVOT 
                    (sum(DefectCount) FOR AltBusinessSeverity IN ([1-Urgent],[2-High],[3-Medium],[3-Medium (3000)],[4-Low])) V    
                    GROUP BY MgrRolledInto
                ) def_data on def_data.MgrRolledInto = e.NTID
order by e.depth