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

Question

We have a table which looks like this :

CREATE TABLE Hierarchy (EmployeeID int not null, parentID int, orderlimit int)
CREATE TABLE Order (EmployeeID int not null, OrderSize int)

It's supposed to tell you the approval range for each employee on orders, and we decided to keep the employee manager's ID, in the same record, because in case an order exceeds a person's limit, his/her manager (referred to by parentID) should approve it.

If it exceeds the upper level again, the order should go to the higher level and so on and on, until it reaches the level where it qualifies to approve the order. If parentID is null for one person, this means he is the highest level in management structure AND we don't know how many possible levels we might have, and if the highest level'

To clarify consider this

INSERT INTO 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 to create a view which returns something like 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, an order with ORDER VALUES (1, 8999) should go to employeeID : 30.

ALL values are positive, and the query shouldn't be bound to the levels of the hierarchy.

We don't want to use cursors, it means, we want to strictly use set-based operations because performance is very important in this case.

1. Can this be done with CTEs? Or simply joins? Sub queries? Nested queries?
2. Any suggestions to improve the design?

Please keep in mind that we need an answer independent of the levels of the hierarchy.

Answer 1

Recursive CTE answer...

Create  View OrderApprovalHierachy
As
With    cte As
(
        Select  h1.employeeID, 
                h1.parentID, 
                h2.parentID As parentsparent, 
                h2.orderlimit
        From   (Select  *
                From    hierarchy n1
                Union
                Select  employeeID, 
                        employeeID as parentID, 
                        orderlimit
                From    hierarchy n2) h1
        Left    Join hierarchy h2
                On  h1.parentID = h2.EmployeeID
        Union   All
        Select  h1.employeeID, 
                h1.parentsparent, 
                h2.parentID As parentsparent, 
                h2.orderlimit
        From    cte h1
        Join    hierarchy h2
                On  h1.parentsparent = h2.EmployeeID
        Where   h1.employeeID <> h1.parentID
),      prep As
(
        Select  Row_Number() Over (Partition By EmployeeID Order By IsNull(orderLimit,2147483647) Asc) As pID, 
                EmployeeID, 
                parentID As ApprovalGoesTo, 
                orderLimit - 1 As UpperLimit, 
                orderLimit As NextLowerLimit
        From    cte
)
Select  p1.employeeID,
        p1.ApprovalGoesTo, 
        IsNull(p2.NextLowerLimit,0) As LowerLimit,
        p1.UpperLimit
From    prep p1
Left    Join prep p2
        On  p1.EmployeeID = p2.EmployeeID
        And p1.pID = p2.pID + 1
Where   IsNull(p1.UpperLimit,0) <> -1
And     p1.ApprovalGoesTo Is Not Null