June 4, 2018 at 1:03 pm
jasona.work - Monday, June 4, 2018 12:34 PMMatt Miller (4) - Monday, June 4, 2018 12:17 PMJason - -This looks like a variant of the adjacency list issue. Jeff wrote up something a few years back on it.
Yeah, I looked at treating it as a hierarchy initially, but that didn't pan out. Using the diagram of the employee org chart in Jeff's example, add a line indicating that Kim (EmpID 14) *ALSO* reports to Lisa (EmpID 18) That seemed to throw a monkey wrench into the solutions.
Yeah, you can't have a hierarchy with multiple reporting paths from one individual up to the highest level. That effectively removes it from being a true hierarchy.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 4, 2018 at 1:48 pm
jasona.work - Monday, June 4, 2018 12:34 PMMatt Miller (4) - Monday, June 4, 2018 12:17 PMJason - -This looks like a variant of the adjacency list issue. Jeff wrote up something a few years back on it.
Yeah, I looked at treating it as a hierarchy initially, but that didn't pan out. Using the diagram of the employee org chart in Jeff's example, add a line indicating that Kim (EmpID 14) *ALSO* reports to Lisa (EmpID 18) That seemed to throw a monkey wrench into the solutions.
True. there's a loop, so how would we expect to break the infinite recursion?
with cte_data as (
select candidateid
, parentStep
, childStep
,0 as steplevel
,cast(right('000'+cast(childstep as varchar(3)),3) as varchar(4000)) anchor
from #testdata td
where td.parentStep not in (select td1.childstep from #testdata td1 where td1.CandidateID=td.CandidateID)
union all
select td.candidateid
, td.parentStep
, td.childStep
,c.steplevel+1
,cast(c.anchor+ '-'+right('000'+cast(td.childstep as varchar(3)),3)+
case when c.anchor not like '%'+right('000'+cast(td.childstep as varchar(3)),3) +'%' then '' else 'stop' end as varchar(4000))
from #testdata as td join cte_data c on c.CandidateID=td.CandidateID and c.ChildStep=td.parentstep
where c.anchor not like '%stop'
--and c.steplevel<99
)
select *
from cte_data
order by parentStep
option(maxrecursion 100)
certainly not pretty but this would at least show it until you hit the first "loop".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply