CODEWITHSUNDEEP
sqlsql-server
jin cheng teo
jin cheng teo

Reputation: 305

SQL Join 3 Different Queries

I have an employee table below:

StaffName EffectiveDate FieldType FieldValue
Terry 1/1/1991 Title Instructor
Terry 1/1/1991 Department LM
Terry 1/1/1992 Title Senior Instructor
Owen 1/1/1990 Title Administrator
Owen 1/1/1990 Department LM

I tried the below query:

SELECT DISTINCT Employee.StaffName,  NameWithMaxDate.MaxDate
FROM Employee
INNER JOIN
(SELECT StaffName,
MAX(EffectiveDate) OVER (PARTITION BY StaffName) AS MaxDate
FROM Employee) AS NameWithMaxDate
ON Employee.StaffName = NameWithMaxDate.StaffName

I get the below result:

StaffName MaxDate
Owen 1/1/1990
Terry 1/1/1992

I have another query:

SELECT StaffName,FieldValue
FROM Employee
WHERE FieldType = 'Department'
AND FieldValue = 'LM'

The result is below:

StaffName FieldValue
Terry LM
Owen LM

I have one last query:

SELECT StaffName,FieldValue
FROM Employee
WHERE  FieldType = 'Title'

This is the result that I get:

StaffName FieldValue
Terry Instructor
Terry Senior Instructor
Owen Administrator

I am stuck on how to combine my 3 queries above to get the result which I desire as shown below:

EffectiveDate StaffName Title Department
1/1/1992 Terry Senior Instructor LM
1/1/1990 Owen Admin LM

Maybe my concepts of JOIN is not enough, or is there some other SQL functions that I have to use in this case?

Thank you

Upvotes: 0

Views: 51

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

The trick is to get the last record per StaffName and FieldType by Effective Date. We do this via the WITH TIES in concert with the window function row_number()

Then it becomes a small matter of a conditional aggregation

Example or dbFiddle

Select EffectiveDate=max(EffectiveDate)
      ,StaffName
      ,Title      = max(case when FieldType='Title' then FieldValue end)
      ,Department = max(case when FieldType='Department' then FieldValue end)
 From  (
        Select top 1 with ties * 
         From  YourTable
         Order By row_number() over (partition by StaffName,FieldType Order by EffectiveDate desc)
      ) A
 Group By StaffName

Results

EffectiveDate   StaffName   Title               Department
1990-01-01      Owen        Administrator       LM
1992-01-01      Terry       Senior Instructor   LM

Upvotes: 2

Related Questions