The first part all the way to "Left Outer Join" works great!
But now what I'm trying to do is take the RC.Position from the first part and use that result in the second part to get info from the correct field.
Thus if RC.Position = RR, I need to pull the field RR_Assigned from the same engagement ID from the engagement table.
Hope that makes sense, below is my attempt at code which doesn't work at all (it doesn't like my case syntax).
CODE
Select
C.Name,
E.Classification,
E.End_Quarter,
E.End_Year,
RC.Position,
ra.Assigned
From Engagements as E
Inner Join Clients as C on C.ID = E.Client_ID
INNER JOIN (
Select a.Client_ID as ID, a.Position as Position, a.Eng_ID as Eng_ID
from Rep_Client a
inner join Employees b on b.ID = a.Emp_ID
where a.Emp_ID = '18'
) RC on C.ID = RC.ID and E.Engagement_ID = RC.Eng_ID
LEFT OUTER JOIN (
CASE WHEN RC.Position = 'RR' THEN
Select a.Client_ID as ID, a.Engagement_ID as Eng_ID, a.RR_Assigned as Assigned
from Engagements a
CASE WHEN RC.Position = 'WPR' THEN
Select a.Client_ID as ID, a.Engagement_ID as Eng_ID, a.WPR_Assigned as Assigned
from Engagements a
) ra on ra.Client_ID = E.Client_ID
30 Sep, 2008 - 10:24 AM
| 