This one's gonna be a bit tough to properly explain, so if there's anything I can provide to help clarify, please let me know. See the attached file for the test code. The situation I'm trying to solve is the following:
I've constructed a hierarchical structure where there are essentially two types of elements: Users and Seats. In this structure, a particular entry can be one of these two types. The entry can also have a tree associated, in the case that it is a Seat - a Seat is occupied by either a User or a Seat, and for a specific term.
My objective is to be able to query this table with a specific User ID, and return all entries which match that User ID. Another criteria is that, in the event that the User is in that table because it is occupying a Seat, the current date must lie between the Start and End date of the term.
This is simple enough if we were to restrict the term check to just the leaf User entry. The issue is that the entire tree's term must also pass the date check.
In the sample data structure I've constructed, the final query, where I'm passing the User ID 1 to get my output, returns the /1/ entry, as well as the /5/1/1/ entry (along with its tree, only for demo purposes). My objective is to construct the query such that the second entry is not included in the result set, due to the fact that, despite being nominally eligible due to the /5/1/1/ record having UserID 1 and a term with today's date between Start and End date of the term, the /5/1/ record's Start and End date do not. IE, the result set should just contain Record ID 1, and not Record ID 9.