I am working on a query to pull in employee information, including who can approve the employee’s timecards. Our database includes who the employee reports to, but that person won’t necessarily be the one who can approve timecards. The data looks like this
| EENum | Position | Reports to |
|---|---|---|
| 1 | Laborer | 3 |
| 2 | Laborer | 3 |
| 3 | Foreman | 6 |
| 4 | Laborer | 5 |
| 5 | Foreman | 7 |
| 6 | Super | 8 |
| 7 | Super | 8 |
| 8 | PM | 8 |
In this example, only the “Super” and “PM” positions can approve timecards. Since employees 1 and 2 report to employee 3, who is a foreman and can’t approve timecards, I need a query that will look into employee 3’s data to see who they report to, in this case employee 6 who is a Super and can therefore approve their timecards. So the final output of the query would need to look like this:
| EENum | Approved By |
|---|---|
| 1 | 6 |
| 2 | 6 |
| 3 | 6 |
| 4 | 7 |
| 5 | 7 |
| 6 | 8 |
| 7 | 8 |
| 8 | 8 |
And while not common, there are instances where there could be a 3rd “layer”, so I can’t just do an “IF” statement to go up a single level. It needs to go up until it finds a “reports to” whose position allows them to approve timecards.
I’ve tried doing recursive searches, but they haven’t pulled the information in correctly, and unfortunately I don’t understand recursive searches well enough to explain what’s going on with the returned data.
I also would prefer to not use temporary tables, as multiple people will need to use this query and some of them don’t have the access level to create and delete temporary tables.
Grant Baugh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.