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

出自TSK WIKI
跳至導覽 跳至搜尋
Root​(留言 | 貢獻)
Root​(留言 | 貢獻)
第10行: 第10行:
ORDER BY  MA016
ORDER BY  MA016
</SQL>
</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>
==程式碼暫存==
==程式碼暫存==
*計算休假日
*計算休假日

於 2016年11月3日 (四) 16:44 的修訂

業務單位

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

<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 COUNT(MP004) AS MP004 FROM CMSMP WHERE MP004 BETWEEN '20161102' AND '20161107' </SQL>