預交日延遲分析

出自TSK WIKI
跳至導覽 跳至搜尋

說明

以搜尋條件查詢加工單客戶預交日與加工預交日來分析訂單交期狀況,並以圖表方式來顯示。

搜尋條件

  • 客戶
  • 下單日區間
  • 預交日區間
  • 加工預交日區間

SQL

<sql> SELECT *,WKDAY2-WKDAY1 AS DIFF, (SELECT CASE WHEN WKDAY1<=0 THEN 0 WHEN WKDAY1>0 AND WKDAY1<=5 THEN 1 WHEN WKDAY1>5 AND WKDAY1<=10 THEN 2 WHEN WKDAY1>10 AND WKDAY1<=15 THEN 3 WHEN WKDAY1>15 AND WKDAY1<=20 THEN 4 WHEN WKDAY1>20 AND WKDAY1<=25 THEN 5 WHEN WKDAY1>25 AND WKDAY1<=30 THEN 6 WHEN WKDAY1>30 AND WKDAY1<=35 THEN 7 WHEN WKDAY1>35 AND WKDAY1<=40 THEN 8 WHEN WKDAY1>40 THEN 9 END)WK1, (SELECT CASE WHEN WKDAY2<=0 THEN 0 WHEN WKDAY2>0 AND WKDAY2<=5 THEN 1 WHEN WKDAY2>5 AND WKDAY2<=10 THEN 2 WHEN WKDAY2>10 AND WKDAY2<=15 THEN 3 WHEN WKDAY2>15 AND WKDAY2<=20 THEN 4 WHEN WKDAY2>20 AND WKDAY2<=25 THEN 5 WHEN WKDAY2>25 AND WKDAY2<=30 THEN 6 WHEN WKDAY2>30 AND WKDAY2<=35 THEN 7 WHEN WKDAY2>35 AND WKDAY2<=40 THEN 8 WHEN WKDAY2>40 THEN 9 END)WK2, (SELECT CASE WHEN WKDAY2-WKDAY1 <=0 THEN 0 WHEN WKDAY2-WKDAY1 >0 AND WKDAY2-WKDAY1 <=5 THEN 1 WHEN WKDAY2-WKDAY1 >5 AND WKDAY2-WKDAY1 <=10 THEN 2 WHEN WKDAY2-WKDAY1 >10 AND WKDAY2-WKDAY1 <=15 THEN 3 WHEN WKDAY2-WKDAY1 >15 AND WKDAY2-WKDAY1 <=20 THEN 4 WHEN WKDAY2-WKDAY1 >20 AND WKDAY2-WKDAY1 <=25 THEN 5 WHEN WKDAY2-WKDAY1 >25 AND WKDAY2-WKDAY1 <=30 THEN 6 WHEN WKDAY2-WKDAY1 >30 AND WKDAY2-WKDAY1 <=35 THEN 7 WHEN WKDAY2-WKDAY1 >35 AND WKDAY2-WKDAY1 <=40 THEN 8 WHEN WKDAY2-WKDAY1 >40 THEN 9 END)DF

FROM

(SELECT CAST(CAST(TD054 AS INTEGER)AS CHAR(9)) AS TA002,TC004, TC075,CAST(TD012*TC009 AS INTEGER) AS TWM,TC003,TD013,REPLACE(CD.UDF05,'/',)AS UDF05, DATEDIFF(DAY,TC003,TD013)- (SELECT COUNT(MP004) FROM CMSMP WHERE MP004 BETWEEN TC003 AND TD013) AS WKDAY1, DATEDIFF(DAY,TC003,CD.UDF05)- (SELECT COUNT(MP004) FROM CMSMP WHERE MP004 BETWEEN TC003 AND CD.UDF05) AS WKDAY2

					FROM v_COPTCD CD

WHERE TD054 > 15 AND UDF05 >0 )X //已建立views: v_CKDAYS </sql>