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

出自TSK WIKI
跳至導覽 跳至搜尋
Root​(留言 | 貢獻)
Root​(留言 | 貢獻)
 
(未顯示同一使用者於中間所作的 10 次修訂)
第10行: 第10行:
ORDER BY  MA016
ORDER BY  MA016
</SQL>
</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>
<SQL>
SELECT TC004,TD001,TD002,TD003,TC003,TD013,DATEDIFF(DAY,TC003,TD013)-
SELECT TC004,TD001,TD002,TD003,TC003,TD013,DATEDIFF(DAY,TC003,TD013)-  
(SELECT CASE
(SELECT COUNT(MP004) FROM CMSMP WHERE MP004 BETWEEN TC003 AND TD013) AS WORKDAY  
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  
FROM COPTD  
JOIN COPTC ON TC001=TD001 AND TC002=TD002
JOIN COPTC ON TC001=TD001 AND TC002=TD002
</SQL>
</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>
<SQL>
第100行: 第123行:
AND (TH020<>'V' OR TH020 IS NULL)
AND (TH020<>'V' OR TH020 IS NULL)
AND (TG003>'20151231' OR TG003 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>


第112行: 第150行:
JOIN COPTG ON TH001=TG001 AND TH002=TG002
JOIN COPTG ON TH001=TG001 AND TH002=TG002
WHERE TH002 >'161015001' AND TG023<>'V'
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>
*原預交日未到已入庫
<SQL>
SELECT TD054,TC075,TD016,TD001,TD002,TD003,TD013,TD004,TD005,TD012*TC009 MONEY,TD008,TD009,TD009*TD011*TC009 AS M2,
TA015,TA017,TD011*TA017*TC009 AS M1,TD020,TA510,TA511 FROM v_COPTCD
JOIN MOCTA ON TA001='5102' AND TA002=TD054
WHERE TD021='Y' AND TD013 >'20170620' AND TA013='Y' AND TA017>0
ORDER BY TD013
</SQL>
</SQL>

於 2017年6月13日 (二) 10:41 的最新修訂

業務單位

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

<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>

  • 原預交日未到已入庫

<SQL> SELECT TD054,TC075,TD016,TD001,TD002,TD003,TD013,TD004,TD005,TD012*TC009 MONEY,TD008,TD009,TD009*TD011*TC009 AS M2, TA015,TA017,TD011*TA017*TC009 AS M1,TD020,TA510,TA511 FROM v_COPTCD JOIN MOCTA ON TA001='5102' AND TA002=TD054 WHERE TD021='Y' AND TD013 >'20170620' AND TA013='Y' AND TA017>0 ORDER BY TD013 </SQL>