﻿ 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|
...
```

## Practice Online

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## SQL: Tips of the Day

How to create a foreign key in SQL Server?

```create table question_bank
(
question_id uniqueidentifier primary key,
question_exam_id uniqueidentifier not null,
question_text varchar(1024) not null,
question_point_value decimal,
constraint fk_questionbank_exams foreign key (question_exam_id) references exams (exam_id)
);
```

Database: SQL Server

Ref : https://bit.ly/3sgS152