lookup value using ad-hoc

[RaeDar]

I need to lookup up a value based on a range to determine length accrual rate based on length of employment. Example below.

1st table; Person reflects 27 months length of service using code 1.

2nd table; code 1 has 3 records. If 168 minimum months of service they get rate A, if 84 minimum months of service they get rate B, ect.

Now we need to determine which rate by comparing the 27 months for code 1 to get the correct rate, how in ad-hoc?

Hope I make sence, thanks for your help!

 

[PHV]

Hope I make sence
Not really, at least for me.
Why not posting your actual tables schema, some input samples and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RaeDar]

We are using 3rd party software that allows us to create processing steps using Ad-hoc sql. This is the scenario;
Here's table 1: (Lookup table - *compound PK)
*Key *Key Rate minimum months of service
PTO1 .527 24
PTO1 .123 48
PTO1 .458 60
PTO2 .292 120
PTO2 .529 300

Table 2: (Employee table)
Employee has PTO1 code assigned and has been with the employer for 50 months, therefore the .123 Key Rate would apply.

How would I write a sql statement to acquire the .123 rate when there are 3 rates for the PTO code?

Thanks again for your help and efforts.

 

[PHV]

SELECT [Key Rate]
FROM [table 1] AS A INNER JOIN [table 2] AS B ON A.Key = B.Key
WHERE A.[minimum months of service] = (SELECT Max([minimum months of service] FROM [table 1] WHERE [Key] = B.Key AND [minimum months of service] < B.[number of months])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886