Joing based on field value

[RJL1]

I have a query with a Months field. I need to get the value from another table based on the value of the Months field on the main query.

Employee table has the Months field

EmpID       Months
161         48
600         140

benefits table has the MaxVacation

BenID       MaxCumAvail          MonthsEmp
1           121.54               0
2           162.31               120

I need to join EmpID 161 to BenID 1 since its Months are less than 120 but also join EmpID 600 to BenID 2 since its months are greater than 120

Any suggestions on this
Thanks

 

[imex]

Try something like that:

select
    e.EmpID,
    e.Months,
    (select top 1 MaxCumAvail 
     from Benefits as b 
     where b.MonthsEmp < e.Months
     order by b.MonthsEmp desc)
from Employee as e

I hope this helps.


Imoveis em Guarulhos