create or replace PROCEDURE Pro_Drivemail_log (
Log_Date Varchar2
)
Is
V_SQL VARCHAR2(5000);
--===============================================================
-- Procedure Desc
--
-- Parameter ：年月日并連的字符型參數（YYYYMMDD）
--
-- Desc ：統計參數日發送的數量，和參數日打開的數量;總打開量、各
-- 后綴的打開量和總點擊量之外的統計，是以參數日的發送量
-- 為基準的。
--
-- Result Table ：LOG_SENDING
-- Transition Table：DRIVEMAIL_SEND_TEMP & DRIVEMAIL_OPEN_TEMP
--
--================================================================
BEGIN
---當天發送數據提取
EXECUTE IMMEDIATE 'TRUNCATE TABLE DRIVEMAIL_SEND_TEMP';
COMMIT;
FOR C IN (SELECT * FROM TAB WHERE TNAME LIKE 'TASK_EMAILS_%' AND SUBSTR(TNAME,INSTR(TNAME,'_',1,2)+1)>'103000') LOOP
V_SQL:='INSERT /*+ APPEND */ INTO DRIVEMAIL_SEND_TEMP NOLOGGING
(ID,EMAIL,ACTIVE,OPEN,CLICK,OPEN_TIME,SENDING_TIME)
SELECT ID,EMAIL,ACTIVE,OPEN,CLICK,OPEN_TIME,SENDING_TIME
FROM '||C.TNAME||' WHERE TO_CHAR(SENDING_TIME,''YYYYMMDD'') = '||Log_Date;
EXECUTE IMMEDIATE V_SQL;
COMMIT;
END LOOP;
---------------------------------------------------------------------------------------------------------
---當天打開數據提取
EXECUTE IMMEDIATE 'TRUNCATE TABLE DRIVEMAIL_OPEN_TEMP';
COMMIT;
FOR C IN (SELECT * FROM TAB WHERE TNAME LIKE 'TASK_EMAILS_%' AND SUBSTR(TNAME,INSTR(TNAME,'_',1,2)+1)>'103000') LOOP
V_SQL:='INSERT /*+ APPEND */ INTO DRIVEMAIL_OPEN_TEMP NOLOGGING
(ID,EMAIL,ACTIVE,OPEN,CLICK,OPEN_TIME,SENDING_TIME)
SELECT ID,EMAIL,ACTIVE,OPEN,CLICK,OPEN_TIME,SENDING_TIME
FROM '||C.TNAME||' WHERE OPEN>=1 AND TO_CHAR(OPEN_TIME,''YYYYMMDD'') = '||Log_Date ;
EXECUTE IMMEDIATE V_SQL;
COMMIT;
END LOOP;
---------------------------------------------------------------------------------------------------------
INSERT /*+ APPEND */ INTO LOG_SENDING NOLOGGING
SELECT A.SENDINGDATE,
A.SENDINGCOUNT,
A.SENDSUCCESS,
B.OPENCOUNT,
B.CLICKCOUNT,
A.REBOUND,
A.SOFTREBOUND,
------------------------------------------------------------------------------------------
----記錄的是當天打開的數據（含以往發送的數據）
B.OPEN_163,
B.OPEN_126,
B.OPEN_SINA,
B.OPEN_TOM,
B.OPEN_SOHU,
B.OPEN_YAHOO_COM,
B.OPEN_YAHOO_COMCN,
B.OPEN_QQ,
B.OPEN_HOTMAIL,
B.OPEN_21CN,
------------------------------------------------------------------------------------------
A.SOFT_163,
A.SOFT_126,
A.SOFT_SINA,
A.SOFT_TOM,
A.SOFT_SOHU,
A.SOFT_YAHOO_COM,
A.SOFT_YAHOO_COMCN,
A.SOFT_QQ,
A.SOFT_HOTMAIL,
A.SOFT_21CN,
------------------------------------------------------------------------------------------
A.R_Open,
A.R_Open_163,
A.R_Open_126,
A.R_Open_Sina,
A.R_Open_Tom,
A.R_Open_Sohu,
A.R_Open_YahooCom,
A.R_Open_Yahoocomcn,
A.R_Open_QQ,
A.R_Open_HOTMAIL,
A.R_Open_21CN,
------------------------------------------------------------------------------------------
A.R_Soft,
A.R_Soft_163,
A.R_Soft_126,
A.R_Soft_Sina,
A.R_Soft_Tom,
A.R_Soft_Sohu,
A.R_Soft_YahooCom,
A.R_Soft_Yahoocomcn,
A.R_Soft_QQ,
A.R_Soft_HOTMAIL,
A.R_Soft_21CN
------------------------------------------------------------------------------------------
FROM
(
SELECT LOG_DATE SENDINGDATE ,--發送時間
COUNT(*) SENDINGCOUNT ,--發送數量
SUM(CASE WHEN ACTIVE=1 THEN 1 ELSE 0 END) SENDSUCCESS ,--發送成功數
-- SUM(OPEN) OPENCOUNT ,--打開數
-- SUM(CLICK) CLICKCOUNT ,--點擊數
SUM(CASE WHEN ACTIVE<-50 AND ACTIVE>-500 THEN 1 ELSE 0 END) REBOUND ,--硬彈回數
SUM(CASE WHEN ACTIVE=-1 OR ACTIVE<-500 THEN 1 ELSE 0 END) SOFTREBOUND ,--軟彈回數
-----------------------------------------------------------------
/*
----Count Of Open
SUM(CASE WHEN OPEN>=1 AND INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END) OPEN_163 ,--打開數-163
SUM(CASE WHEN OPEN>=1 AND INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END) OPEN_126 ,--打開數-126
SUM(CASE WHEN OPEN>=1 AND
(INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END) OPEN_SINA ,--打開數-Sina
SUM(CASE WHEN OPEN>=1 AND
(INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END) OPEN_TOM ,--打開數-Tom
SUM(CASE WHEN OPEN>=1 AND
(INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END) OPEN_SOHU ,--打開數-Sohu
SUM(CASE WHEN OPEN>=1 AND
(INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END) OPEN_YAHOO_COM ,--打開數-YaHoo.com
SUM(CASE WHEN OPEN>=1 AND
(INSTR(EMAIL,'@yahoo.com.cn')>0 OR
INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END) OPEN_YAHOO_COMCN ,--打開數-YaHoo.com.cn
SUM(CASE WHEN OPEN>=1 AND
(INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END) OPEN_QQ ,--打開數-QQ
SUM(CASE WHEN OPEN>=1 AND
(INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
INSTR(EMAIL,'@msn')>0 OR INSTR(EMAIL,'@MSN')>0 ) THEN 1 ELSE 0 END) OPEN_HOTMAIL ,--打開數-Hotmail+MSN
SUM(CASE WHEN OPEN>=1 AND
(INSTR(EMAIL,'@21cn')>0 OR INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END) OPEN_21CN ,--打開數-21CN
*/
-----------------------------------------------------------------
----Count Of Soft Rebound
SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END) SOFT_163 ,--軟彈數-163
SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END) SOFT_126 ,--軟彈數-126
SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND
(INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END) SOFT_SINA ,--軟彈數-Sina
SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND
(INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END) SOFT_TOM ,--軟彈數-Tom
SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND
(INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END) SOFT_SOHU ,--軟彈數-Sohu
SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND
(INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END) SOFT_YAHOO_COM ,--軟彈數-YaHoo.com
SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND
(INSTR(EMAIL,'@yahoo.com.cn')>0 OR
INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END) SOFT_YAHOO_COMCN ,--軟彈數-YaHoo.com.cn
SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND
(INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END) SOFT_QQ ,--軟彈數-QQ
SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND
(INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
INSTR(EMAIL,'@msn')>0 OR INSTR(EMAIL,'@MSN')>0) THEN 1 ELSE 0 END) SOFT_HOTMAIL ,--軟彈數-Hotmail+MSN
SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND
(INSTR(EMAIL,'@21cn')>0 Or INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END) SOFT_21CN ,--軟彈數-21CN
-----------------------------------------------------------------
----Rate Of Open
(Case When SUM(CASE WHEN ACTIVE=1 THEN 1 ELSE 0 END) = 0 Then 0 Else
ROUND(SUM(OPEN)/SUM(CASE WHEN ACTIVE=1 THEN 1 ELSE 0 END),4) End) R_Open ,---打開比例
(Case When SUM(CASE WHEN ACTIVE=1 AND INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END)=0 Then 0 Else
ROUND(SUM(CASE WHEN OPEN>=1 AND INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END)
/SUM(CASE WHEN ACTIVE=1 AND INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END),4) End) R_Open_163 ,---打開比例-163
(Case When SUM(CASE WHEN ACTIVE=1 AND INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END)=0 Then 0 Else
ROUND(SUM(CASE WHEN OPEN>=1 AND INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END)
/SUM(CASE WHEN ACTIVE=1 AND INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END),4) End) R_Open_126 ,---打開比例-126
(Case When SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
ROUND(SUM(CASE WHEN OPEN>=1 AND (INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END)
/SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END),4)
End) R_Open_Sina ,---打開比例-Sina
(Case When SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
ROUND(SUM(CASE WHEN OPEN>=1 AND (INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END)
/SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END),4)
End) R_Open_Tom ,---打開比例-Tom
(Case When SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
ROUND(SUM(CASE WHEN OPEN>=1 AND (INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END)
/SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END),4)
End) R_Open_Sohu ,---打開比例-Sohu
(Case When SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END) = 0 Then 0 Else
ROUND(SUM(CASE WHEN OPEN>=1 AND (INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END)
/SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END),4) End) R_Open_YahooCom ,---打開比例-Yahoo.com
(Case When SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@yahoo.com.cn')>0 OR INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
ROUND(SUM(CASE WHEN OPEN>=1 AND (INSTR(EMAIL,'@yahoo.com.cn')>0 OR INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END)
/SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@yahoo.com.cn')>0 OR INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END),4)
End) R_Open_Yahoocomcn,---打開比例-Yahoo.com.cn
(Case When SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
ROUND(SUM(CASE WHEN OPEN>=1 AND (INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END)
/SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END),4)
End) R_Open_QQ ,---打開比例-QQ
(Case When SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
INSTR(EMAIL,'@msn')>0 OR INSTR(EMAIL,'@MSN')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
ROUND(SUM(CASE WHEN OPEN>=1 AND (INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
INSTR(EMAIL,'@msn')>0 OR INSTR(EMAIL,'@MSN')>0) THEN 1 ELSE 0 END)
/SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
INSTR(EMAIL,'@msn')>0 OR INSTR(EMAIL,'@MSN')>0) THEN 1 ELSE 0 END),4)
End) R_Open_HOTMAIL ,---打開比例-Hotmail
(Case When SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@21cn')>0 OR INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
ROUND(SUM(CASE WHEN OPEN>=1 AND (INSTR(EMAIL,'@21cn')>0 OR INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END)
/SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@21cn')>0 OR INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END),4)
End) R_Open_21CN ,---打開比例-21CN R_Open_Hotmail ,---打開比例-Hotmail
-----------------------------------------------------------------
----Rate Of Soft Rebound
(Case When COUNT(*) = 0 Then 0 Else
ROUND(SUM(CASE WHEN ACTIVE=-1 OR ACTIVE<-500 THEN 1 ELSE 0 END)
/COUNT(*),4) End) R_Soft ,---軟彈比例
(Case When SUM(CASE WHEN INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END) = 0 Then 0 Else
ROUND(SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END)
/SUM(CASE WHEN INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END),4) End) R_Soft_163 ,---軟彈比例-163
(Case When SUM(CASE WHEN INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END) = 0 Then 0 Else
ROUND(SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END)
/SUM(CASE WHEN INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END),4) End) R_Soft_126 ,---軟彈比例-126
(Case When SUM(CASE WHEN (INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END) = 0 Then 0 Else
ROUND(SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND (INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END)
/SUM(CASE WHEN (INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END),4)
End) R_Soft_Sina ,---軟彈比例-Sina
(Case When SUM(CASE WHEN (INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
ROUND(SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND (INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END)
/SUM(CASE WHEN (INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END),4)
End) R_Soft_Tom ,---軟彈比例-Tom
(Case When SUM(CASE WHEN (INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
ROUND(SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND (INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END)
/SUM(CASE WHEN (INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END),4)
End) R_Soft_Sohu ,---軟彈比例-Sohu
(Case When SUM(CASE WHEN (INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END) = 0 Then 0 Else
ROUND(SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND (INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END)
/SUM(CASE WHEN (INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END),4) End) R_Soft_YahooCom ,---軟彈比例-Yahoo.com
(Case When SUM(CASE WHEN (INSTR(EMAIL,'@yahoo.com.cn')>0 OR INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
ROUND(SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND (INSTR(EMAIL,'@yahoo.com.cn')>0 OR INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END)
/SUM(CASE WHEN (INSTR(EMAIL,'@yahoo.com.cn')>0 OR INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END),4)
End) R_Soft_Yahoocomcn,---軟彈比例-Yahoo.com.cn
(Case When SUM(CASE WHEN (INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
ROUND(SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND (INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END)
/SUM(CASE WHEN (INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END),4)
End) R_Soft_QQ ,---軟彈比例-QQ
(Case When SUM(CASE WHEN (INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
INSTR(EMAIL,'@msn')>0 OR INSTR(EMAIL,'@MSN')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
ROUND(SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND (INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
INSTR(EMAIL,'@msn')>0 OR INSTR(EMAIL,'@MSN')>0) THEN 1 ELSE 0 END)
/SUM(CASE WHEN (INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
INSTR(EMAIL,'@msn')>0 OR INSTR(EMAIL,'@MSN')>0) THEN 1 ELSE 0 END),4)
End) R_Soft_HOTMAIL ,---軟彈比例-Hotmail
(Case When SUM(CASE WHEN (INSTR(EMAIL,'@21cn')>0 OR INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
ROUND(SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND (INSTR(EMAIL,'@21cn')>0 OR INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END)
/SUM(CASE WHEN (INSTR(EMAIL,'@21cn')>0 OR INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END),4)
End) R_Soft_21CN ---軟彈比例-21CN
FROM DRIVEMAIL_SEND_TEMP ) A ,
(SELECT LOG_DATE SENDINGDATE ,
SUM(CASE WHEN OPEN>=1 THEN 1 ELSE 0 END) OPENCOUNT ,--打開數
SUM(CASE WHEN CLICK>=1 THEN 1 ELSE 0 END) CLICKCOUNT ,--點擊數
----Count Of Open
SUM(CASE WHEN OPEN>=1 AND INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END) OPEN_163 ,--打開數-163
SUM(CASE WHEN OPEN>=1 AND INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END) OPEN_126 ,--打開數-126
SUM(CASE WHEN OPEN>=1 AND
(INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END) OPEN_SINA ,--打開數-Sina
SUM(CASE WHEN OPEN>=1 AND
(INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END) OPEN_TOM ,--打開數-Tom
SUM(CASE WHEN OPEN>=1 AND
(INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END) OPEN_SOHU ,--打開數-Sohu
SUM(CASE WHEN OPEN>=1 AND
(INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END) OPEN_YAHOO_COM ,--打開數-YaHoo.com
SUM(CASE WHEN OPEN>=1 AND
(INSTR(EMAIL,'@yahoo.com.cn')>0 OR
INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END) OPEN_YAHOO_COMCN ,--打開數-YaHoo.com.cn
SUM(CASE WHEN OPEN>=1 AND
(INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END) OPEN_QQ ,--打開數-QQ
SUM(CASE WHEN OPEN>=1 AND
(INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
INSTR(EMAIL,'@msn')>0 OR INSTR(EMAIL,'@MSN')>0 ) THEN 1 ELSE 0 END) OPEN_HOTMAIL ,--打開數-Hotmail+MSN
SUM(CASE WHEN OPEN>=1 AND
(INSTR(EMAIL,'@21cn')>0 OR INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END) OPEN_21CN --打開數-21CN
FROM DRIVEMAIL_OPEN_TEMP) B
WHERE A.SENDINGDATE = B.SENDINGDATE;
COMMIT;
END Pro_Drivemail_log;