SQL Query for abandonment rate calculation for Shoretel Reports

Question

Can someone check my query it does not seem to be executing. I am tring to get the abandonment Rate percentage from callsAgent and CallsAbandoned = to abandomnet % 98.45. The fields in 'column' are a column.

Formula should be: CallsAbandoned >0 or CallsAgent >0 then CallsAbandoned / CallsAgent *100

CASE WHEN 'callsadandoned' > 0 OR 'callsagent' > 0 
THEN 'callsadandoned' / 'callsagent' *100 END 
AS AdnandonmentRate

SELECT  queuecall1.StartTime, 
    queuecall1.TargetDN,
    queuecall1.TargetType,
    queuecall1.QueueDN,
    queuecall1.QueueName,
    queuecall1.DurationSeconds,
    queuecall1.CallID,
    call1.CallType,
    queuecall1.Duration,
    queuecall1.ExitReason,
    datename(mm, queuecall1.StartTime) AS Month,
    datename(YYYY, queuecall1.StartTime) AS Year,
    datename(DW, queuecall1.StartTime) AS Weekday,
    datename(QQ, queuecall1.StartTime) AS Quarter,
    datename(WK, queuecall1.StartTime) AS Week,
    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,
    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, 
   CASE WHEN (ExitReason = 9) AND 
    TargetDN <> QueueDN OR 
    ExitReason = 10 OR
    ExitReason = 11 THEN 1 ELSE 0 END as CallsTransfered,
    CASE WHEN CallsAbandoned > 0 OR CallsAgent > 0 THEN CallsAbandoned /           CallsAgent *100 END AS AdnandonmentRate
    FROM   (queuecall queuecall1 INNER JOIN connect connect1 
ON queuecall1.ConnectTableID=connect1.ID) INNER JOIN call call1 
ON connect1.CallTableID=call1.ID

The error i am getting is: Msg 8117, Level 16, State 1, Line 31 Operand data type varchar is invalid for divide operator.

Any Assitance would be great.

Thanks, Arron

Answer 1

By placing apostrophe's around the column names you are treating them as individual varchar (text) values and not column names. Which the error is correctly saying cannot be used when dividing.

If you remove these, it will instead know that you are referring to a column name. Assuming the values in your columns are of the applicable type that can be divided. The following should work:

CASE WHEN callsadandoned > 0 OR callsagent > 0 THEN callsadandoned / callsagent *100 END AS AdnandonmentRate