官网 › 【MOD】汉之殇创世版 › 汉之殇——螃蟹阵！！（第五期）

xwolf56 发表于 2013-4-16 10:08:09

看看学习学习

差点挂了 发表于 2013-4-16 11:28:36

谢谢分享~

saymod 发表于 2013-4-16 11:58:27

ssssssssssssssssss

+++++++++ 发表于 2013-4-16 14:05:42

看看   是什么

2276960970 发表于 2013-4-16 14:12:01

CREATE or REPLACE VIEW scm_rpt_o_0270_vw    /*t4品番表（SCM_MTL_MATERIAL_TBL）
t1库存明细单（SCM_WMS_INV_PROC_DTL_TBL）t2库存处理单（SCM_WMS_INV_PROC_TBL）
t3采购订单（SCM_PO_ORDER_TBL）*/
AS
SELECT t4.RETROFIT_TYPE ,t4.DIST_TYPE,
DATE_FORMAT(CONVERT(CONCAT(t1.ACT_RCPT_DATE,'01'),datetime),'%Y') as YYYY,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '01' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_01,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '01' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_01,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '01' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '01' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '01' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_01,

CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '02' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_02,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '02' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_02,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '02' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '02' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '02' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_02,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '03' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_03,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '03' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_03,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '03' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '03' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '03' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_03,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '04' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_04,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '04' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_04,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '04' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '04' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '04' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_04,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '05' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_05,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '05' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_05,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '05' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '05' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '05' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_05,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '06' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_06,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '06' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_06,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '06' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '06' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '06' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_06,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '07' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_07,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '07' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_07,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '07' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '07' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '07' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_07,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '08' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_08,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '08' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_08,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '08' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '08' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '08' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_08,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '09' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_09,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '09' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_09,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '09' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '09' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '09' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_09,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '10' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_10,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '10' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_10,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '10' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '10' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '10' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_10,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '11' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_11,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '11' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_11,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '11' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '11' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '11' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_11,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '12' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_12,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '12' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_12,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '12' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '12' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '12' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_12,
t1.ID
   FROM SCM_WMS_INV_PROC_DTL_TBL t1 INNER JOIN
   SCM_WMS_INV_PROC_TBL t2 ON t1.BILL_ID=t2.ID
   INNER JOIN SCM_PO_ORDER_TBL t3 ON t1.DATA_SOURCE_ID=t3.ID
   INNER JOIN SCM_MTL_MATERIAL_TBL t4 ON t1.ITEM_ID=t4.ID
   WHERE t1.DATA_SOURCE_BILL='PO_ORDER'

lyb19900618 发表于 2013-4-16 14:16:03

围观围观

MOM 发表于 2013-4-16 14:16:13

好呵呵呵呵

听雨 发表于 2013-4-16 15:11:37

啥玩意啊

qiming12132013 发表于 2013-4-16 15:14:59

....................

ketl_888 发表于 2013-4-16 16:06:09

。。。。。。。。。。。。。。。。。。。。。。。。。。

wzy483751 发表于 2013-4-16 19:41:58

人法人认为

z370813326 发表于 2013-4-16 19:52:59

xdgfdgfvjhgjhbj

zhuzh3u 发表于 2013-4-17 09:52:37

kankanxuexi

djaafer 发表于 2013-4-17 10:42:16

看看吧我喜欢螃蟹

じ☆vの龍 发表于 2013-4-17 12:31:36

看看有没有用处！

魔鬼不哭泣 发表于 2013-4-17 12:46:08

看看，，，，学习下

xx- 发表于 2013-4-17 21:37:56

螃蟹阵

墨黑 发表于 2013-4-17 21:43:37

螃蟹阵第一次听说啊 看看先

藍鑽脆脆果果 发表于 2013-4-17 23:20:49

看看，学习学习{:soso_e102:}

李杜 发表于 2013-4-17 23:38:04

什么东西啊

查看完整版本: 汉之殇——螃蟹阵！！（第五期）