w3resource

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.

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.

SQL: Tips of the Day

How to combine date from one field with time from another field - MS SQL Server

You can simply add the two.

  • if the Time part of your Date column is always zero
  • and the Date part of your Time column is also always zero (base date: January 1, 1900)

Adding them returns the correct result-

SELECT Combined = MyDate + MyTime FROM MyTable

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

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook