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
389 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
query optimization 6 30
selective rebuild of SQL Tables in scheduled job 10 46
Change this SQL to get all nodes 3 54
get count of orders by customer Sql Server table. 3 46
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

739 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