w3resource

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


Click to view Full table

Sample table: HumanResources.EmployeePayHistory


Click to view Full table

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.



Follow us on Facebook and Twitter for latest update.