Page History: Hierarchies - SQL Server

Compare Page Revisions

0 Current 0

« Older Revision - Back to Page History - Current Revision

Page Revision: Tue, Jan 13, 2009, 9:42 AM

Note: The following T-SQL will work in SQL Server 2005 or later.

Bottom-Up Hierarchy¶

declare @root int
set @root = 12345678 -- VP's Resource ID

with emp (name_lf, emp_nbr, mgr_nbr, lvl, root_emp_nbr) as (	
    select
        name_lf
        , emp_nbr
        , manager_id
        , lvl = 1
        , root_emp_nbr = emp_nbr
    from 
        vw_associate
    union all select
        a.name_lf
        , a.emp_nbr
        , a.manager_id
        , e.lvl+1
        , root_emp_nbr = e.root_emp_nbr
    from
        emp e                        -- e = emp record
        inner join vw_associate a    -- a = mgr record
            on e.mgr_nbr = a.emp_nbr
	)
select 
	tree = replicate('.',lvl) + convert(varchar(2),lvl), 
	name_lf, 
	emp_nbr, 
	mgr_nbr, 
	lvl, 
	root_emp_nbr
from 
	emp 
where 
	root_emp_nbr = @root
order by 
	lvl

Top-Down Hierarchy¶

declare @root int
set @root = 12345678 -- VP's Resource ID

with emp as (
    -- Base Case
    select
        name_lf,  
        emp_nbr,  
        manager_id,  
        lvl = 1,  
        root_emp_nbr = emp_nbr,  
        emp_nbr_path = convert(varchar(1000),name_lf + '[' + 
            convert(varchar(7),emp_nbr) + ']')
    from 
        vw_associate

    -- Recursive Step
    union all select
        a.name_lf,  
        a.emp_nbr,  
        a.manager_id,  
        e.lvl+1,  
        root_emp_nbr = e.root_emp_nbr,  
        emp_nbr_path = convert(varchar(1000), e.emp_nbr_path + '\' + 
            a.name_lf + '[' + convert(varchar(7),a.emp_nbr) + ']')
    from
        emp e                        -- e = mgr record
        inner join vw_associate a    -- a = emp record
            on a.manager_id = e.emp_nbr
    where 1=1
        and a.last_name not like 'Offshore%'
        and a.last_name not like 'Onshore%'
        and a.last_name not like 'Hold for%'
        and a.last_name not like 'Consultant%'
        and a.last_name not like 'New%hire%'
        and a.last_name not like 'IBM%'
        and a.first_name not like 'Developer%'
        and a.emp_status in ('A','P','L')
    )
select 
	tree = replicate('.',lvl) + convert(varchar(2),lvl),  
	name_lf,  
	emp_nbr
from  
	emp 
where  
	root_emp_nbr = @root
order by  
	emp_nbr_path