AdventureWorks Database: Formated date and weekly salary calculation
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 --
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 --
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| ...
Go to:
PREV : Number of contacts for each type and name.
NEXT : Calculate latest weekly salary of employees.
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.