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.
