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.
Course Of The Month
7 days, 14 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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
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?
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?
[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
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
Featured Post
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…
Suggested Courses
Course of the Month7 days, 14 hours left to enroll
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.