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

# 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
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              |
...
```

## Practice Online

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

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