w3resource

AdventureWorks Database: Return the employee with the fewest number of vacation hours

SQL Query - AdventureWorks: Exercise-103 with Solution

103. From the following table write a query in SQL to return the employee with the fewest number of vacation hours compared to other employees with the same job title. Partitions the employees by job title and apply the first value to each partition independently.

Sample table: HumanResources.Employee


Click to view Full table

Sample table: Person.Person


Click to view Full table

Sample Solution:

SELECT JobTitle, LastName, VacationHours,
       FIRST_VALUE(LastName) OVER (PARTITION BY JobTitle
                                   ORDER BY VacationHours ASC
                                   ROWS UNBOUNDED PRECEDING
                                  ) AS FewestVacationHours
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS p
    ON e.BusinessEntityID = p.BusinessEntityID
ORDER BY JobTitle;

Sample Output:

jobtitle                                |lastname         |vacationhours|fewestvacationhours|
----------------------------------------+-----------------+-------------+-------------------+
Accountant                              |Moreland         |           58|Moreland           |
Accountant                              |Seamans          |           59|Moreland           |
Accounts Manager                        |Liu              |           57|Liu                |
Accounts Payable Specialist             |Tomic            |           63|Tomic              |
Accounts Payable Specialist             |Sheperdigian     |           64|Tomic              |
Accounts Receivable Specialist          |Poe              |           60|Poe                |
Accounts Receivable Specialist          |Spoon            |           61|Poe                |
Accounts Receivable Specialist          |Walton           |           62|Poe                |
Application Specialist                  |Bueno            |           71|Bueno              |
Application Specialist                  |Bacon            |           72|Bueno              |
Application Specialist                  |Meyyappan        |           73|Bueno              |
Application Specialist                  |Berg             |           74|Bueno              |
...

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Return the name of the product that is the least expensive.
Next: Return the difference in sales quotas for a specific employee over previous years.


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Convert Timestamp to date in MySQL Query:

DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'

Database: MySQL

Ref : https://bit.ly/3EJPnMQ