Solved

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

Posted on 2013-12-20
4
382 Views
Last Modified: 2013-12-22
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?
0
Comment
Question by:Rivess
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
sameer2010 earned 500 total points
ID: 39733306
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
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 39733500
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
 

Author Closing Comment

by:Rivess
ID: 39735235
A simple and tricky piece of code.
0
 

Author Comment

by:Rivess
ID: 39735240
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now