統計報表程式碼

出自TSK WIKI
於 2017年1月7日 (六) 21:17 由 Root​(留言 | 貢獻) 所做的修訂 (→業務單位)
跳至導覽 跳至搜尋

業務單位

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

<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 MA016,MB007,TA003,ISNULL(SUM(TH037+TH038),0) AS TH037 FROM (SELECT MA016,MB007,TH001,TH002,TH003,TH037,TH038,TA040,TA041,TA001,TA002, (SELECT CASE WHEN SUBSTRING(TA003, 7, 2)> MA043 THEN SUBSTRING(Convert(varchar,DATEADD(MONTH,1,TA003),112), 1, 6) ELSE SUBSTRING(TA003,1,6) END) AS TA003,TA004,TA025 FROM ACRTA JOIN COPMA ON MA001=TA004 JOIN (SELECT TH001,TH002,TH003,TH004,TH027,TH028,TH037,TH038 FROM COPTH WHERE TH020='Y'

UNION ALL
SELECT TJ001,TJ002,TJ003,TJ004,TJ025,TJ026,TJ033,TJ034 FROM COPTJ WHERE TJ021='Y'

)Y JOIN INVMB ON TH004=MB001 ON TH027=TA001 AND TH028=TA002 WHERE TA025='Y' )X WHERE TA003 LIKE '2016%' GROUP BY TA003,MB007,MA016 </sql>

  • 計算訂單日與預交日間的差日,並扣除假日數

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

  • 計算差日,並加入客戶名稱、預交日。

<SQL> SELECT TC004,TC075,TD001,TD002,TD003,TD004,TD005,TD006,TC003,TD013, DATEDIFF(DAY,TC003,TD013)- (SELECT COUNT(MP004) FROM CMSMP WHERE MP004 BETWEEN TC003 AND TD013) AS WORKDAY FROM COPTD JOIN COPTC ON TC001=TD001 AND TC002=TD002 WHERE TD001='2202' AND TD021='Y' AND TC003='20161124' AND TD016<>'V' </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>

  • 查年應收1百萬

<sql> SELECT TA004,TMONEY FROM (SELECT TA004,SUM(TA4142) AS TMONEY FROM v_SUMEACHECOMPSALL WHERE THEMONTH LIKE '2016%' GROUP BY TA004)X WHERE TMONEY >'1000000' </sql>

  • 查年2016應收1百萬元之客戶,依單位、工作日天數分類

<SQL> SELECT MA002,WORKDAY,TD010,COUNT(TD010)AS UNITNUM,SUM(TD008*TD011)AS MONEY FROM v_NEWCOPTCD JOIN (SELECT TA004,TMONEY FROM (SELECT TA004,SUM(TA4142) AS TMONEY FROM v_SUMEACHECOMPSALL WHERE THEMONTH LIKE '2016%' GROUP BY TA004)X WHERE TMONEY <'1000000' AND TMONEY >'0')X ON TA004=TC004 WHERE COPTDDATE BETWEEN '20160101' AND '20161231' AND TD001='2202' AND TD021='Y' AND TD016<>'y' AND TD010<>'M' GROUP BY MA002,WORKDAY,TD010 ORDER BY MA002,TD010,WORKDAY </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>

  • 常用電線

<SQL> SELECT TB003,TIMES,AMOUNT,MC007 FROM ( SELECT TB003,COUNT(TB003)AS TIMES,SUM(TB004) AS AMOUNT FROM (SELECT TD001,TD002,TD003,TD004,TD005,TD006,TB003,TB004 FROM v_COPTCD JOIN MOCTA ON TA001='5102' AND TA002=TD054 JOIN MOCTB ON TA001=TB001 AND TA002=TB002 JOIN INVMB ON TB003=MB001 WHERE MB007='302' AND TB003 NOT LIKE '6%' AND TA013='Y' AND TA011<>'v' AND TD002 LIKE '16%' )X GROUP BY TB003 )Y LEFT JOIN INVMC ON TB003=MC001 WHERE MC002='01' ORDER BY TIMES DESC </SQL>