檢視 統計報表程式碼 的原始碼
←
統計報表程式碼
跳至導覽
跳至搜尋
由於以下原因,您無權編輯此頁面:
您請求的操作只有這個群組的使用者能使用:
使用者
您可以檢視並複製此頁面的原始碼。
==業務單位== *統計訂單日及預交日訂單筆數跟金額 <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>
返回到「
統計報表程式碼
」。
導覽選單
個人工具
建立帳號
登入
命名空間
頁面
討論
臺灣正體
視圖
閱讀
檢視原始碼
檢視歷史
更多
搜尋
導覽
首頁
近期變更
隨機頁面
MediaWiki說明
工具
連結至此的頁面
相關變更
特殊頁面
頁面資訊