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

Click to view Full table

Sample table: Person.Person

Click to view Full table

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.