「統計報表程式碼」:修訂間差異

出自TSK WIKI
跳至導覽 跳至搜尋
Root​(留言 | 貢獻)
Root​(留言 | 貢獻)
第9行: 第9行:
JOIN CMSMV ON MV001=MA016
JOIN CMSMV ON MV001=MA016
ORDER BY  MA016
ORDER BY  MA016
</SQL>
*統計訂單日與預交日扣除假日的差日
<SQL>
SELECT TC004,TD001,TD002,TD003,TC003,TD013,DATEDIFF(DAY,TC003,TD013)-
(SELECT CASE
WHEN EXISTS
(SELECT MP004 FROM CMSMP WHERE MP004 BETWEEN TC003 AND TD013)
THEN (SELECT COUNT(MP004)AS MP004 FROM CMSMP WHERE MP004 BETWEEN TC003 AND TD013)
ELSE '0'END) AS WORKDAY
FROM COPTD
JOIN COPTC ON TC001=TD001 AND TC002=TD002
</SQL>
</SQL>
*卷線訂單日期與交期差日
*卷線訂單日期與交期差日

於 2016年11月7日 (一) 12:23 的修訂

業務單位

  • 統計訂單日及預交日訂單筆數跟金額

<SQL> SELECT MA002,QU,MONEY,MV002 FROM (SELECT TC004,COUNT(TC004)AS QU,SUM(TD076*TD011*TC009)AS MONEY FROM COPTD JOIN COPTC ON TC001=TD001 AND TC002=TD002 WHERE TD013 LIKE '201611%' AND TD021='Y' AND TD016<>'y' AND TD002 BETWEEN '161101000' AND '161102999' AND TD001='2202' GROUP BY TC004)X JOIN COPMA ON MA001=TC004 JOIN CMSMV ON MV001=MA016 ORDER BY MA016 </SQL>

  • 統計訂單日與預交日扣除假日的差日

<SQL> SELECT TC004,TD001,TD002,TD003,TC003,TD013,DATEDIFF(DAY,TC003,TD013)- (SELECT CASE WHEN EXISTS (SELECT MP004 FROM CMSMP WHERE MP004 BETWEEN TC003 AND TD013) THEN (SELECT COUNT(MP004)AS MP004 FROM CMSMP WHERE MP004 BETWEEN TC003 AND TD013) ELSE '0'END) AS WORKDAY FROM COPTD JOIN COPTC ON TC001=TD001 AND TC002=TD002 </SQL>

  • 卷線訂單日期與交期差日

<SQL> SELECT TD001,TD002,TD003,TD006,TD008,TC003,TD013, DATEDIFF(d,Convert(Datetime,TC003,112),Convert(Datetime,TD013,112))AS DIF FROM COPTD JOIN COPTC ON TC001=TD001 AND TC002=TD002 WHERE TD021='Y' AND TD001='2202' AND TC003 BETWEEN '20160101' AND '20161231' AND TC004='012A' AND TD006 LIKE '%TSC-095%' </SQL>

  • 預計結帳月計算

<SQL> SELECT MA016,MA001,MA002,TD001,TD002,TD003,TD013,MA043, (SELECT CASE

WHEN SUBSTRING(CONVERT( VARCHAR(100), (SELECT CASE WHEN EXISTS (SELECT MP004 FROM CMSMP WHERE MP004=CONVERT(VARCHAR(100),DATEADD(DAY, 1, TD013),112)) AND EXISTS (SELECT MP004 FROM CMSMP WHERE MP004=CONVERT(VARCHAR(100),DATEADD(DAY, 2, TD013),112)) THEN DATEADD(DAY, 3, TD013)

 WHEN EXISTS 

(SELECT MP004 FROM CMSMP WHERE MP004=CONVERT(VARCHAR(100),DATEADD(DAY, 1, TD013),112)) THEN DATEADD(DAY, 2, TD013) ELSE DATEADD(DAY, 1, TD013) END), 112),5,4) > SUBSTRING(TD013,5,2)+ MA043

THEN SUBSTRING(CONVERT(VARCHAR(100),DATEADD(MONTH, 1, TD013),112),1,6)

ELSE

SUBSTRING(TD013,1,6)

END) AS COPTDMONTH,TD016,TH020,TH026,TH001,TH002,TH003, (SELECT CASE

WHEN SUBSTRING(CONVERT( VARCHAR(100), (SELECT CASE WHEN EXISTS (SELECT MP004 FROM CMSMP WHERE MP004=CONVERT(VARCHAR(100),DATEADD(DAY, 1, TG003),112)) AND EXISTS (SELECT MP004 FROM CMSMP WHERE MP004=CONVERT(VARCHAR(100),DATEADD(DAY, 2, TG003),112)) THEN DATEADD(DAY, 3, TG003)

 WHEN EXISTS 

(SELECT MP004 FROM CMSMP WHERE MP004=CONVERT(VARCHAR(100),DATEADD(DAY, 1, TG003),112)) THEN DATEADD(DAY, 2, TG003) ELSE DATEADD(DAY, 1, TG003) END), 112),5,4) > SUBSTRING(TG003,5,2)+ MA043

THEN SUBSTRING(CONVERT(VARCHAR(100),DATEADD(MONTH, 1, TG003),112),1,6)

ELSE

SUBSTRING(TG003,1,6)

END) AS COPTHMONTH,


TD004,TD005 , (SELECT CASE WHEN MB007='301' AND MA019<>'401' THEN 'FWH' WHEN MB007='302' AND MA019<>'401' THEN 'FW' WHEN MB007='303' AND MA019<>'401' THEN 'FP' WHEN MB007='301' AND MA019='401' THEN 'WH' WHEN MB007='301' AND MA019='401' THEN 'WH' WHEN MB007='302' AND MA019='401' THEN 'W' WHEN MB007='303' AND MA019='401' THEN 'P' ELSE MB007 END) AS MB007 ,TD008,TD010,TD011,TC008,TD012*TC009 AS TD012NT ,TH008,TH012,TG011,TH013*TG012 AS TG012NT

FROM COPTD JOIN COPTC ON TC001=TD001 AND TC002=TD002 LEFT JOIN COPTH ON TH014=TD001 AND TH015=TD002 AND TH016=TD003 LEFT JOIN COPTG ON TG001=TH001 AND TG002=TH002 JOIN INVMB ON MB001=TD004 JOIN COPMA ON MA001=TC004 WHERE TD021<>'V' AND TD001<>'400' AND MA001<>'518A' AND TD013 >'20151231' AND (TH020<>'V' OR TH020 IS NULL) AND (TG003>'20151231' OR TG003 IS NULL) </SQL>

程式碼暫存

  • 計算休假日

<SQL> SELECT COUNT(MP004) AS MP004 FROM CMSMP WHERE MP004 BETWEEN '20161102' AND '20161107' </SQL>

  • 計算銷貨日與結帳月

<SQL> SELECT TH001,TH002,TH003,TH004,TG003 FROM COPTH JOIN COPTG ON TH001=TG001 AND TH002=TG002 WHERE TH002 >'161015001' AND TG023<>'V' </SQL>