﻿ AdventureWorks: Calculate latest weekly salary of employees

# 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
WHERE hur.RateChangeDate = (SELECT MAX(RateChangeDate)
FROM HumanResources.EmployeePayHistory
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-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|
...
```