sql server - SQL Parent Child Relationship -

i know how create parent/child relationship set of specific months, let's have employee john , want know people working under john, cte this:

with cte  (     select @employeeidtmp employeeid,            0 [level]       union      select em.[employeeid],            [level] + 1       employee em      inner join cte t          on em.[managerid] = t.employeeid      (em.[managerid] <> em.[employeeid]         , em.[managerid] not null) )  select employeeid, [level]   cte 

in cte have specific condition doesn't matter business rules :)

this fine, working on sql server 2008 r2, need build hierarchy relation based not on current month, need example 2 months ago.

if see 1 month it's fine if implement logic cover more 1 month, stuck circular reference right because john have maria working him on january , same hierarchy happens on february, question how can build hierarchy based on happened in period of time, example between january , february.

i'm sure there way mine not :)

sorry i'll provide more data it. let's need run report between january , february 2015, company has organization hierarchy on january different on february because 1 employee change manager or left company. these changes needs reflected on treeview period of month.

here example of treeview:

for january:

john    maria       julia    darin 

for february:

john    maria       julia       nicolas    darin 

if pick date january february should see combination of both including new employee nicolas on february. have a table keeps history of each month keeping employee/manager hierarchy each month have repeated data yes.

table employee:

employeeid int managerid int periodid int

the periodid column number represents month/year example hierarchy january have periodid = 1, february = 2 , on, periodid unique month/year.

i have table value function cte above receives manager , returns employees under him , level.

my cte including periodid looks this:

with cte  (     select @employeeidtmp employeeid,            0 [level]       union      select em.[employeeid],            [level] + 1       employee em      inner join @periodids p         on em.[periodid] = p.[periodid]      inner join cte t          on em.[managerid] = t.employeeid      (em.[managerid] <> em.[employeeid]         , em.[managerid] not null) )  select employeeid, [level]   cte 

when i'm checking 1 month, good, try data 2 months example, taking time , repeating data more 2 times, if i'm specifying 2 months.

if understand correctly, question can reduced "how prevent circular traversal in cte queries". here answer: tsql cte: how avoid circular traversal?