create or replace procedure imms_pk_reportSend(fromDate varchar2,toDate varchar2)
as
/*
Function:系統效能統計
Author: qja
功能:將imms_statusreport 表和imms_report_send 表中的數據統計到imms_report_send表中。
date: 2008.08.19
*/
v_DRsuccess number;
v_DRfail number;
v_updateSum number;
v_failsum number;
v_datdeal varchar2(20);
BEGIN
execute immediate 'truncate table imms_report_send';
--dbms_output.put_line(fromDate||' '||toDate);
for comuser in
(select distinct rs.vc2campaignid,rs.vc2source,rs.vc2servicecode
from imms_realsend rs,imms_realsendstatus iss
where iss.numrealsendguid=rs.numrealsendguid
and trunc(rs.datcreate) >= to_date(fromDate,'yyyy-mm-dd')
and trunc(rs.datcreate) <=to_date(toDate,'yyyy-mm-dd'))
loop
begin
--jiqu update 20080819
v_DRsuccess := 0;
select to_char(ir.datcreate, 'yyyymmdd') ,
count(*)
into v_datdeal,v_DRsuccess
from imms_realsendstatus iss,imms_realsend ir
where iss.numrealsendguid=ir.numrealsendguid
and trunc(ir.datcreate) >= to_date(fromDate,'yyyy-mm-dd')
and trunc(ir.datcreate) <=to_date(toDate,'yyyy-mm-dd')
and iss.vc2reportstatus='S'--DR回覆筆數
group by to_char(ir.datcreate, 'yyyymmdd');
--jiaqu update 20080819
exception
when no_data_found then
v_DRsuccess := 0;
end;
begin
v_DRfail := 0;
select to_char(ir.datcreate, 'yyyymmdd') ,
count(*)
into v_datdeal,v_DRfail
from imms_realsendstatus iss,imms_realsend ir
where iss.numrealsendguid=ir.numrealsendguid
and trunc(ir.datcreate) >= to_date(fromDate,'yyyy-mm-dd')
and trunc(ir.datcreate) <=to_date(toDate,'yyyy-mm-dd')
and iss.vc2reportstatus='F'--DR未回覆筆數
group by to_char(ir.datcreate, 'yyyymmdd');
exception
when no_data_found then
v_DRfail := 0;
end;
begin
v_updateSum :=0;
select to_char(rs.datcreate, 'yyyymmdd') ,
count(*)
into v_datdeal,v_updateSum
from imms_realsendstatus iss,imms_realsend rs
where iss.numrealsendguid=rs.numrealsendguid
and trunc(rs.datcreate) >= to_date(fromDate,'yyyy-mm-dd')
and trunc(rs.datcreate) <=to_date(toDate,'yyyy-mm-dd')
group by to_char(rs.datcreate, 'yyyymmdd');--report update總筆數
exception
when no_data_found then
v_updateSum :=0;
end;
begin
v_failsum :=0;
select to_char(rs.datcreate, 'yyyymmdd') ,
count(*)
into v_datdeal,v_failsum
from imms_realsend rs,imms_realsendstatus iss
where iss.numrealsendguid=rs.numrealsendguid
and trunc(rs.datcreate)>= to_date(fromDate,'yyyy-mm-dd')
and trunc(rs.datcreate)<=to_date(toDate,'yyyy-mm-dd')
and iss.vc2reportstatus='F'
group by to_char(rs.datcreate, 'yyyymmdd');--失敗總筆數
exception
when no_data_found then
v_failsum :=0;
end;
insert into imms_report_send
(numrsguid, vc2campaignid, datcreate, numsuccesssum, numfailsum,numtotalsum, vc2source,numcontentfailed,numreportupdate,vc2servicecode)
values
(seq_reportsend.nextval,comuser.vc2campaignid, to_date(v_datdeal,'yyyymmdd'),v_DRsuccess,v_DRfail,v_DRsuccess+v_DRfail ,comuser.vc2source, v_updateSum,v_failsum+v_DRfail,comuser.vc2servicecode);
COMMIT;
end loop;
END;
/
上面是我的的存儲過程。
高手一條SQL就搞定了。
SQL語句還是很強大的。
努力學習....
我是笨鳥...
select Seq_Report_send.nextval, a.*
from
(SELECT rs.vc2campaignid,
TRUNC(rs.datcreate) AS datdeal,
SUM(DECODE(iss.vc2reportstatus,'S',1,0)) AS DRsuccess,
SUM(DECODE(iss.vc2reportstatus,'F',1,0)) AS DRfail,
SUM(DECODE(iss.vc2reportstatus,'S',1,'F',1,0)) AS Total,
rs.vc2source,
COUNT(rs.vc2campaignid) AS updateSum,
SUM(DECODE(iss.vc2reportstatus,'F',1,0))+sum(DECODE(rs.vc2status,'2',1,0)) AS failsum,
rs.vc2servicecode
FROM imms_realsend rs,imms_realsendstatus iss
WHERE iss.numrealsendguid=rs.numrealsendguid
AND rs.datcreate >= to_date('2008-02-02','yyyy-mm-dd')
AND rs.datcreate <=to_date('2008-08-29','yyyy-mm-dd')
GROUP BY rs.vc2campaignid, rs.vc2source, rs.vc2servicecode, TRUNC(rs.datcreate)) a