July 23, 2014 at 12:37 pm
Hello,
I have the following tables in my DB
Employee table - This table has EmployeeID, Name, DOB.
EmployeeDesignation table - 1 Employee can have many designations with each having an effective date. There is no flag to indicate which among multiples is the current entry. You can only figure it out by the newest/oldest EffectiveDate. I want to get the most recent and the oldest for each employee.
EmployeeSalaryHistory table - Structure/Design is similar to EmployeeDesignation table. I want to get the starting salary and current salary for each employee.
I want my query to output me the following fields...
EmployeeID
EmployeeName
EmployeeDOB
EmployeeStartingDesignation
EmployeeCurrentDesignation
EmployeeStartingSalary
EmployeeCurrentSalary
Here is a piece of code to generate sample data sets
DECLARE @Employee TABLE (EmployeeID INT, EmployeeName VARCHAR (100), EmployeeDOB DATE)
INSERT @Employee VALUES (101, 'James Bond', '07/07/1945'), (102, 'Tanned Tarzan', '12/13/1955'), (103, 'Dracula Transylvanian', '10/22/1967')
DECLARE @EmployeeDesignation TABLE (EmployeeID INT, Designation VARCHAR (100), EffectiveDate DATE)
INSERT @EmployeeDesignation VALUES (101, 'Bond Intern', '01/01/1970'), (101, 'Bond Trainee', '01/01/1975'), (101, 'Bond...James Bond', '01/01/1985')
INSERT @EmployeeDesignation VALUES (102, 'Baby in Animal Care', '01/01/1956'), (102, 'Survivor', '07/01/1966'), (102, 'King of the Jungle', '11/12/1975')
INSERT @EmployeeDesignation VALUES (103, 'Blood Drawing Trainee', '4/18/1985'), (103, 'Eternal Blood Sucker', '10/31/1990')
DECLARE @EmployeeSalaryHistory TABLE (EmployeeID INT, Salary MONEY, EffectiveDate DATE)
INSERT @EmployeeSalaryHistory VALUES (101, 5000.00, '01/01/1970'), (101, 25000.00, '11/21/1979'), (101, 49000.00, '6/01/1985')
INSERT @EmployeeSalaryHistory VALUES (102, 22000.00, '01/01/1956'), (102, 87450.00, '09/15/1965')
INSERT @EmployeeSalaryHistory VALUES (103, 14600.00, '4/18/1985'), (103, 23600.00, '09/01/1989'), (103, 143300.00, '2/21/1999')
Currently, I have a query to get this done which looks as below. Since I have more than 8K employees with each having multiple Designation and Salary entries, my query is taking forever.
selecte.EmployeeID, e.EmployeeName, e.EmployeeDOB,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate) EmployeeStartingDesignation,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate Desc) EmployeeCurrentDesignation,
(select top 1 Salary from @EmployeeSalaryHistory es where es.EmployeeID = e.EmployeeID Order By EffectiveDate) EmployeeStartingSalary,
(select top 1 Salary from @EmployeeSalaryHistory es where es.EmployeeID = e.EmployeeID Order By EffectiveDate Desc) EmployeeCurrentSalary
from@Employee e
Please advise the best possible query to fulfill my requirement.
Thanks,
SC
July 23, 2014 at 3:30 pm
Dunno if this is the fanciest way to do this, but here's a quick answer:
DECLARE @Employee TABLE (EmployeeID INT, EmployeeName VARCHAR (100), EmployeeDOB DATETIME)
INSERT INTO @Employee
SELECT 101, 'James Bond', '07/07/1945' UNION ALL
SELECT 102, 'Tanned Tarzan', '12/13/1955' UNION ALL
SELECT 103, 'Dracula Transylvanian', '10/22/1967'
DECLARE @EmployeeDesignation TABLE (EmployeeID INT, Designation VARCHAR (100), EffectiveDate DATETIME)
INSERT INTO @EmployeeDesignation
SELECT 101, 'Bond Intern', '01/01/1970' UNION ALL
SELECT 101, 'Bond Trainee', '01/01/1975' UNION ALL
SELECT 101, 'Bond...James Bond', '01/01/1985' UNION ALL
SELECT 102, 'Baby in Animal Care', '01/01/1956' UNION ALL
SELECT 102, 'Survivor', '07/01/1966' UNION ALL
SELECT 102, 'King of the Jungle', '11/12/1975' UNION ALL
SELECT 103, 'Blood Drawing Trainee', '4/18/1985' UNION ALL
SELECT 103, 'Eternal Blood Sucker', '10/31/1990'
DECLARE @EmployeeSalaryHistory TABLE (EmployeeID INT, Salary MONEY, EffectiveDate DATETIME)
INSERT INTO @EmployeeSalaryHistory
SELECT 101, 5000.00, '01/01/1970' UNION ALL
SELECT 101, 25000.00, '11/21/1979' UNION ALL
SELECT 101, 49000.00, '6/01/1985' UNION ALL
SELECT 102, 22000.00, '01/01/1956' UNION ALL
SELECT 102, 87450.00, '09/15/1965' UNION ALL
SELECT 103, 14600.00, '4/18/1985' UNION ALL
SELECT 103, 23600.00, '09/01/1989' UNION ALL
SELECT 103, 143300.00, '2/21/1999'
;WITH StartingDesignation AS
(
SELECT
Designation,
EmployeeID,
RN = ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY EffectiveDate)
FROM @EmployeeDesignation
),
CurrentDesignation AS
(
SELECT
Designation,
EmployeeID,
RN = ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY EffectiveDate DESC)
FROM @EmployeeDesignation
),
StartingSalary AS
(
SELECT
Salary,
EmployeeID,
RN = ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY EffectiveDate)
FROM @EmployeeSalaryHistory
),
CurrentSalary AS
(
SELECT
Salary,
EmployeeID,
RN = ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY EffectiveDate DESC)
FROM @EmployeeSalaryHistory
)
SELECT
E.EmployeeID,
E.EmployeeName,
E.EmployeeDOB,
SD.Designation AS EmployeeStartingDesignation,
CD.Designation AS EmployeeCurrentDesignation,
SS.Salary AS EmployeeStartingSalary,
CS.Salary AS EmployeeCurrentSalary
FROM @Employee AS E
INNER JOIN StartingDesignation AS SD ON
E.EmployeeID = SD.EmployeeID AND
SD.RN = 1
INNER JOIN CurrentDesignation AS CD ON
E.EmployeeID = CD.EmployeeID AND
CD.RN = 1
INNER JOIN StartingSalary AS SS ON
E.EmployeeID = SS.EmployeeID AND
SS.RN = 1
INNER JOIN CurrentSalary AS CS ON
E.EmployeeID = CS.EmployeeID AND
CS.RN = 1
July 23, 2014 at 4:01 pm
P.S. If you have any situations where a record in @Employee won't also exist at least once in the two other tables, you will want to use LEFT OUTER JOIN there instead of INNER JOIN.
July 23, 2014 at 4:13 pm
Here's another version that I guess might be a bit more efficient? Only uses 2 CTE's instead of 4.
DECLARE @Employee TABLE (EmployeeID INT, EmployeeName VARCHAR (100), EmployeeDOB DATETIME)
INSERT INTO @Employee
SELECT 101, 'James Bond', '07/07/1945' UNION ALL
SELECT 102, 'Tanned Tarzan', '12/13/1955' UNION ALL
SELECT 103, 'Dracula Transylvanian', '10/22/1967'
DECLARE @EmployeeDesignation TABLE (EmployeeID INT, Designation VARCHAR (100), EffectiveDate DATETIME)
INSERT INTO @EmployeeDesignation
SELECT 101, 'Bond Intern', '01/01/1970' UNION ALL
SELECT 101, 'Bond Trainee', '01/01/1975' UNION ALL
SELECT 101, 'Bond...James Bond', '01/01/1985' UNION ALL
SELECT 102, 'Baby in Animal Care', '01/01/1956' UNION ALL
SELECT 102, 'Survivor', '07/01/1966' UNION ALL
SELECT 102, 'King of the Jungle', '11/12/1975' UNION ALL
SELECT 103, 'Blood Drawing Trainee', '4/18/1985' UNION ALL
SELECT 103, 'Eternal Blood Sucker', '10/31/1990'
DECLARE @EmployeeSalaryHistory TABLE (EmployeeID INT, Salary MONEY, EffectiveDate DATETIME)
INSERT INTO @EmployeeSalaryHistory
SELECT 101, 5000.00, '01/01/1970' UNION ALL
SELECT 101, 25000.00, '11/21/1979' UNION ALL
SELECT 101, 49000.00, '6/01/1985' UNION ALL
SELECT 102, 22000.00, '01/01/1956' UNION ALL
SELECT 102, 87450.00, '09/15/1965' UNION ALL
SELECT 103, 14600.00, '4/18/1985' UNION ALL
SELECT 103, 23600.00, '09/01/1989' UNION ALL
SELECT 103, 143300.00, '2/21/1999'
;WITH Designation AS
(
SELECT
Designation,
EmployeeID,
StartingRN = ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY EffectiveDate),
CurrentRN = ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY EffectiveDate DESC)
FROM @EmployeeDesignation
),
Salary AS
(
SELECT
Salary,
EmployeeID,
StartingRN = ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY EffectiveDate),
CurrentRN = ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY EffectiveDate DESC)
FROM @EmployeeSalaryHistory
)
SELECT
E.EmployeeID,
E.EmployeeName,
E.EmployeeDOB,
SD.Designation AS EmployeeStartingDesignation,
CD.Designation AS EmployeeCurrentDesignation,
SS.Salary AS EmployeeStartingSalary,
CS.Salary AS EmployeeCurrentSalary
FROM @Employee AS E
INNER JOIN Designation AS SD ON
E.EmployeeID = SD.EmployeeID AND
SD.StartingRN = 1
INNER JOIN Designation AS CD ON
E.EmployeeID = CD.EmployeeID AND
CD.CurrentRN = 1
INNER JOIN Salary AS SS ON
E.EmployeeID = SS.EmployeeID AND
SS.StartingRN = 1
INNER JOIN Salary AS CS ON
E.EmployeeID = CS.EmployeeID AND
CS.CurrentRN = 1
July 23, 2014 at 4:29 pm
Here are 2 more options that you could test. I'm not sure if they're any better, but at least they read the tables just once. The second one should be faster as it's sorting on (theoretically) less rows.
WITH CTE AS(
SELECT e.EmployeeID,
e.EmployeeName,
e.EmployeeDOB,
ed.Designation,
ROW_NUMBER() OVER( PARTITION BY e.EmployeeID ORDER BY ed.EffectiveDate ASC) DesignationNoASC,
ROW_NUMBER() OVER( PARTITION BY e.EmployeeID ORDER BY ed.EffectiveDate DESC) DesignationNoDESC,
es.Salary,
ROW_NUMBER() OVER( PARTITION BY e.EmployeeID ORDER BY es.EffectiveDate ASC) SalaryNoASC,
ROW_NUMBER() OVER( PARTITION BY e.EmployeeID ORDER BY es.EffectiveDate DESC) SalaryNoDESC
FROM @Employee e
JOIN @EmployeeDesignation ed ON e.EmployeeID = ed.EmployeeID
JOIN @EmployeeSalaryHistory es ON e.EmployeeID = es.EmployeeID
)
SELECT EmployeeID,
EmployeeName,
EmployeeDOB,
MAX( CASE WHEN DesignationNoASC = 1 THEN Designation END) EmployeeStartingDesignation,
MAX( CASE WHEN DesignationNoDESC = 1 THEN Designation END) EmployeeCurrentDesignation,
MAX( CASE WHEN SalaryNoASC = 1 THEN Salary END) EmployeeStartingSalary,
MAX( CASE WHEN SalaryNoDESC = 1 THEN Salary END) EmployeeCurrentSalary
FROM CTE
GROUP BY EmployeeID,
EmployeeName,
EmployeeDOB;
WITH Designations AS(
SELECT EmployeeID,
Designation,
ROW_NUMBER() OVER( PARTITION BY EmployeeID ORDER BY EffectiveDate ASC) DesignationNoASC,
ROW_NUMBER() OVER( PARTITION BY EmployeeID ORDER BY EffectiveDate DESC) DesignationNoDESC
FROM @EmployeeDesignation
),
Salaries AS(
SELECT EmployeeID,
Salary,
ROW_NUMBER() OVER( PARTITION BY EmployeeID ORDER BY EffectiveDate ASC) SalaryNoASC,
ROW_NUMBER() OVER( PARTITION BY EmployeeID ORDER BY EffectiveDate DESC) SalaryNoDESC
FROM @EmployeeSalaryHistory
)
SELECT e.EmployeeID,
e.EmployeeName,
e.EmployeeDOB,
MAX( CASE WHEN DesignationNoASC = 1 THEN Designation END) EmployeeStartingDesignation,
MAX( CASE WHEN DesignationNoDESC = 1 THEN Designation END) EmployeeCurrentDesignation,
MAX( CASE WHEN SalaryNoASC = 1 THEN Salary END) EmployeeStartingSalary,
MAX( CASE WHEN SalaryNoDESC = 1 THEN Salary END) EmployeeCurrentSalary
FROM @Employee e
JOIN Designations d ON e.EmployeeID = d.EmployeeID
JOIN Salaries s ON e.EmployeeID = s.EmployeeID
WHERE (d.DesignationNoASC = 1 OR d.DesignationNoDESC = 1)
AND (s.SalaryNoASC = 1 OR s.SalaryNoDESC = 1)
GROUP BY e.EmployeeID,
e.EmployeeName,
e.EmployeeDOB;
July 26, 2014 at 5:52 am
Here is a very efficient Window Function method, one scan of each table.
😎
USE tempdb;
GO
DECLARE @Employee TABLE (EmployeeID INT, EmployeeName VARCHAR (100), EmployeeDOB DATE)
INSERT @Employee VALUES (101, 'James Bond', '07/07/1945'), (102, 'Tanned Tarzan', '12/13/1955'), (103, 'Dracula Transylvanian', '10/22/1967')
DECLARE @EmployeeDesignation TABLE (EmployeeID INT, Designation VARCHAR (100), EffectiveDate DATE)
INSERT @EmployeeDesignation VALUES (101, 'Bond Intern', '01/01/1970'), (101, 'Bond Trainee', '01/01/1975'), (101, 'Bond...James Bond', '01/01/1985')
INSERT @EmployeeDesignation VALUES (102, 'Baby in Animal Care', '01/01/1956'), (102, 'Survivor', '07/01/1966'), (102, 'King of the Jungle', '11/12/1975')
INSERT @EmployeeDesignation VALUES (103, 'Blood Drawing Trainee', '4/18/1985'), (103, 'Eternal Blood Sucker', '10/31/1990')
DECLARE @EmployeeSalaryHistory TABLE (EmployeeID INT, Salary MONEY, EffectiveDate DATE)
INSERT @EmployeeSalaryHistory VALUES (101, 5000.00, '01/01/1970'), (101, 25000.00, '11/21/1979'), (101, 49000.00, '6/01/1985')
INSERT @EmployeeSalaryHistory VALUES (102, 22000.00, '01/01/1956'), (102, 87450.00, '09/15/1965')
INSERT @EmployeeSalaryHistory VALUES (103, 14600.00, '4/18/1985'), (103, 23600.00, '09/01/1989'), (103, 143300.00, '2/21/1999')
;WITH EMP_DATA AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY EM.EmployeeID
ORDER BY (SELECT NULL)
) AS EM_RID
,EM.EmployeeID
,EM.EmployeeName
,FIRST_VALUE(ED.Designation) OVER
(
PARTITION BY EM.EmployeeID
ORDER BY ED.EffectiveDate
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS EmployeeStartingDesignation
,LAST_VALUE(ED.Designation) OVER
(
PARTITION BY EM.EmployeeID
ORDER BY ED.EffectiveDate
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS EmployeeCurrentDesignation
,FIRST_VALUE(ES.Salary) OVER
(
PARTITION BY EM.EmployeeID
ORDER BY ES.EffectiveDate
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS EmployeeStartingSalary
,LAST_VALUE(ES.Salary) OVER
(
PARTITION BY EM.EmployeeID
ORDER BY ES.EffectiveDate
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS EmployeeCurrentSalary
FROM @Employee EM
INNER JOIN @EmployeeDesignation ED
ON EM.EmployeeID = ED.EmployeeID
INNER JOIN @EmployeeSalaryHistory ES
ON ED.EmployeeID = ES.EmployeeID
)
SELECT
ED.EmployeeID
,ED.EmployeeName
,ED.EmployeeStartingDesignation
,ED.EmployeeCurrentDesignation
,ED.EmployeeStartingSalary
,ED.EmployeeCurrentSalary
FROM EMP_DATA ED
WHERE ED.EM_RID = 1;
Results
EmployeeID EmployeeName EmployeeStartingDesignation EmployeeCurrentDesignation EmployeeStartingSalary EmployeeCurrentSalary
----------- ---------------------- ---------------------------- --------------------------- ---------------------- ---------------------
101 James Bond Bond Intern Bond...James Bond 5000.00 49000.00
102 Tanned Tarzan Baby in Animal Care King of the Jungle 22000.00 87450.00
103 Dracula Transylvanian Blood Drawing Trainee Eternal Blood Sucker 14600.00 143300.00
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply