CREATE FUNCTION [dbo].[fun_AgtRateByMin]
( @start DATETIME,@end DATETIME ,@uid VARCHAR(20) )RETURNS TABLE
AS
RETURN
(
SELECT
u.uid AS [工号]
,u.name AS [姓名]
,ISNULL(SUM(CASE WHEN a.io=0 THEN 1 ELSE 0 END),0) AS [呼入总量]
,ISNULL(SUM(CASE WHEN a.io=0 AND a.AnswerFlag=1 THEN 1 ELSE 0 END),0) AS [呼入接通量]
,ISNULL(SUM(CASE WHEN a.io=0 AND a.AnswerFlag=1 THEN a.TalkDuration ELSE 0 END),0) AS [呼入通话时长]
,ISNULL(SUM(CASE WHEN a.io=1 THEN 1 ELSE 0 END),0) AS [呼出总量]
,ISNULL(SUM(CASE WHEN a.io=1 AND a.AnswerFlag=1 THEN 1 ELSE 0 END),0) AS [呼出接通量]
,ISNULL(SUM(CASE WHEN a.io=1 AND a.AnswerFlag=1 THEN a.TalkDuration ELSE 0 END),0) AS [呼出通话时长]
,i.freesec AS [空闲时长]
,i.pausesec AS [小休时长]
,i.doingsec AS [后台时长]
,i.confsec AS [会议时长]
,i.traningsec AS [培训时长]
,i.dinnersec AS [就餐时长]
FROM dbo.rc_calllog a
LEFT JOIN
(
SELECT
uid
,ISNULL(SUM(CASE WHEN status='空闲' THEN duration ELSE 0 END),0) AS freesec
,ISNULL(SUM(CASE WHEN status='小休' THEN duration ELSE 0 END),0) AS pausesec
,ISNULL(SUM(CASE WHEN status='主动服务' THEN duration ELSE 0 END),0) AS doingsec
,ISNULL(SUM(CASE WHEN STATUS='会议' THEN duration ELSE 0 END),0) AS confsec
,ISNULL(SUM(CASE WHEN STATUS='培训' THEN duration ELSE 0 END),0) AS traningsec
,ISNULL(SUM(CASE WHEN STATUS='就餐' THEN duration ELSE 0 END),0) AS dinnersec
FROM dbo.rc_statrecord WHERE starttime BETWEEN @start AND @end GROUP BY uid
)i ON a.AgentId= i.uid
INNER JOIN dbo.rc_operator u ON a.AgentId=u.uid
WHERE a.InboundCallTime BETWEEN @start AND @end OR a.StartRingTime BETWEEN @start AND @end
GROUP BY u.uid,u.name,i.freesec,i.pausesec,i.doingsec,i.confsec,i.traningsec,i.dinnersec
)
( @start DATETIME,@end DATETIME ,@uid VARCHAR(20) )RETURNS TABLE
AS
RETURN
(
SELECT
u.uid AS [工号]
,u.name AS [姓名]
,ISNULL(SUM(CASE WHEN a.io=0 THEN 1 ELSE 0 END),0) AS [呼入总量]
,ISNULL(SUM(CASE WHEN a.io=0 AND a.AnswerFlag=1 THEN 1 ELSE 0 END),0) AS [呼入接通量]
,ISNULL(SUM(CASE WHEN a.io=0 AND a.AnswerFlag=1 THEN a.TalkDuration ELSE 0 END),0) AS [呼入通话时长]
,ISNULL(SUM(CASE WHEN a.io=1 THEN 1 ELSE 0 END),0) AS [呼出总量]
,ISNULL(SUM(CASE WHEN a.io=1 AND a.AnswerFlag=1 THEN 1 ELSE 0 END),0) AS [呼出接通量]
,ISNULL(SUM(CASE WHEN a.io=1 AND a.AnswerFlag=1 THEN a.TalkDuration ELSE 0 END),0) AS [呼出通话时长]
,i.freesec AS [空闲时长]
,i.pausesec AS [小休时长]
,i.doingsec AS [后台时长]
,i.confsec AS [会议时长]
,i.traningsec AS [培训时长]
,i.dinnersec AS [就餐时长]
FROM dbo.rc_calllog a
LEFT JOIN
(
SELECT
uid
,ISNULL(SUM(CASE WHEN status='空闲' THEN duration ELSE 0 END),0) AS freesec
,ISNULL(SUM(CASE WHEN status='小休' THEN duration ELSE 0 END),0) AS pausesec
,ISNULL(SUM(CASE WHEN status='主动服务' THEN duration ELSE 0 END),0) AS doingsec
,ISNULL(SUM(CASE WHEN STATUS='会议' THEN duration ELSE 0 END),0) AS confsec
,ISNULL(SUM(CASE WHEN STATUS='培训' THEN duration ELSE 0 END),0) AS traningsec
,ISNULL(SUM(CASE WHEN STATUS='就餐' THEN duration ELSE 0 END),0) AS dinnersec
FROM dbo.rc_statrecord WHERE starttime BETWEEN @start AND @end GROUP BY uid
)i ON a.AgentId= i.uid
INNER JOIN dbo.rc_operator u ON a.AgentId=u.uid
WHERE a.InboundCallTime BETWEEN @start AND @end OR a.StartRingTime BETWEEN @start AND @end
GROUP BY u.uid,u.name,i.freesec,i.pausesec,i.doingsec,i.confsec,i.traningsec,i.dinnersec
)
( @start DATETIME,@end DATETIME ,@uid VARCHAR(20) )RETURNS TABLE
AS
RETURN
(
SELECT
u.uid AS [工号]
,u.name AS [姓名]
,nvl(SUM(CASE WHEN a.io=0 THEN 1 ELSE 0 END),0) AS [呼入总量]
,nvl(SUM(CASE WHEN a.io=0 AND a.AnswerFlag=1 THEN 1 ELSE 0 END),0) AS [呼入接通量]
,nvl(SUM(CASE WHEN a.io=0 AND a.AnswerFlag=1 THEN a.TalkDuration ELSE 0 END),0) AS [呼入通话时长]
,nvl(SUM(CASE WHEN a.io=1 THEN 1 ELSE 0 END),0) AS [呼出总量]
,nvl(SUM(CASE WHEN a.io=1 AND a.AnswerFlag=1 THEN 1 ELSE 0 END),0) AS [呼出接通量]
,nvl(SUM(CASE WHEN a.io=1 AND a.AnswerFlag=1 THEN a.TalkDuration ELSE 0 END),0) AS [呼出通话时长]
,i.freesec AS [空闲时长]
,i.pausesec AS [小休时长]
,i.doingsec AS [后台时长]
,i.confsec AS [会议时长]
,i.traningsec AS [培训时长]
,i.dinnersec AS [就餐时长]
FROM dbo.rc_calllog a
LEFT JOIN
(
SELECT
uid
,nvl(SUM(CASE WHEN status='空闲' THEN duration ELSE 0 END),0) AS freesec
,nvl(SUM(CASE WHEN status='小休' THEN duration ELSE 0 END),0) AS pausesec
,nvl(SUM(CASE WHEN status='主动服务' THEN duration ELSE 0 END),0) AS doingsec
,nvl(SUM(CASE WHEN STATUS='会议' THEN duration ELSE 0 END),0) AS confsec
,nvl(SUM(CASE WHEN STATUS='培训' THEN duration ELSE 0 END),0) AS traningsec
,nvl(SUM(CASE WHEN STATUS='就餐' THEN duration ELSE 0 END),0) AS dinnersec
FROM dbo.rc_statrecord WHERE starttime BETWEEN @start AND @end GROUP BY uid
)i ON a.AgentId= i.uid
INNER JOIN dbo.rc_operator u ON a.AgentId=u.uid
WHERE a.InboundCallTime BETWEEN @start AND @end OR a.StartRingTime BETWEEN @start AND @end
GROUP BY u.uid,u.name,i.freesec,i.pausesec,i.doingsec,i.confsec,i.traningsec,i.dinnersec
)
参数和变量前不能加@。@只是sqlserver的语法。
这个是你忘改了吧。
--oracle 中返回数据集要用 sys_refcursor类型
--oracle 中别名中不能加[] 除非两边加双引号,为了简单我全去掉了。
----如果你想要用 "[asdasd]" 这种形式
--@定义变量的形式oracle也不用,我给改了
CREATE FUNCTION FUN_AGTRATEBYMIN(I_START DATETIME,
I_END DATETIME,
I_UID VARCHAR(20),
O_CURSOR OUT SYS_REFCUROSR) AS
BEGIN
OPEN O_CURSOR FOR
SELECT U.UID AS 工号,
U.NAME AS 姓名
,ISNULL(SUM(CASE WHEN a.io=0 THEN 1 ELSE 0 END),0) AS 呼入总量
,ISNULL(SUM(CASE WHEN a.io=0 AND a.AnswerFlag=1 THEN 1 ELSE 0 END),0) AS 呼入接通量
,ISNULL(SUM(CASE WHEN a.io=0 AND a.AnswerFlag=1 THEN a.TalkDuration ELSE 0 END),0) AS 呼入通话时长
,ISNULL(SUM(CASE WHEN a.io=1 THEN 1 ELSE 0 END),0) AS 呼出总量
,ISNULL(SUM(CASE WHEN a.io=1 AND a.AnswerFlag=1 THEN 1 ELSE 0 END),0) AS 呼出接通量
,ISNULL(SUM(CASE WHEN a.io=1 AND a.AnswerFlag=1 THEN a.TalkDuration ELSE 0 END),0) AS 呼出通话时长,
I.FREESEC AS 空闲时长,
I.PAUSESEC AS 小休时长,
I.DOINGSEC AS 后台时长,
I.CONFSEC AS 会议时长,
I.TRANINGSEC AS 培训时长,
I.DINNERSEC AS 就餐时长
FROM DBO.RC_CALLLOG A
LEFT JOIN (SELECT UID
,ISNULL(SUM(CASE WHEN status='空闲' THEN duration ELSE 0 END),0) AS freesec
,ISNULL(SUM(CASE WHEN status='小休' THEN duration ELSE 0 END),0) AS pausesec
,ISNULL(SUM(CASE WHEN status='主动服务' THEN duration ELSE 0 END),0) AS doingsec
,ISNULL(SUM(CASE WHEN STATUS='会议' THEN duration ELSE 0 END),0) AS confsec
,ISNULL(SUM(CASE WHEN STATUS='培训' THEN duration ELSE 0 END),0) AS traningsec
,ISNULL(SUM(CASE WHEN STATUS='就餐' THEN duration ELSE 0 END),0) AS dinnersec
FROM DBO.RC_STATRECORD
WHERE STARTTIME BETWEEN I_START AND I_END
GROUP BY UID) I
ON A.AGENTID = I.UID
INNER JOIN DBO.RC_OPERATOR U
ON A.AGENTID = U.UID
WHERE A.INBOUNDCALLTIME BETWEEN I_START AND I_END
OR A.STARTRINGTIME BETWEEN I_START AND I_END
GROUP BY U.UID,
U.NAME,
I.FREESEC,
I.PAUSESEC,
I.DOINGSEC,
I.CONFSEC,
I.TRANINGSEC,
I.DINNERSEC);
END;
CREATE OR REPLACE procedure FUN_AGTRATEBYMIN(V_START IN DATE,V_END IN DATE,V_OUTPUT OUT SYS_REFCURSOR)
is
BEGIN
open v_output for
SELECT
u.uid AS 工号
,U.NAME AS 姓名
,nvl(SUM(CASE WHEN a.io=0 THEN 1 ELSE 0 END),0) AS 呼入总量
,nvl(SUM(CASE WHEN a.io=0 AND a.AnswerFlag=1 THEN 1 ELSE 0 END),0) AS 呼入接通量
,nvl(SUM(CASE WHEN a.io=0 AND a.AnswerFlag=1 THEN a.TalkDuration ELSE 0 END),0) AS 呼入通话时长
,nvl(SUM(CASE WHEN a.io=1 THEN 1 ELSE 0 END),0) AS 呼出总量
,nvl(SUM(CASE WHEN a.io=1 AND a.AnswerFlag=1 THEN 1 ELSE 0 END),0) AS 呼出接通量
,nvl(SUM(CASE WHEN a.io=1 AND a.AnswerFlag=1 THEN a.TalkDuration ELSE 0 END),0) AS 呼出通话时长
,i.freesec AS 空闲时长
,i.pausesec AS 小休时长
,i.doingsec AS 后台时长
,i.confsec AS 会议时长
,i.traningsec AS 培训时长
,I.DINNERSEC AS 就餐时长
FROM rc_calllog a
LEFT JOIN
(
SELECT
uid
,nvl(SUM(CASE WHEN status='空闲' THEN duration ELSE 0 END),0) AS freesec
,nvl(SUM(CASE WHEN status='小休' THEN duration ELSE 0 END),0) AS pausesec
,nvl(SUM(CASE WHEN status='主动服务' THEN duration ELSE 0 END),0) AS doingsec
,nvl(SUM(CASE WHEN STATUS='会议' THEN duration ELSE 0 END),0) AS confsec
,nvl(SUM(CASE WHEN STATUS='培训' THEN duration ELSE 0 END),0) AS traningsec
,NVL(SUM(CASE WHEN STATUS='就餐' THEN DURATION ELSE 0 END),0) AS DINNERSEC
FROM rc_statrecord WHERE starttime BETWEEN v_start AND v_end GROUP BY uid
)i ON a.AgentId= i.uid
INNER JOIN RC_OPERATOR U ON A.AGENTID=U.UID
WHERE a.InboundCallTime BETWEEN v_start AND v_end OR a.StartRingTime BETWEEN v_start AND v_end
GROUP BY U.UID,U.NAME,I.FREESEC,I.PAUSESEC,I.DOINGSEC,I.CONFSEC,I.TRANINGSEC,I.DINNERSEC;
end;
还是写成存储过程吧,这在oracle里比较实用。