Shoretel报告废弃率计算的SQL查询

Question

有人能检查一下我的查询，它似乎没有执行。我试图从callsAgent和CallsAbandoned =获得放弃率% 98.45。“列”中的字段是列。

公式应为: CallsAbandoned >0或CallsAgent >0，然后是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

我得到的错误是: Msg 8117，级别16，状态1，第31行操作数数据类型varchar对除法操作符无效。

任何默许都是很好的。

谢谢你，阿伦

Answer 1

通过在列名周围放置撇号，您将它们视为单独的varchar (text)值，而不是列名。该错误正确地说，在除法时不能使用。

如果删除这些内容，它将知道您所引用的是列名。假设列中的值是可以划分的适用类型。下列措施应能发挥作用：

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