Hello,
I have a dynamic table containing employee information including employee ID, user ID, etc. The LAST_WITH column will only have one row populated with data that displays as “HERE.” The LAST_WITH column is dynamic. All EMP_IDs and USER_IDs are unique.
When the EMP_ID is ordered in ASC order, I’m looking to identify the employee with the LAST_WITH equal to “HERE,” then find the employee with the next highest EMP_ID and return their corresponding USER_ID such that the desired employee’s TYPE = ‘UX’, CUR = ‘Y’ and LAST_WITH is null.
If an employee with the LAST_WITH equal to “HERE” and also equates to the MAX(EMP_ID), then find the employee with the MIN(EMP_ID) and return their corresponding USER_ID such that the desired employee’s TYPE = ‘UX’, CUR = ‘Y’ and LAST_WITH is null. This in turn would "circle" back to the beginning.
The table name is called ASSIGNMENTS.
Below are my create table and insert scripts.
CREATE TABLE ASSIGNMENTS (EMP_ID NUMBER, USER_ID NUMBER, CUR VARCHAR(1), TYPE VARCHAR(2), LAST_WITH VARCHAR(4))
;
INSERT ALL
INTO ASSIGNMENTS(EMP_ID, USER_ID, CUR, TYPE, LAST_WITH)
VALUES (326, 8481, 'Y', 'UX', 'HERE')
INTO ASSIGNMENTS(EMP_ID, USER_ID, CUR, TYPE, LAST_WITH)
VALUES (333, 8963, 'Y', 'UX', '')
INTO ASSIGNMENTS(EMP_ID, USER_ID, CUR, TYPE, LAST_WITH)
VALUES (341, 8293, 'Y', 'UX', '')
INTO ASSIGNMENTS(EMP_ID, USER_ID, CUR, TYPE, LAST_WITH)
VALUES (356, 8335, 'Y', 'UX', '')
SELECT 1 FROM DUAL
;
Given the above information, below would be the desired output in this example.
USER_ID
8963
How would I go about achieving this result using SQL? I am using SQL Developer version 19.2.1.247.2212.