SQL Query Shoretel Tracking Voicemail Calls

Question

I have query that i am having trouble with. I am trying to capture the parameters within the below KEY:

//  We track all calls ending up in Workgroup Voice Mailbox:
//  ExitReason:
//  9 - TransferVM
//  2 - ForwardAlways (Workgroup is set up to always forward calls)
//  3 - ForwardBusy
//  4 - ForwardNoAnswer
//  5 - ForwardNoAgent
//
//  TargetType:
//  3 - Mailbox
//  
//  TargetDN:
//  Target's Extension.
//  If target is workgroup's mailbox (TargetDN = QueueDN and TargetType = 3)
//  call ended up in workgroup mailbox.
//

The Query i am tring to exectute is:

Select CASE WHEN ExitReason = 9 AND 
    TargetType = 3 AND 
    TargetDN = QueueDN OR 
    ExitReason = 2 OR
    ExitReason = 3 OR
    ExitReason = 4 OR
    ExitReason = 5 AND
    TargetType = 3 THEN 1 ELSE 0 AS CallsVM
FROM   (queuecall queuecall1 INNER JOIN connect connect1 
ON queuecall1.ConnectTableID=connect1.ID) INNER JOIN call call1 
ON connect1.CallTableID=call1.ID

I get multiple errors with AS, FROM. Any assistance would be great. Thanks, Arron

Answer 1

You're missing the keyword END in your case statement. Also you missed an opportunity to use IN

e.g.

SELECT CASE 
         WHEN exitreason = 9 
              AND targettype = 3 
              AND targetdn = queuedn 
               OR exitreason IN ( 2, 3, 4, 5 ) 
                  AND targettype = 3 THEN 1 
         ELSE 0 
       END AS CallsVM 
FROM   queuecall queuecall1 
       INNER JOIN connect connect1 
               ON queuecall1.connecttableid = connect1.id 
       INNER JOIN call call1 
               ON connect1.calltableid = call1.id