FWIW I was curious why my code didn't work so I snagged and slightly modified Lynn's code plugging in my select statement.

use tempdb

go

CREATE TABLE Table_1 (

EID INT,

Name VARCHAR(32),

Title VARCHAR(32),

Number CHAR(12)

);

CREATE TABLE Table_2 (

Date DATE,

Analyst_EID INT NULL,

Journey_EID INT NULL,

Accountant_EID INT NULL

);

INSERT INTO Table_1

( EID, Name, Title, Number )

VALUES ( 1, -- EID - int

'John', -- EmployeeName - varchar(32)

'Analyst', -- Title - varchar(32)

'xxx-xxx-xxxx' -- Number - char(12)

),

( 2, -- EID - int

'Sam', -- EmployeeName - varchar(32)

'Journey Man', -- Title - varchar(32)

'xxx-xxx-xxxx' -- Number - char(12)

),

( 3, -- EID - int

'Sandra', -- EmployeeName - varchar(32)

'Accountant', -- Title - varchar(32)

'xxx-xxx-xxxx' -- Number - char(12)

);

INSERT INTO Table_2

( Date ,

Analyst_EID ,

Journey_EID ,

Accountant_EID

)

VALUES ( '2012-02-01' , -- EmpDate - date

1 , -- AnalystEID - int

2 , -- JourneyEID - int

3 -- AccountantEID - int

),

( '2012-02-03' , -- EmpDate - date

NULL , -- AnalystEID - int

2 , -- JourneyEID - int

NULL -- AccountantEID - int

),

( '2012-02-04' , -- EmpDate - date

NULL , -- AnalystEID - int

NULL , -- JourneyEID - int

3 -- AccountantEID - int

),

( '2012-02-05' , -- EmpDate - date

1 , -- AnalystEID - int

2 , -- JourneyEID - int

3 -- AccountantEID - int

);

--SELECT * FROM Table_1;

--SELECT * FROM Table_2;

Select Table_2.Date, MAX(t1analyst.Name ), MAX( t1j.Name ), MAX(t1acct.Name )

From Table_2

left join Table_1 t1analyst on Table_2.Analyst_EID = t1analyst.EID

left join Table_1 t1j on Table_2.Journey_EID = t1j.EID

left join Table_1 t1acct on Table_2.Accountant_EID = t1acct.EID

group by Table_2.Date

Order by Table_2.Date ASC

DROP TABLE Table_1;

DROP TABLE Table_2;