I have a table that I need to get some specific data from for a view. Here's the base table structure with some sample data:
| UserID | ReportsToUserID | Org ID |
-------------------------------------
| 1 | NULL | 1 |
-------------------------------------
| 2 | 1 | 1 |
-------------------------------------
| 3 | 2 | 1 |
-------------------------------------
| 4 | 3 | 1 |
-------------------------------------
The users will be entering reports and users can see the reports of users who report to them and any users who report to those users. Users who report to no one can see everything in their organization Given my sample data above, user 1 can see the reports of 2, 3, & 4; user 2 can see the reports of 3 & 4; and user 3 can see the reports of 4.
For the view, I'd like to have the data returned as follows:
| UserID | CanSeeUserID | OrgID |
--------------------------------------------
| 1 | 2 | 1 |
--------------------------------------------
| 1 | 3 | 1 |
--------------------------------------------
| 1 | 4 | 1 |
--------------------------------------------
| 2 | 3 | 1 |
--------------------------------------------
etc...
Below is my current code, any help is greatly appreciated.
WITH CTEUsers (UserID, CanSeeUserID, OrgID)
AS
(
SELECT e.ID, e.ReportsToUserID, e.OrgID
FROM Users e WITH(NOLOCK)
WHERE COALESCE(ReportsToUserID,0) = 0 --ReportsToUserID can be NULL or 0
UNION ALL
SELECT e.ReportsToUserID, e.ID,e.OrgID
FROM Users e WITH(NOLOCK)
JOIN CTEUsers c
ON e.ID = c.UserID
)
SELECT * FROM CTEUsers
