AdventureWorks Database: Calculate latest weekly salary of employees
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
businessentityid|persontype|namestyle|title|firstname |middlename |lastname |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid |modifieddate |
----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+
1|EM |false | |Ken |J |Sánchez | | 0| |[XML] |92c4279f-1207-48a3-8448-4636514eb7e2|2009-01-07 00:00:00.000|
2|EM |false | |Terri |Lee |Duffy | | 1| |[XML] |d8763459-8aa8-47cc-aff7-c9079af79033|2008-01-24 00:00:00.000|
3|EM |false | |Roberto | |Tamburello | | 0| |[XML] |e1a2555e-0828-434b-a33b-6f38136a37de|2007-11-04 00:00:00.000|
4|EM |false | |Rob | |Walters | | 0| |[XML] |f2d7ce06-38b3-4357-805b-f4b6b71c01ff|2007-11-28 00:00:00.000|
5|EM |false |Ms. |Gail |A |Erickson | | 0| |[XML] |f3a3f6b4-ae3b-430c-a754-9f2231ba6fef|2007-12-30 00:00:00.000|
6|EM |false |Mr. |Jossef |H |Goldberg | | 0| |[XML] |0dea28fd-effe-482a-afd3-b7e8f199d56f|2013-12-16 00:00:00.000|
7|EM |false | |Dylan |A |Miller | | 2| |[XML] |c45e8ab8-01be-4b76-b215-820c8368181a|2009-02-01 00:00:00.000|
8|EM |false | |Diane |L |Margheim | | 0| |[XML] |a948e590-4a56-45a9-bc9a-160a1cc9d990|2008-12-22 00:00:00.000|
9|EM |false | |Gigi |N |Matthew | | 0| |[XML] |5fc28c0e-6d36-4252-9846-05caa0b1f6c5|2009-01-09 00:00:00.000|
-- more --
Sample table: HumanResources.EmployeePayHistory
businessentityid|ratechangedate |rate |payfrequency|modifieddate |
----------------+-----------------------+-------+------------+-----------------------+
1|2009-01-14 00:00:00.000| 125.5| 2|2014-06-30 00:00:00.000|
2|2008-01-31 00:00:00.000|63.4615| 2|2014-06-30 00:00:00.000|
3|2007-11-11 00:00:00.000|43.2692| 2|2014-06-30 00:00:00.000|
4|2007-12-05 00:00:00.000| 8.62| 2|2007-11-21 00:00:00.000|
4|2010-05-31 00:00:00.000| 23.72| 2|2010-05-16 00:00:00.000|
4|2011-12-15 00:00:00.000|29.8462| 2|2011-12-01 00:00:00.000|
5|2008-01-06 00:00:00.000|32.6923| 2|2014-06-30 00:00:00.000|
6|2008-01-24 00:00:00.000|32.6923| 2|2014-06-30 00:00:00.000|
7|2009-02-08 00:00:00.000|50.4808| 2|2014-06-30 00:00:00.000|
8|2008-12-29 00:00:00.000|40.8654| 2|2014-06-30 00:00:00.000|
-- more --
Sample Solution:
-- Selecting specific columns and performing calculations on them
SELECT
-- Casting the RateChangeDate column to VARCHAR(10) format and aliasing it as FromDate
CAST(hur.RateChangeDate as VARCHAR(10)) AS FromDate,
-- Concatenating the LastName, FirstName, and MiddleName columns and aliasing it as NameInFull
CONCAT(LastName, ', ', FirstName, ' ', MiddleName) AS NameInFull,
-- Multiplying the Rate column by 40 to calculate the SalaryInAWeek and selecting it as is
(40 * hur.Rate) AS SalaryInAWeek
-- Joining the Person.Person table with the HumanResources.EmployeePayHistory table based on specific conditions
FROM Person.Person AS pp
INNER JOIN HumanResources.EmployeePayHistory AS hur
ON hur.BusinessEntityID = pp.BusinessEntityID
-- Filtering the rows where RateChangeDate matches the maximum RateChangeDate for each employee
WHERE hur.RateChangeDate = (
SELECT MAX(RateChangeDate)
FROM HumanResources.EmployeePayHistory
WHERE BusinessEntityID = hur.BusinessEntityID
)
-- Ordering the result set by the concatenated name (NameInFull) in ascending order
ORDER BY NameInFull;
Explanation:
- The SELECT statement retrieves specific columns and performs calculations on them.
- CAST(hur.RateChangeDate as VARCHAR(10)) AS FromDate: Converts the RateChangeDate column to VARCHAR(10) format and renames it as FromDate.
- CONCAT(LastName, ', ', FirstName, ' ', MiddleName) AS NameInFull: Concatenates the LastName, FirstName, and MiddleName columns with appropriate delimiters and renames it as NameInFull.
- (40 * hur.Rate) AS SalaryInAWeek: Calculates the SalaryInAWeek by multiplying the Rate column by 40.
- The FROM clause specifies the tables from which the data is retrieved, with appropriate aliases (pp for Person.Person and hur for HumanResources.EmployeePayHistory).
- The INNER JOIN clause joins the Person.Person table with the HumanResources.EmployeePayHistory table based on the BusinessEntityID column.
- The WHERE clause filters the rows to include only those where the RateChangeDate matches the maximum RateChangeDate for each employee, determined using a subquery.
- The ORDER BY clause orders the result set by the concatenated name (NameInFull) in ascending order.
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.