AdventureWorks Database: Add vacation and sick time to find total hours away from work
SQL Query - AdventureWorks: Exercise-138 with Solution
138. From the following table write a query in SQL to find a total number of hours away from work can be calculated by adding vacation time and sick leave. Sort results ascending by Total Hours Away.
Sample table: HumanResources.Employee
Sample table: Person.Person
Sample Solution:
SELECT p.FirstName, p.LastName, VacationHours, SickLeaveHours,
VacationHours + SickLeaveHours AS "Total Hours Away"
FROM HumanResources.Employee AS e
JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
ORDER BY "Total Hours Away" ASC;
Sample Output:
firstname |lastname |vacationhours|sickleavehours|Total Hours Away| -----------+-----------------+-------------+--------------+----------------+ Hung-Fu |Ting | 0| 20| 20| Mandar |Samant | 0| 20| 20| Laura |Norman | 0| 20| 20| Min |Su | 1| 20| 21| Terri |Duffy | 1| 20| 21| Jim |Scardelis | 1| 20| 21| Krishna |Sunkammurali | 2| 21| 23| Nuan |Yu | 2| 21| 23| Roberto |Tamburello | 2| 21| 23| Olinda |Turner | 3| 21| 24| Houman |Pournasseh | 3| 21| 24| Michael |Sullivan | 3| 21| 24| ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Combine ProductModelID and Name columns from two tables.
Next: Tax difference between highest and lowest-taxed state or province.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- 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