sql 语句

最新推荐文章于 2024-09-14 18:42:05 发布

willvc123

最新推荐文章于 2024-09-14 18:42:05 发布

阅读量551

点赞数

本文链接：https://blog.csdn.net/willvc123/article/details/3444434

版权

这是做一个保险项目写的一个报表模块的时候拼的一个sql串

select    to_char(t.agentid) AgentID, (s.AgentName) AgentName,
sum(t.LoginCount) LoginCount,   sum(t.LogoutCount) LogoutCount,
    sum(t.WorkTime)||'秒' WorkTime ,   sum(t.CallTime)||'秒' CallTime,
    sum(t.BusyTime)||'秒' BusyTime,    ROUND(decode(sum(t.callcount),0,0,
    sum(t.calltime)/sum(t.callcount)),0)||'秒' AvgBusyTime,   sum(t.WorkStatTime)||'秒' WorkStatTime,
     ROUND(decode(sum(t.worktime),0,0,sum(t.calltime)/sum(t.worktime)*100),2)||'%' rate ,
     sum(t.CallCount) CallCount   ,sum(t.KeepNum) KeepNum   ,sum(t.BusyCount) BusyCount   ,
     sum(t.InterHelpNum) InterHelpNum   ,sum(t.TransferAutoNum) TransferAutoNum     ,sum(t.TriCallNum) TriCallNum   ,
     sum(t.OutCallNum) OutCallNum     ,sum(t.InterCallNum) InterCallNum       ,sum(t.TransferOutNum) TransferOutNum
     ,sum(t.InterTransferNum) InterTransferNum from icd.t_daylog_agentwork t
      left join xhs.csr_agent s on s.agentid=to_char(t.agentid) where LENGTH(t.agentid)<>4
        and t.logdate >=to_date(20081205000000,'yyyymmddhh24miss') and t.logdate
        <=to_date(20081219235959,'yyyymmddhh24miss') group by t.agentid ,s.agentname
         union select '总计' AgentID, ' ' AgentName,   NVL(sum(t.LoginCount),0) LoginCount,
         NVL(sum(t.LogoutCount),0) LogoutCount,   NVL(sum(t.WorkTime),0)||'秒' WorkTime,
          NVL(sum(t.CallTime),0)||'秒' CallTime,   NVL(sum(t.BusyTime),0)||'秒' BusyTime,
           ROUND(decode(sum(t.callcount),0,0,sum(t.calltime)/sum(t.callcount)),0)||'秒' AvgBusyTime,
            NVL(sum(t.WorkStatTime),0)||'秒' WorkStatTime,
            ROUND(decode(sum(t.worktime),0,0,sum(t.calltime)/sum(t.worktime)*100),2)||'%' rate ,
            NVL(sum(t.CallCount),0) CallCount ,NVL(sum(t.KeepNum),0) KeepNum ,NVL(sum(t.BusyCount),0) BusyCount ,
            NVL(sum(t.InterHelpNum),0) InterHelpNum   ,NVL(sum(t.TransferAutoNum),0) TransferAutoNum    ,NVL(sum(t.TriCallNum),0) TriCallNum ,NVL(sum(t.OutCallNum),0) OutCallNum    ,
            NVL(sum(t.InterCallNum),0) InterCallNum        ,NVL(sum(t.TransferOutNum),0) TransferOutNum   ,
            NVL(sum(t.InterTransferNum),0) InterTransferNum   from icd.t_daylog_agentwork t
            where LENGTH(t.agentid)<>4   and t.logdate >=to_date(20081205000000,'yyyymmddhh24miss') and t.logdate <=to_date(20081219235959,'yyyymmddhh24miss')