AdventureWorks Database: Formated date and weekly salary calculation

SQL Query - AdventureWorks: Exercise-23 with Solution

23. From the following table write a query in SQL to retrieve the RateChangeDate, full name (first name, middle name and last name) and weekly salary (40 hours in a week) of employees. In the output the RateChangeDate should appears in date format. Sort the output in ascending order on NameInFull.

Sample table: HumanResources.EmployeePayHistory

Click to view Full table

Sample table: Person.Person

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      
    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: Number of contacts for each type and name.
Next: Calculate latest weekly salary of employees.