SQL查詢語句

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