Hereâs the scenario: I have two tables, Positions and Assignments. Positions is the full inventory of available positions in my organization. Assignments where organization members are tied to their assigned position. Assignments may have a status of either âQueuedâ, âCurrentâ, or âClosedâ. The business rule is that we cannot assign positions that are in either a Queued or Current status.
I am trying to build a report that will display all positions available for assignment. I essentially need to do the Corteza equivalent of this:
SELECT * FROM positions
WHERE recordID NOT IN (SELECT positionID FROM assignments WHERE assignmentStatus = âCurrentâ OR assignmentStatus = âQueuedâ)
Assistance appreciated.