You can avoid 2 of the 3 table scans of Table1 with a brute force "uncrosstab" (unpivot) followed by a cross tab (pivot). I haven't tested it for performance but the execution plan looks pretty good.

I converted the test data to a 2005 compatible format and wrote the code for 2005 and up just in-case folks with 2005 would want to do this. Since I'm doing all of this on a 2008 box, I can assure you it will also work in 2008.

--============================================================================================================

-- Create and populate the test tables. This is NOT a part of the solution.

-- I did, however,add the expected PK's which also create indexes.

--============================================================================================================

DECLARE @Table1 TABLE

(

EID INT NOT NULL PRIMARY KEY CLUSTERED,

EmployeeName VARCHAR(32) NOT NULL,

Title VARCHAR(32) NOT NULL,

Number CHAR(12) NOT NULL

)

;

DECLARE @Table2 TABLE

(

Date DATETIME NOT NULL PRIMARY KEY CLUSTERED,

AnalystEID INT NULL,

JourneyEID INT NULL,

AccountantEID INT NULL

)

;

INSERT INTO @Table1

(EID, EmployeeName, Title, Number)

SELECT 1,'John' ,'Analyst' ,'xxx-xxx-xxxx' UNION ALL

SELECT 2,'Sam' ,'Journey Man','xxx-xxx-xxxx 'UNION ALL

SELECT 3,'Sandra','Accountant' ,'xxx-xxx-xxxx'

;

INSERT INTO @Table2

(Date, AnalystEID, JourneyEID, AccountantEID)

SELECT '2012-02-01', 1 , 2 , 3 UNION ALL

SELECT '2012-02-03', NULL, 2 , NULL UNION ALL

SELECT '2012-02-04', NULL, NULL, 3 UNION ALL

SELECT '2012-02-05', 1 , 2 , 3

;

--============================================================================================================

-- Solve the problem only 1 scan of each table.

--============================================================================================================

SELECT Date = CONVERT(CHAR(10),t2.Date,101),

AnalystEID = MAX(CASE WHEN ca.AttributeName = 'AnalystEID' THEN t1.EmployeeName ELSE '' END),

JourneyEID = MAX(CASE WHEN ca.AttributeName = 'JourneyEID' THEN t1.EmployeeName ELSE '' END),

AcccountantEID = MAX(CASE WHEN ca.AttributeName = 'AcccountantEID' THEN t1.EmployeeName ELSE '' END)

FROM @Table2 t2

CROSS APPLY (SELECT 'AnalystEID' ,AnalystEID UNION ALL

SELECT 'JourneyEID' ,JourneyEID UNION ALL

SELECT 'AcccountantEID',AccountantEID) ca (AttributeName,AttributeValue)

JOIN @Table1 t1 ON t1.EID = ca.AttributeValue

GROUP BY t2.Date

ORDER BY t2.Date

;