tsql - A tree structure in SQL Server that needs to be flattened in a very special way -

we have table looks :

create table hierarchy (employeeid int not null, parentid int, orderlimit int) create table order (employeeid int not null, ordersize int) 

it's supposed tell approval range each employee on orders, , decided keep employee manager's id, in same record, because in case order exceeds person's limit, his/her manager (referred parentid) should approve it.

if exceeds upper level again, order should go higher level , on , on, until reaches level qualifies approve order. if parentid null 1 person, means highest level in management structure , don't know how many possible levels might have, , if highest level'

to clarify consider this

insert hierarchy values  (1,10,0), (2,11,0), (3,12,0), (10,20,100), (11,21,300), (12,22,200), (20,30,1000), (21,31,2000), (22,31,3000), (30,40,10000), (31,40,15000), (40,null,null) 

we want create view returns this:

employeeid       approvalgoesto     lowerlimit    upperlimit ------------------------------------------------------------ 1                10                  0             99 1                20                  100           999 1                30                  1000          9999 1                40                  10000         null 2                11                  0             299 2                21                  300           1999 2                31                  2000          14999 2                40                  15000         null . . . 10               10                  0             100 10               20                  101           1000 10               30                  1001          10000 . . . 31               31                  0             15000 31               40                  15001         null 40               40                  0             null 

so, order order values (1, 8999) should go employeeid : 30.

all values positive, , query shouldn't bound levels of hierarchy.

we don't want use cursors, means, want strictly use set-based operations because performance important in case.

1. can done ctes? or joins? sub queries? nested queries?
2. any suggestions improve design?

please keep in mind need answer independent of levels of hierarchy.

recursive cte answer...

create  view orderapprovalhierachy    cte (         select  h1.employeeid,                  h1.parentid,                  h2.parentid parentsparent,                  h2.orderlimit           (select  *                    hierarchy n1                 union                 select  employeeid,                          employeeid parentid,                          orderlimit                    hierarchy n2) h1         left    join hierarchy h2                 on  h1.parentid = h2.employeeid         union           select  h1.employeeid,                  h1.parentsparent,                  h2.parentid parentsparent,                  h2.orderlimit            cte h1         join    hierarchy h2                 on  h1.parentsparent = h2.employeeid           h1.employeeid <> h1.parentid ),      prep (         select  row_number() on (partition employeeid order isnull(orderlimit,2147483647) asc) pid,                  employeeid,                  parentid approvalgoesto,                  orderlimit - 1 upperlimit,                  orderlimit nextlowerlimit            cte ) select  p1.employeeid,         p1.approvalgoesto,          isnull(p2.nextlowerlimit,0) lowerlimit,         p1.upperlimit    prep p1 left    join prep p2         on  p1.employeeid = p2.employeeid         , p1.pid = p2.pid + 1   isnull(p1.upperlimit,0) <> -1 ,     p1.approvalgoesto not null