sql pros needed to solve logical problem

Question

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 hierarchy 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!

Read this : http://msdn.microsoft.com/en-us/library/ms186243.aspx — Donnie, Dec 08 '10 at 00:06
@Donnie, im using a cte to get the expanded view of the employee hierarchy, but cant figure out a way to aggregate all the counts of defects for all the level 1 users and their subordinates. — kacalapy, Dec 08 '10 at 00:10
If you're doing the expansion with a recursive CTE, you should be able to collapse it back down via aggregation at the end. What you have done so far would help me be more specific. — Donnie, Dec 08 '10 at 00:13
@Donnie - have a look at this, it gets me the perefect resultset but fails to roll up the level 1s children counts as i require: http://stackoverflow.com/questions/4378975/recursive-sql-function-with-rollup-logic — kacalapy, Dec 08 '10 at 14:03

score 0 · Accepted Answer · answered Dec 08 '10 at 21:27

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

sql pros needed to solve logical problem

1 Answers1