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

出自TSK WIKI
跳至導覽 跳至搜尋
Root​(留言 | 貢獻)
Root​(留言 | 貢獻)
第17行: 第17行:
WHERE TD021='Y' AND TD001='2202' AND TC003 BETWEEN '20160101' AND '20161231'
WHERE TD021='Y' AND TD001='2202' AND TC003 BETWEEN '20160101' AND '20161231'
AND TC004='012A' AND TD006 LIKE '%TSC-095%'
AND TC004='012A' AND TD006 LIKE '%TSC-095%'
</SQL>
*預計結帳月計算
<SQL>
SELECT MA016,MA001,MA002,MA043,TD013,
(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 PRDATE,
TD001,TD002,TD003,TD004,TD005,TD008,TD010,TD011,TC008,TD012*TC009 AS TD012NT,
(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,TD013 AS TD013A,
TD016
FROM COPTD
JOIN COPTC ON TC001=TD001 AND TC002=TD002
JOIN INVMB ON MB001=TD004
JOIN COPMA ON MA001=TC004
WHERE TD021<>'V' AND TD001<>'400' AND MA001<>'518A' AND TD013 >'20151231'
</SQL>
</SQL>



於 2016年11月4日 (五) 12:25 的修訂

業務單位

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

<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,MA043,TD013, (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 PRDATE,

TD001,TD002,TD003,TD004,TD005,TD008,TD010,TD011,TC008,TD012*TC009 AS TD012NT, (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,TD013 AS TD013A, TD016

FROM COPTD JOIN COPTC ON TC001=TD001 AND TC002=TD002 JOIN INVMB ON MB001=TD004 JOIN COPMA ON MA001=TC004 WHERE TD021<>'V' AND TD001<>'400' AND MA001<>'518A' AND TD013 >'20151231' </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>