Case Statement on INNER Join

Question

Trying to use CASE statements on a inner join and all I'm getting is syntax errors, anyone got any advice on this?

Here is the code

 SELECT  
    Call_type_ID,
    SUM (staging.dbo.outgoing_measure.ring_time) AS Ring_Time,
    SUM (staging.dbo.outgoing_measure.hold_time) As Hold_Time,
    SUM (staging.dbo.outgoing_measure.talk_time) AS Talk_Time,
    SUM (staging.dbo.outgoing_measure.acw_time) AS ACW_Time,
    COUNT(*) CallCount
FROM outgoing_measure

INNER JOIN datamartend.dbo.Call_Type_Dim ON 
CASE 
WHEN 
CTICallType_ID = 1
AND CTIAgentCallType_ID = 0
AND Abandoned  IS NULL
AND AnsTime > 0
AND CallState IS NULL
THEN Call_Type_ID = 10
WHEN
CTICallType_ID = 1
AND CTIAgentCallType_ID = 0
AND Abandoned  IS NULL
AND AnsTime > 0  
AND CallState = 1
THEN call_Type_id = 11
WHEN 
CTICallType_ID = 1
AND CTIAgentCallType_ID = 0
AND Abandoned = 1
AND AnsTime IS NULL
AND CallState IS NULL
THEN call_type_ID = 12
ELSE call_type_id = 1
END

Group by call_Type_id

This is the first time i've even worked with case statements let alone combining them with a inner join so i'm sorry if i've totally messed up.

The syntax errors im getting are:

Incorrect syntax on the = and WHEN here

THEN Call_Type_ID = 10
WHEN

And incorrect syntax expecting CONVERSION on the GROUP BY

Solution

It seems like you are trying to create Where-clauses in the case, but you should instead compare the result of the case against Call_Type_ID(or any other field you want) as in the example i wrote below Hope it helps!

Also sometimes i use brackets over my casing to make it easier to see where they start and stop.

INNER JOIN datamartend.dbo.Call_Type_Dim ON 
(CASE 
 WHEN  CTICallType_ID = 1
     AND CTIAgentCallType_ID = 0
     AND Abandoned  IS NULL
     AND AnsTime > 0
     AND CallState IS NULL
     THEN 10
WHEN CTICallType_ID = 1
     AND CTIAgentCallType_ID = 0
     AND Abandoned  IS NULL
     AND AnsTime > 0  
     AND CallState = 1
     THEN 11
WHEN 
     CTICallType_ID = 1
     AND CTIAgentCallType_ID = 0
     AND Abandoned = 1
     AND AnsTime IS NULL
     AND CallState IS NULL
     THEN 12
ELSE 1
END) = Call_Type_ID  -- Insert something here to join on.

Comments

[Richard C]

I think you could well be right, like i said im new to whole concept. thanks for the advice