CODEWITHSUNDEEP
sqlsql-serversql-server-2008
cmonti
cmonti

Reputation: 187

SQL Parent Child Relationship

I would like to know how to create a parent/child relationship for a set of specific months, let's say we have an employee John and I want to know all the people working under John, so I would do a CTE like this:

WITH CTE
AS 
(
    SELECT @EmployeeIdTmp as EmployeeId,
           0 AS [Level]

     UNION ALL

    SELECT em.[EmployeeId],
           [Level] + 1
      FROM Employee em
     INNER JOIN CTE t 
        ON em.[ManagerId] = t.EmployeeId
     WHERE (em.[ManagerId] <> em.[EmployeeId] 
       AND em.[ManagerId] IS NOT NULL)
)

SELECT EmployeeId, [Level]
  FROM CTE

In this CTE I have a specific where condition but it doesn't matter just business rules :)

This is fine, is working perfectly on SQL Server 2008 R2, now I need to build my hierarchy relation based not only on the current month, I need to look back for example two months ago.

If I see only one month it's fine but If I implement a logic to cover more than one month, I get stuck into a circular reference which is right because John could have Maria working for him on January and also the same hierarchy happens on February, my question is how I can build a hierarchy based on what happened in a period of time, like for example between January and February.

I'm sure there is a way to do it but mine is not :)

Sorry I'll provide more data about it. Let's say I need to run a report between January and February 2015, company has an organization hierarchy on January but could be different on February because one employee change his manager or left the company. So all these changes needs to be reflected on my treeview for that period of month.

Here an example of my treeview:

For January:

John
   Maria
      Julia
   Darin

For February:

John
   Maria
      Julia
      Nicolas
   Darin

If I pick a date from January to February I should see a combination of both including the new employee Nicolas on February. I have a a table that keeps history of each month keeping the employee/manager hierarchy so for each month I could have repeated data yes.

Table Employee:

EmployeeId int ManagerId int PeriodId int

The PeriodId column is a number that represents a month/year so for example my hierarchy for january will have PeriodId = 1, february = 2 and so on, the PeriodId is unique by month/year.

I have a table value function with the CTE above that receives a manager and returns all employees under him and the level.

My CTE including the PeriodId looks like this:

WITH CTE
AS 
(
    SELECT @EmployeeIdTmp as EmployeeId,
           0 AS [Level]

     UNION ALL

    SELECT em.[EmployeeId],
           [Level] + 1
      FROM Employee em
     INNER JOIN @PeriodIds p
        ON em.[PeriodId] = p.[PeriodId]
     INNER JOIN CTE t 
        ON em.[ManagerId] = t.EmployeeId
     WHERE (em.[ManagerId] <> em.[EmployeeId] 
       AND em.[ManagerId] IS NOT NULL)
)

SELECT EmployeeId, [Level]
  FROM CTE

When I'm checking for one month, all is good, but as soon as I try to get data for two months for example, is taking too much time and repeating data more than two times, even if I'm specifying just two months.

Upvotes: 0

Views: 200

Answers (1)

D.R.
D.R.

Reputation: 21194

If I understand correctly, the question can be reduced to "how to prevent circular traversal in CTE queries". Look here for an answer: TSQL CTE: How to avoid circular traversal?

Upvotes: 1

Related Questions