SQL Syntax across multiple fields
I have a payroll puzzle to solve. There are two tables involved in this puzzle. One has the payroll detail information and the second has the potential accounts that correspond to those records. In the sample dataset there are two employees(1000 and 1200). For each payroll record I need to obtain the ACCOUNT field value from the PAYACCTS table. The trick is the PAYACCTS table because there are 'Catch All' records. If no exact values match then the value on the catch all record is used. So the answers for each record are:
1-6000 because 025 matches 025,TEC matches TEC and HOURLY matches HOURLY
2-5000 because 030 matches nothing so it falls to ALL,TEC falls to ALL and HOURLY matches HOURLY
3-7 all go to 5000 for the same reason as 2
8-7000 because DEPTMENT,POSITION and PAYCODE all do not match. The 'Catch All' gets this one.
Essentially the comparison proceed from left to right. DEPTMENT first, then POSITION next and lastly PAYCODE.
Can this be with a single SQL script or should I make a function or stored procedure for this?
SAMPLEPAYROLL.txt
1-6000 because 025 matches 025,TEC matches TEC and HOURLY matches HOURLY
2-5000 because 030 matches nothing so it falls to ALL,TEC falls to ALL and HOURLY matches HOURLY
3-7 all go to 5000 for the same reason as 2
8-7000 because DEPTMENT,POSITION and PAYCODE all do not match. The 'Catch All' gets this one.
Essentially the comparison proceed from left to right. DEPTMENT first, then POSITION next and lastly PAYCODE.
Can this be with a single SQL script or should I make a function or stored procedure for this?
SAMPLEPAYROLL.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER