AdventureWorks Database: Fetch hiredate of last employee in a department for a salary
106. From the following tables, write a query in SQL to return the department, last name, rate, hire date, and the last hire date within each department partitioned by rate. The query should retrieve data for specific departments ('Information Services' and 'Document Control').
Sample table: HumanResources.vEmployeeDepartmentHistory
businessentityid|title|firstname |middlename |lastname |suffix|shift |department |groupname |startdate |enddate |
----------------+-----+-----------+----------------+-----------------+------+-------+--------------------------+------------------------------------+----------+----------+
1| |Ken |J |Sánchez | |Day |Executive |Executive General and Administration|2009-01-14| |
2| |Terri |Lee |Duffy | |Day |Engineering |Research and Development |2008-01-31| |
3| |Roberto | |Tamburello | |Day |Engineering |Research and Development |2007-11-11| |
4| |Rob | |Walters | |Day |Engineering |Research and Development |2007-12-05|2010-05-30|
4| |Rob | |Walters | |Day |Tool Design |Research and Development |2010-05-31| |
5|Ms. |Gail |A |Erickson | |Day |Engineering |Research and Development |2008-01-06| |
6|Mr. |Jossef |H |Goldberg | |Day |Engineering |Research and Development |2008-01-24| |
7| |Dylan |A |Miller | |Day |Research and Development |Research and Development |2009-02-08| |
8| |Diane |L |Margheim | |Day |Research and Development |Research and Development |2008-12-29| |
9| |Gigi |N |Matthew | |Day |Research and Development |Research and Development |2009-01-16| |
10| |Michael | |Raheem | |Day |Research and Development |Research and Development |2009-05-03| |
-- more --
Sample table: HumanResources.EmployeePayHistory
businessentityid|ratechangedate |rate |payfrequency|modifieddate |
----------------+-----------------------+-------+------------+-----------------------+
1|2009-01-14 00:00:00.000| 125.5| 2|2014-06-30 00:00:00.000|
2|2008-01-31 00:00:00.000|63.4615| 2|2014-06-30 00:00:00.000|
3|2007-11-11 00:00:00.000|43.2692| 2|2014-06-30 00:00:00.000|
4|2007-12-05 00:00:00.000| 8.62| 2|2007-11-21 00:00:00.000|
4|2010-05-31 00:00:00.000| 23.72| 2|2010-05-16 00:00:00.000|
4|2011-12-15 00:00:00.000|29.8462| 2|2011-12-01 00:00:00.000|
5|2008-01-06 00:00:00.000|32.6923| 2|2014-06-30 00:00:00.000|
6|2008-01-24 00:00:00.000|32.6923| 2|2014-06-30 00:00:00.000|
7|2009-02-08 00:00:00.000|50.4808| 2|2014-06-30 00:00:00.000|
8|2008-12-29 00:00:00.000|40.8654| 2|2014-06-30 00:00:00.000|
-- more --
Sample table: HumanResources.Employee
businessentityid|nationalidnumber|loginid |jobtitle |birthdate |maritalstatus|gender|hiredate |salariedflag|vacationhours|sickleavehours|currentflag|rowguid |modifieddate |organizationnode|
----------------+----------------+----------------------------+----------------------------------------+----------+-------------+------+----------+------------+-------------+--------------+-----------+------------------------------------+-----------------------+----------------+
1|295847284 |adventure-works\ken0 |Chief Executive Officer |1969-01-29|S |M |2009-01-14|true | 99| 69|true |f01251e5-96a3-448d-981e-0f99d789110d|2014-06-30 00:00:00.000|/ |
2|245797967 |adventure-works\terri0 |Vice President of Engineering |1971-08-01|S |F |2008-01-31|true | 1| 20|true |45e8f437-670d-4409-93cb-f9424a40d6ee|2014-06-30 00:00:00.000|/1/ |
3|509647174 |adventure-works\roberto0 |Engineering Manager |1974-11-12|M |M |2007-11-11|true | 2| 21|true |9bbbfb2c-efbb-4217-9ab7-f97689328841|2014-06-30 00:00:00.000|/1/1/ |
4|112457891 |adventure-works\rob0 |Senior Tool Designer |1974-12-23|S |M |2007-12-05|false | 48| 80|true |59747955-87b8-443f-8ed4-f8ad3afdf3a9|2014-06-30 00:00:00.000|/1/1/1/ |
5|695256908 |adventure-works\gail0 |Design Engineer |1952-09-27|M |F |2008-01-06|true | 5| 22|true |ec84ae09-f9b8-4a15-b4a9-6ccbab919b08|2014-06-30 00:00:00.000|/1/1/2/ |
6|998320692 |adventure-works\jossef0 |Design Engineer |1959-03-11|M |M |2008-01-24|true | 6| 23|true |e39056f1-9cd5-478d-8945-14aca7fbdcdd|2014-06-30 00:00:00.000|/1/1/3/ |
7|134969118 |adventure-works\dylan0 |Research and Development Manager |1987-02-24|M |M |2009-02-08|true | 61| 50|true |4f46deca-ef01-41fd-9829-0adab368e431|2014-06-30 00:00:00.000|/1/1/4/ |
8|811994146 |adventure-works\diane1 |Research and Development Engineer |1986-06-05|S |F |2008-12-29|true | 62| 51|true |31112635-663b-4018-b4a2-a685c0bf48a4|2014-06-30 00:00:00.000|/1/1/4/1/ |
9|658797903 |adventure-works\gigi0 |Research and Development Engineer |1979-01-21|M |F |2009-01-16|true | 63| 51|true |50b6cdc6-7570-47ef-9570-48a64b5f2ecf|2014-06-30 00:00:00.000|/1/1/4/2/ |
10|879342154 |adventure-works\michael6 |Research and Development Manager |1984-11-30|M |M |2009-05-03|true | 16| 64|true |eaa43680-5571-40cb-ab1a-3bf68f04459e|2014-06-30 00:00:00.000|/1/1/4/3/ |
-- more --
Sample Solution:
-- Selecting columns for department, last name, rate, hire date, and the last hire date value within each department partitioned by rate
SELECT Department
, LastName
, Rate
, HireDate
, LAST_VALUE(HireDate) OVER (
PARTITION BY Department ORDER BY Rate
) AS LastValue
-- From the vEmployeeDepartmentHistory view in the HumanResources schema
FROM HumanResources.vEmployeeDepartmentHistory AS edh
-- Joining with the EmployeePayHistory table to get pay history data
INNER JOIN HumanResources.EmployeePayHistory AS eph
ON eph.BusinessEntityID = edh.BusinessEntityID
-- Joining with the Employee table to get employee data
INNER JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = edh.BusinessEntityID
-- Filtering data for specified departments
WHERE Department IN ('Information Services', 'Document Control');
Explanation:
- This SQL query retrieves data related to employees' departments, last names, rates, hire dates, and the last hire date value within each department partitioned by rate.
- The SELECT clause specifies the columns to be retrieved, including the result of the window function LAST_VALUE.
- The FROM clause indicates the source view HumanResources.vEmployeeDepartmentHistory from which the data will be retrieved.
- Inner joins with the EmployeePayHistory and Employee tables are used to gather additional employee-related information.
- The WHERE clause filters the data to include only specified departments ('Information Services' and 'Document Control').
- The OVER clause with the LAST_VALUE function calculates the last hire date value (LastValue) within each department partitioned by Department and ordered by Rate.
Sample Output:
department |lastname |rate |hiredate |lastvalue | --------------------+-------------+-------+----------+----------+ Document Control |Chai | 10.25|2009-01-22|2009-02-09| Document Control |Berge | 10.25|2009-02-09|2009-02-09| Document Control |Kharatishvili|16.8269|2008-12-16|2009-03-06| Document Control |Norred |16.8269|2009-03-06|2009-03-06| Document Control |Arifin |17.7885|2009-01-04|2009-01-04| Information Services|Bueno |27.4038|2008-12-23|2009-01-11| Information Services|Berg |27.4038|2009-02-16|2009-01-11| Information Services|Meyyappan |27.4038|2009-02-03|2009-01-11| Information Services|Bacon |27.4038|2009-01-11|2009-01-11| Information Services|Sharma |32.4519|2008-12-04|2009-02-23| ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Return the difference in sales quotas for a specific employee over previous years.
Next: Find the sales quota difference between the current and the first and last quarter.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.