「銷售實績」:修訂間差異
新頁面: ==銷售預估== <sql> SELECT MA001,MA002,TD013,TD001,TD002,TD003,TD004,TD008,TD010,TD011,TD012*TC009 AS TD012NT, (SELECT CASE WHEN MB007='301' AND MA019<>'401' THEN 'FWH' WHEN MB007... |
|||
| (未顯示同一使用者於中間所作的 3 次修訂) | |||
| 第1行: | 第1行: | ||
==銷售實績(不含銷退)== | |||
*v_KINDOFCOPTH | |||
<sql> | |||
SELECT MA001,MA002,TG003,TH014,TH015,TH016,TH005,TH008,TH009,TH012,TH037, | |||
(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='302' AND MA019='401' THEN 'W' | |||
WHEN MB007='303' AND MA019='401' THEN 'P' | |||
ELSE MB007 END) AS MB007,statementDATE,TH001,TH002,TH003,TH020 | |||
FROM v_COPTGH | |||
JOIN INVMB ON MB001=TH004 | |||
JOIN COPMA ON MA001=TG004 | |||
LEFT JOIN | |||
(SELECT TB001,TB002,TB003,TA003,TA038,TA004,MA043, | |||
(SELECT CASE WHEN CAST(SUBSTRING(TA003,7,2) AS INTEGER) > MA043 THEN SUBSTRING(CONVERT(varchar(100),DATEADD(MONTH,+1,TA003),112),1,6) | |||
ELSE | |||
SUBSTRING(TA003,1,6) END) AS statementDATE | |||
FROM v_ACRTABNOTV JOIN COPMA ON MA001=TA004)X | |||
ON TB001=TH027 AND TB002=TH028 AND TB003=TH029 | |||
</sql> | |||
==銷售實績(含銷退)== | |||
*v_KINDOFCOPTH | |||
<sql> | |||
SELECT MA001,MA002,TG003,TH014,TH015,TH016,TH005,TH008,TH009,TH012,TH037, | |||
(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='302' AND MA019='401' THEN 'W' | |||
WHEN MB007='303' AND MA019='401' THEN 'P' | |||
ELSE MB007 END) AS MB007,statementDATE,TH001,TH002,TH003,TH020 | |||
FROM( | |||
SELECT TH001,TH002,TH003,TH004,TG003,TH014,TH015,TH016,TH005,TH008,TH009,TH012,TH037,TH020,TG004,TH027,TH028,TH029 | |||
FROM v_COPTGH | |||
UNION ALL | |||
SELECT TJ001,TJ002,TJ003,TJ004,TI003,TJ018,TJ019,TJ020,TJ005,TJ007,TJ008,TJ011*-1 AS TJ011,TJ033*-1 AS TJ033,TJ021,TI004,TJ025,TJ026,TJ027 | |||
FROM COPTJ JOIN COPTI ON TJ001=TI001 AND TJ002=TI002 | |||
WHERE TJ021<>'V' AND TI019<>'V' | |||
)Y | |||
JOIN INVMB ON MB001=TH004 | |||
JOIN COPMA ON MA001=TG004 | |||
LEFT JOIN | |||
(SELECT TB001,TB002,TB003,TA003,TA038,TA004,MA043, | |||
(SELECT CASE WHEN CAST(SUBSTRING(TA003,7,2) AS INTEGER) > MA043 THEN SUBSTRING(CONVERT(varchar(100),DATEADD(MONTH,+1,TA003),112),1,6) | |||
ELSE | |||
SUBSTRING(TA003,1,6) END) AS statementDATE | |||
FROM v_ACRTABNOTV JOIN COPMA ON MA001=TA004)X | |||
ON TB001=TH027 AND TB002=TH028 AND TB003=TH029 | |||
</sql> | |||
==銷售預估== | ==銷售預估== | ||
<sql> | <sql> | ||
| 第13行: | 第71行: | ||
WHEN MA001='0156' THEN SUBSTRING(CONVERT(varchar(100),DATEADD(MONTH, 1,TD013), 112),1,6) | WHEN MA001='0156' THEN SUBSTRING(CONVERT(varchar(100),DATEADD(MONTH, 1,TD013), 112),1,6) | ||
WHEN MA001='197B' THEN SUBSTRING(TD013,1,6) | WHEN MA001='197B' THEN SUBSTRING(TD013,1,6) | ||
WHEN CONVERT( | WHEN SUBSTRING(CONVERT( | ||
varchar(100), | varchar(100), | ||
(SELECT CASE | (SELECT CASE | ||
| 第24行: | 第82行: | ||
(SELECT MP004 FROM CMSMP WHERE MP004=CONVERT(varchar(100),DATEADD(DAY, 1, TD013),112)) THEN DATEADD(DAY, 2, TD013) | (SELECT MP004 FROM CMSMP WHERE MP004=CONVERT(varchar(100),DATEADD(DAY, 1, TD013),112)) THEN DATEADD(DAY, 2, TD013) | ||
ELSE DATEADD(DAY, 1, TD013) | ELSE DATEADD(DAY, 1, TD013) | ||
END), 112) > MA043 | END), 112),7,2) > MA043 | ||
THEN SUBSTRING(CONVERT(varchar(100),DATEADD(MONTH, 1, TD013),112),1,6) | THEN SUBSTRING(CONVERT(varchar(100),DATEADD(MONTH, 1, TD013),112),1,6) | ||
於 2016年1月21日 (四) 17:18 的最新修訂
銷售實績(不含銷退)
- v_KINDOFCOPTH
<sql> SELECT MA001,MA002,TG003,TH014,TH015,TH016,TH005,TH008,TH009,TH012,TH037, (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='302' AND MA019='401' THEN 'W' WHEN MB007='303' AND MA019='401' THEN 'P' ELSE MB007 END) AS MB007,statementDATE,TH001,TH002,TH003,TH020 FROM v_COPTGH JOIN INVMB ON MB001=TH004 JOIN COPMA ON MA001=TG004 LEFT JOIN
(SELECT TB001,TB002,TB003,TA003,TA038,TA004,MA043,
(SELECT CASE WHEN CAST(SUBSTRING(TA003,7,2) AS INTEGER) > MA043 THEN SUBSTRING(CONVERT(varchar(100),DATEADD(MONTH,+1,TA003),112),1,6)
ELSE
SUBSTRING(TA003,1,6) END) AS statementDATE FROM v_ACRTABNOTV JOIN COPMA ON MA001=TA004)X
ON TB001=TH027 AND TB002=TH028 AND TB003=TH029 </sql>
銷售實績(含銷退)
- v_KINDOFCOPTH
<sql> SELECT MA001,MA002,TG003,TH014,TH015,TH016,TH005,TH008,TH009,TH012,TH037, (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='302' AND MA019='401' THEN 'W' WHEN MB007='303' AND MA019='401' THEN 'P' ELSE MB007 END) AS MB007,statementDATE,TH001,TH002,TH003,TH020
FROM( SELECT TH001,TH002,TH003,TH004,TG003,TH014,TH015,TH016,TH005,TH008,TH009,TH012,TH037,TH020,TG004,TH027,TH028,TH029 FROM v_COPTGH UNION ALL SELECT TJ001,TJ002,TJ003,TJ004,TI003,TJ018,TJ019,TJ020,TJ005,TJ007,TJ008,TJ011*-1 AS TJ011,TJ033*-1 AS TJ033,TJ021,TI004,TJ025,TJ026,TJ027 FROM COPTJ JOIN COPTI ON TJ001=TI001 AND TJ002=TI002 WHERE TJ021<>'V' AND TI019<>'V' )Y
JOIN INVMB ON MB001=TH004 JOIN COPMA ON MA001=TG004 LEFT JOIN
(SELECT TB001,TB002,TB003,TA003,TA038,TA004,MA043,
(SELECT CASE WHEN CAST(SUBSTRING(TA003,7,2) AS INTEGER) > MA043 THEN SUBSTRING(CONVERT(varchar(100),DATEADD(MONTH,+1,TA003),112),1,6)
ELSE
SUBSTRING(TA003,1,6) END) AS statementDATE FROM v_ACRTABNOTV JOIN COPMA ON MA001=TA004)X
ON TB001=TH027 AND TB002=TH028 AND TB003=TH029 </sql>
銷售預估
<sql> SELECT MA001,MA002,TD013,TD001,TD002,TD003,TD004,TD008,TD010,TD011,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='302' AND MA019='401' THEN 'W' WHEN MB007='303' AND MA019='401' THEN 'P' ELSE MB007 END) AS MB007,TD013 AS TD013A,MA043, (SELECT CASE WHEN MA001='0156' THEN SUBSTRING(CONVERT(varchar(100),DATEADD(MONTH, 1,TD013), 112),1,6) WHEN MA001='197B' THEN SUBSTRING(TD013,1,6) 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),7,2) > MA043
THEN SUBSTRING(CONVERT(varchar(100),DATEADD(MONTH, 1, TD013),112),1,6) ELSE
SUBSTRING(TD013,1,6)
END) AS PRDATE
FROM v_COPTCD JOIN INVMB ON MB001=TD004 JOIN COPMA ON MA001=TC004 WHERE TD021<>'V' AND TD001<>'400' </sql>