Link to home
Create AccountLog in
Avatar of d1cjm1ex
d1cjm1ex

asked on

TSql coding for conditional results

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:  

User generated image
ASKER CERTIFIED SOLUTION
Avatar of Rainer Jeschor
Rainer Jeschor
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of d1cjm1ex
d1cjm1ex

ASKER

works as advertised.  Thanks.  I have a logic twist I need to add but will post that as another question as you did answer this specific question.  Hopefully you can look at it as I want to keep the code you sent.