1

I am trying to do a query that allow for multiple columns in a view. Any assistance would be helpful. 

SELECT  queuecall1.StartTime, queuecall1.QueueName
CASE 
    WHEN ExitReason = 7 THEN 1 ELSE 0 END AS CallsAbandoned
    WHEN ExitReason = 1 THEN 1 ELSE 0 END AS CallsAgent 
    WHEN calltype = 1 THEN 1 ELSE 0 END) AS CallsInternal 
    WHEN calltype = 2 THEN 1 ELSE 0 END) AS CallsExternal
FROM   (queuecall queuecall1 INNER JOIN connect connect1 
ON queuecall1.ConnectTableID=connect1.ID) INNER JOIN call call1 
ON connect1.CallTableID=call1.ID
Cœur
  • 37,241
  • 25
  • 195
  • 267
Arron Robles
  • 273
  • 1
  • 2
  • 10
  • 2
    What is your desired result? I see a case syntatically wrong, but i'm not understanding what you want – Filipe Silva Dec 05 '13 at 16:55
  • Thanks for your response. ia am getting an error: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'CASE'. – Arron Robles Dec 05 '13 at 17:01
  • I am tring to get a table view and create a dataset for BI reports for a shoretel phone system. – Arron Robles Dec 05 '13 at 17:03
  • That error is because you are missing a comma before the case, but it is not your only error. Show your desired output, the table that you want in the end. – Filipe Silva Dec 05 '13 at 17:04
  • So, 'code' SELECT queuecall1.StartTime, queuecall1.QueueName, CASE WHEN ExitReason = 7 THEN 1 ELSE 0 END AS CallsAbandoned FROM (queuecall queuecall1 INNER JOIN connect connect1 ON queuecall1.ConnectTableID=connect1.ID) INNER JOIN call call1 ON connect1.CallTableID=call1.ID – Arron Robles Dec 05 '13 at 17:08
  • Thanks Filipe for your help. GarethD beat me to the table results post. -Arron – Arron Robles Dec 05 '13 at 17:17
  • I wasn't totally sure that you were looking for that, but glad you got it working. – Filipe Silva Dec 05 '13 at 17:19

1 Answers1

0

You need to do a separate case statement for each column:

SELECT  queuecall1.StartTime, 
        queuecall1.QueueName,
        CASE WHEN ExitReason = 7 THEN 1 ELSE 0 END AS CallsAbandoned,
        CASE WHEN ExitReason = 1 THEN 1 ELSE 0 END AS CallsAgent,
        CASE WHEN calltype = 1 THEN 1 ELSE 0 END AS CallsInternal,
        CASE WHEN calltype = 2 THEN 1 ELSE 0 END AS CallsExternal
FROM   (queuecall queuecall1 INNER JOIN connect connect1 
ON queuecall1.ConnectTableID=connect1.ID) INNER JOIN call call1 
ON connect1.CallTableID=call1.ID

This would give an output like:

StartTime | QueueName | CallsAbandoned | CallsAgent | CallsInternal | CallsExternal
----------+-----------+----------------+------------+---------------+---------------
 10:59    |  Queue1   |      1         |    0       |     1         |      0
 11:05    |  Queue1   |      1         |    0       |     1         |      0
 11:11    |  Queue1   |      0         |    1       |     1         |      0
 11:12    |  Queue1   |      0         |    0       |     0         |      1
 11:24    |  Queue1   |      0         |    1       |     0         |      1
 11:37    |  Queue1   |      1         |    0       |     0         |      1
 11:42    |  Queue1   |      0         |    1       |     0         |      0
GarethD
  • 68,045
  • 10
  • 83
  • 123
  • GarethD. This is what i was looking for. There were 2 errors on the query, i removed the ) after the END statement and that worked. This was very helpful and resolved my Issue. – Arron Robles Dec 05 '13 at 17:14