How to get one record from a range in an sql view based on the value of a field in another table

Hi there:

I have an Employees table with the fields called EmployeeName and YearsOfService. I have another table called Bonus that contains the fields: MinimumYears and BonusAmt. I want to create a view that displays the EmployeeName, YearsOfService and BonusAmt (one record per employee).

The Bonus table has the values:
MinimumYears | Bonus
0 | 0
2 | 1000
4 | 2800
6 | 4000

Two records in the Employees table may read:
EmployeeName | YearsOfService
Barry Blue | 2.65
Wayne Brown | 4.80
Greg White |1.94

The final view should read:
EmployeeName | YearsOfService | BonusAmt
Barry Blue | 2.65 | 1000
Wayne Brown | 4.80 | 2800
Greg White |1.94 | 0

So, what would be the SQL statement to get the required results?
RivessAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sameer2010Connect With a Mentor Commented:
Try this
-- If you want view, then only include create view
create view bonusview as 
select a.EmployeeName,a.YearsOfService,(select max(b.bonus) from Bonus_Table b where b.MinimumYears < a.YearsOfService) BonusAmt 
from Employees_Table a

Open in new window

0
 
Easwaran ParamasivamCommented:
You could use below one as well.

create view bonusview as 

select
e.EmployeeName,e.YearsOfService, b.bonus
from
Employees_Table e
inner join  Bonus_Table  b on b.MinimumYears =  Floor(e.YearsOfService)

Open in new window

0
 
RivessAuthor Commented:
A simple and tricky piece of code.
0
 
RivessAuthor Commented:
The second solution requires an entry for each possible year in the Bonus table. In the current setup an employee with 3 years won't get any bonus at all since the 'Floor(X)' is trying to match a 3 in the bonus table and an entry with 3 years does not exist. Thanks for your prompt reply.
0
All Courses

From novice to tech pro — start learning today.