Recursive report based on manager of managers...

[NickyJay]

Hi all,

Hope some one can help me with this. I have a table of contacts, each record has (for simplicity) ContactID, Name, ManagerID.

I am able to get staff that work for a manager, and have this working as a drilldown reports, but need to arrange it from the top manager level, where a top level manager manages middler manager, who in turn manage staff (hope this makes sense!!)

I'm struggling to add a top level to the query i have:

/********This is to get the top level manager....****/
SELECT     CONTACTS_1.ID AS AdviserCode, CONTACTS_1.DisplayName AS AdviserName, CONTACTS_1.ManagerID AS ManagerRef, 
                      CONTACTS.DisplayName AS ManagerName
FROM         CONTACTS RIGHT OUTER JOIN
                      CONTACTS AS CONTACTS_1 ON CONTACTS.ID = CONTACTS_1.ManagerID
WHERE     (CONTACTS_1.ManagerID = 5455)
ORDER BY AdviserName

/*********This is the query i have to list all staff under a middle manager**************/
/*****Parameter @Manager is populated when this report is run ************/
SELECT     ID AS AdviserCode, Firstname, Surname, DisplayName, Org_Ref AS OrganisationRef, ManagerID AS ManagerRef
FROM         CONTACTS
WHERE     (ManagerID = @Manager)
ORDER BY Surname

How do i combine the two to get a "tree" of top level manager, middle managers and all staff?

Thanks - this one has me a little baffled???

 

[DatabasePrincess]

What version of SQL Server are you using? If it's 2005 or 2008, there's a template regarding this sort of thing. It's called "Direct Reports Recursion Example". It basically uses a CTE (common table expression).

http://technet.microsoft.com/en-us/library/ms186243(SQL.90).aspx

Hope that helps!

 

[NickyJay]

Hiya,

Many thanks for this. I'm using 2008. I had a look at the CTE but got a bit baffeled - mainly in that i'm accessing a database that i can't write anything other than reports against so wasn't able to run the example.

I have mananged to create myself a query but not have issues with displaying it into a report. I have Manager, with all sub Managers and staff listed, and now want to include any activity by each sub manager or staff on the database. This bit is ok but i can't seem to display it via drill down report - also, where someone is not a submanager but is managed by the main manager, i can't seem to display correctly. Do i need an IIF statement in the report to output this?

Here is my query so far:

SELECT  T2.ID AS SubManagerID, 
		T2.DisplayName AS SubManager, 
		CR_HISTORY.HistoryID AS SM_HID, 
		SCH_TYPES.Description AS SM_TypeOfHist, 
		SCH_TYPECATS.Description AS SM_CategoryOfHist, 
		CR_HISTORY.Client_Ref AS SM_ClientSeen, 
		CR_HISTORY.DateStart AS SM_DateStart, 
		CR_HISTORY.DateEnd AS SM_DateEnd, 
		SC_CLSTAT_1.Description AS SM_Destination, 
		CR_HISTORY.Notes AS SM_HistoryDetails, 
		T3.ID AS PA_ID, 
		T3.DisplayName AS PA, 
		CR_HISTORY_1.HistoryID AS PA_HID, 
		SCH_TYPES_1.Description AS PA_TypeOfHist, 
		CR_HISTORY_1.Client_Ref AS PA_ClientSeen, 
		SCH_TYPECATS_1.Description AS PA_CategoryOfHist, 
		CR_HISTORY_1.DateStart AS PAStartDate, 
		CR_HISTORY_1.DateEnd AS PA_DateEnd, 
		SC_CLSTAT.Description AS PA_Destination, 
		CR_HISTORY_1.Notes AS PA_HistoryDetails, 
		T2.ManagerID
FROM CONTACTS AS T2 LEFT OUTER JOIN
          SC_CLSTAT AS SC_CLSTAT_1 RIGHT OUTER JOIN
          CR_HISTORY LEFT OUTER JOIN
          SCH_TYPECATS INNER JOIN
          SCH_TYPES ON SCH_TYPECATS.Type_Ref = SCH_TYPES.Key_Ref ON CR_HISTORY.HistoryType_Ref = SCH_TYPES.Key_Ref AND 
          CR_HISTORY.HistoryCategory_Ref = SCH_TYPECATS.Cat_Ref ON SC_CLSTAT_1.Key_Ref = CR_HISTORY.StatusNew ON 
          T2.ID = CR_HISTORY.ContactID LEFT OUTER JOIN
          SC_CLSTAT RIGHT OUTER JOIN
          SCH_TYPECATS AS SCH_TYPECATS_1 INNER JOIN
          SCH_TYPES AS SCH_TYPES_1 ON SCH_TYPECATS_1.Type_Ref = SCH_TYPES_1.Key_Ref RIGHT OUTER JOIN
          CR_HISTORY AS CR_HISTORY_1 ON SCH_TYPES_1.Key_Ref = CR_HISTORY_1.HistoryType_Ref AND 
          SCH_TYPECATS_1.Cat_Ref = CR_HISTORY_1.HistoryCategory_Ref RIGHT OUTER JOIN
          CONTACTS AS T3 ON CR_HISTORY_1.ContactID = T3.ID ON SC_CLSTAT.Key_Ref = CR_HISTORY_1.StatusNew ON T2.ID = T3.ManagerID
WHERE CR_HISTORY.DateStart BETWEEN @From AND @To
AND CR_HISTORY_1.DateStart BETWEEN @From AND @To
AND T2.ManagerID = '5455'

Many thanks for any help, it really is appreciated!!