AdventureWorks Database: Calculate latest weekly salary of employees
SQL Query - AdventureWorks: Exercise-24 with Solution
24. From the following tables write a query in SQL to calculate and display the latest weekly salary of each employee. Return RateChangeDate, full name (first name, middle name and last name) and weekly salary (40 hours in a week) of employees Sort the output in ascending order on NameInFull.
Sample table: Person.Person
Sample table: HumanResources.EmployeePayHistory
Sample Solution:
SELECT CAST(hur.RateChangeDate as VARCHAR(10) ) AS FromDate
, CONCAT(LastName, ', ', FirstName, ' ', MiddleName) AS NameInFull
, (40 * hur.Rate) AS SalaryInAWeek
FROM Person.Person AS pp
INNER JOIN HumanResources.EmployeePayHistory AS hur
ON hur.BusinessEntityID = pp.BusinessEntityID
WHERE hur.RateChangeDate = (SELECT MAX(RateChangeDate)
FROM HumanResources.EmployeePayHistory
WHERE BusinessEntityID = hur.BusinessEntityID)
ORDER BY NameInFull;
Sample Output:
fromdate |nameinfull |salaryinaweek| ----------+-------------------------------+-------------+ 2013-03-14|Abbas, Syed E | 1924.040| 2010-01-16|Abercrombie, Kim B | 498.00| 2009-02-28|Abolrous, Hazem E | 1153.8480| 2009-01-02|Ackerman, Pilar G | 769.2320| 2009-03-05|Adams, Jay G | 498.00| 2009-01-17|Ajenstat, François P | 1538.4600| 2012-04-16|Alberts, Amy E | 1924.040| 2008-12-02|Alderson, Greg F | 400| 2008-12-28|Alexander, Sean P | 423.0760| 2009-12-02|Altman, Gary E. | 961.5400| 2009-01-02|Anderson, Nancy A | 498.00| 2011-05-31|Ansman-Wolfe, Pamela O | 923.0760| 2009-01-04|Arifin, Zainal T | 711.5400| 2009-01-11|Bacon, Dan K | 1096.1520| 2009-01-21|Baker, Bryan | 498.00| 2009-12-25|Baker, Mary R | 538.00| 2009-01-20|Barbariol, Angela W | 440| ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Formated date and weekly salary calculation.
Next: Find sum, avg, count, min, max OrderQty.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join