CREATE OR REPLACE PROCEDURE SP_RPT_MEDIASTAT_T2
/*
Description : 短信发送统计报表,供iWeb报表调用
Author : wfw2527
Date : 2011-08-17
Version : 1.0
Comments :
*/
AS
eInfo VARCHAR2(200);
tSql VARCHAR2(2000);
V_BEGINPARTID VARCHAR(20);
V_ENDPARTID VARCHAR(20);
V_BeginDate VARCHAR(20);
V_EndDate VARCHAR(20);BEGIN
tSql :='insert into t_temp_mscount select getcity(a.sendno) city,
count(*) AS SENDCOUNT,
SUM(CASE
WHEN deliveryreport = ''DELIVRD|000'' THEN
1
ELSE
0
END) AS SUCCESSCOUNT,
SUM(CASE
WHEN deliveryreport = ''DELIVRD|000'' THEN
0
ELSE
1
END) AS FAILCOUNT,
to_char(decode(count(*),
0,
0,
round(SUM(CASE
WHEN deliveryreport = ''DELIVRD|000'' THEN
1
ELSE
0
END) * 100.0 / count(*),
2)),
''990.99'') || '' % '' AS SUCCESSPER
from (select *
from t_ms_media_task t
where MONTHDAY >= ''1011''
AND MONTHDAY < ''1017''
AND SENDTIME >= to_date(''2011-10-11 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')
AND SENDTIME < to_date(''2011-10-17 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')
union all
select *
from t_ms_media_task_his t
where MONTHDAY >= ''1011''
AND MONTHDAY < ''1017''
AND SENDTIME >= to_date(''2011-10-11 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')
AND SENDTIME < to_date(''2011-10-17 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')) a,
SENDADMINUSER.t_send_spinfo b
where a.spid = b.gid group by getcity(a.sendno)';
execute immediate tsql;
commit;
END SP_RPT_MEDIASTAT_T2;
/
/*
Description : 短信发送统计报表,供iWeb报表调用
Author : wfw2527
Date : 2011-08-17
Version : 1.0
Comments :
*/
AS
eInfo VARCHAR2(200);
tSql VARCHAR2(2000);
V_BEGINPARTID VARCHAR(20);
V_ENDPARTID VARCHAR(20);
V_BeginDate VARCHAR(20);
V_EndDate VARCHAR(20);BEGIN
tSql :='insert into t_temp_mscount select getcity(a.sendno) city,
count(*) AS SENDCOUNT,
SUM(CASE
WHEN deliveryreport = ''DELIVRD|000'' THEN
1
ELSE
0
END) AS SUCCESSCOUNT,
SUM(CASE
WHEN deliveryreport = ''DELIVRD|000'' THEN
0
ELSE
1
END) AS FAILCOUNT,
to_char(decode(count(*),
0,
0,
round(SUM(CASE
WHEN deliveryreport = ''DELIVRD|000'' THEN
1
ELSE
0
END) * 100.0 / count(*),
2)),
''990.99'') || '' % '' AS SUCCESSPER
from (select *
from t_ms_media_task t
where MONTHDAY >= ''1011''
AND MONTHDAY < ''1017''
AND SENDTIME >= to_date(''2011-10-11 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')
AND SENDTIME < to_date(''2011-10-17 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')
union all
select *
from t_ms_media_task_his t
where MONTHDAY >= ''1011''
AND MONTHDAY < ''1017''
AND SENDTIME >= to_date(''2011-10-11 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')
AND SENDTIME < to_date(''2011-10-17 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')) a,
SENDADMINUSER.t_send_spinfo b
where a.spid = b.gid group by getcity(a.sendno)';
execute immediate tsql;
commit;
END SP_RPT_MEDIASTAT_T2;
/
select getcity(a.sendno) city,
count(*) AS SENDCOUNT,
SUM(CASE
WHEN deliveryreport = 'DELIVRD|000' THEN
1
ELSE
0
END) AS SUCCESSCOUNT,
SUM(CASE
WHEN deliveryreport = 'DELIVRD|000' THEN
0
ELSE
1
END) AS FAILCOUNT,
to_char(decode(count(*),
0,
0,
round(SUM(CASE
WHEN deliveryreport = 'DELIVRD|000' THEN
1
ELSE
0
END) * 100.0 / count(*),
2)),
'990.99') || ' % ' AS SUCCESSPER
from (select *
from t_ms_media_task t
where MONTHDAY >= '1011'
AND MONTHDAY < '1018'
AND SENDTIME >= to_date('2011-10-11 11:00:00','yyyy-mm-dd hh24:mi:ss')
AND SENDTIME < to_date('2011-10-18 11:00:00','yyyy-mm-dd hh24:mi:ss')
union all
select *
from t_ms_media_task_his t
where MONTHDAY >= '1011'
AND MONTHDAY < '1018'
AND SENDTIME >= to_date('2011-10-11 11:00:00','yyyy-mm-dd hh24:mi:ss')
AND SENDTIME < to_date('2011-10-18 11:00:00','yyyy-mm-dd hh24:mi:ss')) a,
SENDADMINUSER.t_send_spinfo b
where a.spid = b.gid group by getcity(a.sendno)
这是为什么?、、、、、
看下这个