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
businessentityid|ratechangedate         |rate   |payfrequency|modifieddate           |
----------------+-----------------------+-------+------------+-----------------------+
               1|2009-01-14 00:00:00.000|  125.5|           2|2014-06-30 00:00:00.000|
               2|2008-01-31 00:00:00.000|63.4615|           2|2014-06-30 00:00:00.000|
               3|2007-11-11 00:00:00.000|43.2692|           2|2014-06-30 00:00:00.000|
               4|2007-12-05 00:00:00.000|   8.62|           2|2007-11-21 00:00:00.000|
               4|2010-05-31 00:00:00.000|  23.72|           2|2010-05-16 00:00:00.000|
               4|2011-12-15 00:00:00.000|29.8462|           2|2011-12-01 00:00:00.000|
               5|2008-01-06 00:00:00.000|32.6923|           2|2014-06-30 00:00:00.000|
               6|2008-01-24 00:00:00.000|32.6923|           2|2014-06-30 00:00:00.000|
               7|2009-02-08 00:00:00.000|50.4808|           2|2014-06-30 00:00:00.000|
               8|2008-12-29 00:00:00.000|40.8654|           2|2014-06-30 00:00:00.000|
			   -- more --

Click to view Full table

Sample table: Person.Person
businessentityid|persontype|namestyle|title|firstname               |middlename      |lastname              |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid                             |modifieddate           |
----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+
               1|EM        |false    |     |Ken                     |J               |Sánchez               |      |             0|                     |[XML]       |92c4279f-1207-48a3-8448-4636514eb7e2|2009-01-07 00:00:00.000|
               2|EM        |false    |     |Terri                   |Lee             |Duffy                 |      |             1|                     |[XML]       |d8763459-8aa8-47cc-aff7-c9079af79033|2008-01-24 00:00:00.000|
               3|EM        |false    |     |Roberto                 |                |Tamburello            |      |             0|                     |[XML]       |e1a2555e-0828-434b-a33b-6f38136a37de|2007-11-04 00:00:00.000|
               4|EM        |false    |     |Rob                     |                |Walters               |      |             0|                     |[XML]       |f2d7ce06-38b3-4357-805b-f4b6b71c01ff|2007-11-28 00:00:00.000|
               5|EM        |false    |Ms.  |Gail                    |A               |Erickson              |      |             0|                     |[XML]       |f3a3f6b4-ae3b-430c-a754-9f2231ba6fef|2007-12-30 00:00:00.000|
               6|EM        |false    |Mr.  |Jossef                  |H               |Goldberg              |      |             0|                     |[XML]       |0dea28fd-effe-482a-afd3-b7e8f199d56f|2013-12-16 00:00:00.000|
               7|EM        |false    |     |Dylan                   |A               |Miller                |      |             2|                     |[XML]       |c45e8ab8-01be-4b76-b215-820c8368181a|2009-02-01 00:00:00.000|
               8|EM        |false    |     |Diane                   |L               |Margheim              |      |             0|                     |[XML]       |a948e590-4a56-45a9-bc9a-160a1cc9d990|2008-12-22 00:00:00.000|
               9|EM        |false    |     |Gigi                    |N               |Matthew               |      |             0|                     |[XML]       |5fc28c0e-6d36-4252-9846-05caa0b1f6c5|2009-01-09 00:00:00.000|
			   -- more --

Click to view Full table

Sample Solution:

-- Selecting specific columns and performing calculations on them
SELECT 
    -- Casting the RateChangeDate column to VARCHAR(10) format and aliasing it as FromDate
    CAST(hur.RateChangeDate as VARCHAR(10)) AS FromDate,
    -- Concatenating the LastName, FirstName, and MiddleName columns and aliasing it as NameInFull
    CONCAT(LastName, ', ', FirstName, ' ', MiddleName) AS NameInFull,
    -- Multiplying the Rate column by 40 to calculate the SalaryInAWeek and selecting it as is
    (40 * hur.Rate) AS SalaryInAWeek
-- Joining the Person.Person table with the HumanResources.EmployeePayHistory table based on specific conditions
FROM Person.Person AS pp
    INNER JOIN HumanResources.EmployeePayHistory AS hur
        ON hur.BusinessEntityID = pp.BusinessEntityID
-- Ordering the result set by the concatenated name (NameInFull) in ascending order
ORDER BY NameInFull;

Explanation:

  • The SELECT statement retrieves specific columns and performs calculations on them.
  • CAST(hur.RateChangeDate as VARCHAR(10)) AS FromDate: Converts the RateChangeDate column to VARCHAR(10) format and renames it as FromDate.
  • CONCAT(LastName, ', ', FirstName, ' ', MiddleName) AS NameInFull: Concatenates the LastName, FirstName, and MiddleName columns with appropriate delimiters and renames it as NameInFull.
  • (40 * hur.Rate) AS SalaryInAWeek: Calculates the SalaryInAWeek by multiplying the Rate column by 40.
  • The FROM clause specifies the tables from which the data is retrieved, with appropriate aliases (pp for Person.Person and hur for HumanResources.EmployeePayHistory).
  • The INNER JOIN clause joins the Person.Person table with the HumanResources.EmployeePayHistory table based on the BusinessEntityID column.
  • The ORDER BY clause orders the result set by the concatenated name (NameInFull) in ascending order.

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.