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
Sample table: Person.Person
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.
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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
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