How to get most recent and oldest from the the joins to a child table

Question

Post reply

How to get most recent and oldest from the the joins to a child table

SQLCurious

SSCrazy

Points: 2339
More actions
July 23, 2014 at 12:37 pm

#288881

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

autoexcrement SSCertifiable Points: 5956 More actions · Answer 1

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

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

autoexcrement SSCertifiable Points: 5956 More actions · Answer 2

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.

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

autoexcrement SSCertifiable Points: 5956 More actions · Answer 3

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

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

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;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

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