CASE Statements

[shamilton]

The code below works...but Status and Scheduled With return null values if the Type is 2...why?

 

SELECT
tblHistory.Dte AS 'Date',
'Scheduled With' = CASE
WHEN tblHistory.Typ = 1 THEN tblActivity.SchedWith
WHEN tblHistory.Typ = 2 THEN tblOpportunity.Name
END,
'Type' = CASE tblHistory.Typ
WHEN 1 THEN 'Activity'
WHEN 2 THEN 'Opportunity'
END,
tblHistory.Details,
'Status' = CASE
WHEN tblHistory.Typ = 1 THEN tblResults.Description
WHEN tblHistory.Typ = 2 THEN tblStatus.Description
END,
tblUser.FirstN + ' ' + tblUser.LastN AS 'User'
FROM CRMHistory tblHistory
LEFT OUTER JOIN CRMActivity tblActivity ON tblHistory.SourceID = tblActivity.ID
LEFT OUTER JOIN CRMOpportunity tblOpportunity ON tblHistory.SourceID = tblOpportunity.ID
LEFT OUTER JOIN engCRM_Results tblResults ON tblHistory.Result = tblResults.ID
LEFT OUTER JOIN UserInfo tblUser ON tblHistory.Login = tblUser.Login
LEFT OUTER JOIN engCRM_Status tblStatus ON tblOpportunity.Status = tblStatus.ID
ORDER BY tblHistory.Dte DESC

[shamilton]

Nevermind...figured it out.

SourceID was 0 so the join wasn't going to do anything...fixed and now it works.