I need to pass the records of a table, record by record, to another set of logic that will pull certain results based on various conditions.
In Table1 I have two cols. 1) StoreCode 2)JobCode. This table gets updated via another process. Typically no more than 10 records.
I need to take each record (each StoreCode and JobCode combination) and pull from say an Employees table to get a list of each employee that matches that StoreCode and JobCode.
The catch is this: In theory there should only be one record in the Employees table for each StoreCode and Jobe Code. If only one record does exist I want to pull 3 fields from the employess table (Fname, Lname, Rank).
If no records are found then I just want to return one record with NULL for each of those 3 fields.
If multiple records are found I want to return one record with the values 'Duplicates', NULL, NULL
In the end result I need a table that has the same number of records that were in Table1 with the results found in the conditional logic above.
Results table would look similar to this: