「統計報表程式碼」:修訂間差異
| 第20行: | 第20行: | ||
*預計結帳月計算 | *預計結帳月計算 | ||
<SQL> | <SQL> | ||
SELECT MA016,MA001,MA002, | SELECT MA016,MA001,MA002,TD001,TD002,TD003,TD013,MA043, | ||
(SELECT CASE | (SELECT CASE | ||
| 第41行: | 第41行: | ||
SUBSTRING(TD013,1,6) | SUBSTRING(TD013,1,6) | ||
END) AS | 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 | (SELECT CASE | ||
WHEN MB007='301' AND MA019<>'401' THEN 'FWH' | WHEN MB007='301' AND MA019<>'401' THEN 'FWH' | ||
| 第52行: | 第76行: | ||
WHEN MB007='302' AND MA019='401' THEN 'W' | WHEN MB007='302' AND MA019='401' THEN 'W' | ||
WHEN MB007='303' AND MA019='401' THEN 'P' | WHEN MB007='303' AND MA019='401' THEN 'P' | ||
ELSE MB007 END) AS MB007, | ELSE MB007 END) AS MB007 | ||
,TD008,TD010,TD011,TC008,TD012*TC009 AS TD012NT | |||
,TH008,TH012,TG011,TH013*TG012 AS TG012NT | |||
FROM COPTD | FROM COPTD | ||
JOIN COPTC ON TC001=TD001 AND TC002=TD002 | 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 INVMB ON MB001=TD004 | ||
JOIN COPMA ON MA001=TC004 | JOIN COPMA ON MA001=TC004 | ||
WHERE TD021<>'V' AND TD001<>'400' AND MA001<>'518A' AND TD013 >'20151231' | 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> | ||
於 2016年11月4日 (五) 13:06 的修訂
業務單位
- 統計訂單日及預交日訂單筆數跟金額
<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 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>