sql pros needed to solve logical problem

i dotn see how i can get the desired result set using SQL and i have defiantly over thought the problem and cant seethe trees for the woods at this point.

what i have is a self referencing employees table (empID, MgrID, empUserName, more...) and i have a defects table that has a load of defects that correlated back to the employees table using the empUserName field.

i want a report that takes a param of a empID and gets all defects assigned to them, as well as all of the param's direct reports (so where employees .mgrID =@empid passed in)

now here is the tricky part - the direct reports may have their own direct reports within the hi开发者_Python百科erarchy and these defects need to get rolled up and added to the numbers of defects of the level 1 users. This is whats stumping me, any ideas on this? some pseudo code would be great to follow the logic of how this would be done

thanks all!

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