[已解決]MS-SQL欄位判別

ms sql tsql

seefood0329 2022-05-18 22:34:50 ‧ 1246 瀏覽

分享至

不好意思請教各位高手
目前我MSSQL的Forecast資料表筆數有50萬筆，訂單的筆數20萬筆
現在有個Forecast表格跟訂單資料表，有個邏輯是若是在每月5號之前，現在當年月份>Forecast當年月份要顯示在OrderQty/OrderAmt欄位，當月月份=<Forecast當年月份要顯示在Forecast Qty/ForecastaMT欄位，想請問這題是否能直接下T-SQL且performance高的解法?

範例如下：
假設今天在是2月，想要撈出1、2月 Forecast跟Order的比較表
Forecast表格

Order表格

希望求得的報表

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

rogeryao

iT邦超人 7 級 ‧ 2022-05-19 08:50:56

最佳解答

CREATE TABLE OrderTB (
id NVARCHAR(30),
YYYYMM NVARCHAR(30),
Order_ID NVARCHAR(30),
Item NVARCHAR(30),
Qty INT,
Amt INT);

INSERT INTO OrderTB 
VALUES
('1','202201','111001','Apple',15,150),
('2','202201','111001','Banana',40,400),
('3','202201','111002','Melon',60,600),
('4','202201','111003','Strawberry',50,500),
('5','202201','111004','Cherry',10,100),
('6','202202','111004','Melon',50,500),
-- 額外加入
('7','202201','111004','Mango',250,2500),
('8','202202','111004','Orange',1250,12500);

CREATE TABLE ForecastTB (
id NVARCHAR(30),	
YYYYMM NVARCHAR(30),	
Forecast_Version NVARCHAR(30),	
Item NVARCHAR(30),	
Qty INT,	
Amt INT);

INSERT INTO ForecastTB 
VALUES
('1','202201','Fll','Apple',15,150),
('2','202201','Fll','Banana',40,400),
('3','202201','Fll','Melon',60,600),
('4','202201','Fll','Strawberry',50,500),
('5','202201','Fll','Cherry',10,100),
('6','202202','Fll','Apple',20,150),
('7','202202','Fll','Banana',50,400),
('8','202202','Fll','Melon',30,600),
('9','202202','Fll','Strawberry',40,500),
('10','202202','Fll','Cherry',0,0);

DECLARE @MYGETDATE DATE
SET @MYGETDATE = '20220201'
-- SET @MYGETDATE = (SELECT DATEADD(DAY,-5,GETDATE()))

DECLARE @PREMONTH VARCHAR(6)
SET @PREMONTH = CONVERT(CHAR(6),DATEADD(MONTH,-1,@MYGETDATE),112)
DECLARE @ACTMONTH VARCHAR(6)
SET @ACTMONTH = CONVERT(CHAR(6),DATEADD(MONTH,0,@MYGETDATE),112)
--
SELECT ROW_NUMBER() OVER (ORDER BY D.YYYYMM,D.Item) AS NO,
D.YYYYMM,D.Item,
SUM(IIF(D.YYYYMM = @PREMONTH,0,D.FQty)) AS FQty,
SUM(IIF(D.YYYYMM = @PREMONTH,0,D.FAmt)) AS FAmt,
SUM(IIF(D.YYYYMM = @PREMONTH,D.FQty + D.OQty,D.OQty)) AS OQty,
SUM(IIF(D.YYYYMM = @PREMONTH,D.FAmt + D.OAmt,D.OAmt)) AS OAmt
FROM (
SELECT A.YYYYMM,A.Item,A.Qty AS FQty,A.Amt AS FAmt,0 AS OQty,0 AS OAmt
FROM ForecastTB AS A
WHERE A.YYYYMM = @PREMONTH
OR A.YYYYMM = @ACTMONTH
UNION ALL
SELECT B.YYYYMM,B.Item,0 AS FQty,0 AS FAmt,B.Qty AS OQty,B.Amt AS OAmt
FROM OrderTB AS B
WHERE B.YYYYMM = @PREMONTH
OR B.YYYYMM = @ACTMONTH) AS D
GROUP BY D.YYYYMM,D.Item
ORDER BY D.YYYYMM,D.Item

Demo

回應 2
分享
檢舉

seefood0329 iT邦新手 5 級 ‧ 2022-05-19 23:04:26 檢舉

太感謝前輩的詳細指教

seefood0329 iT邦新手 5 級 ‧ 2022-05-19 23:20:17 檢舉

自己實際在改寫時，發現如果按照自己的邏輯
範例應該求得的報表為如下：

依照rogeryao大提供的解法，修正如下

DECLARE @MYGETDATE DATE
SET @MYGETDATE = '20220201'
-- SET @MYGETDATE = (SELECT DATEADD(DAY,-5,GETDATE()))

DECLARE @PREMONTH VARCHAR(6)
SET @PREMONTH = CONVERT(CHAR(6),DATEADD(MONTH,-1,@MYGETDATE),112)
DECLARE @ACTMONTH VARCHAR(6)
SET @ACTMONTH = CONVERT(CHAR(6),DATEADD(MONTH,0,@MYGETDATE),112)
--
SELECT ROW_NUMBER() OVER (ORDER BY D.YYYYMM,D.Item) AS NO,
D.YYYYMM,D.Item,
IIF (D.YYYYMM = @PREMONTH,0,FQty) AS FQty,
IIF (D.YYYYMM = @PREMONTH,0,FAmt) AS FAmt,
IIF (D.YYYYMM = @PREMONTH,FQty + OQty,OQty) AS OQty,
IIF(D.YYYYMM= @PREMONTH,FAmt + OAmt, OAmt) AS OAmt
FROM (
SELECT C.YYYYMM,C.Item,
SUM(IIF(MYTYPE='F',FQty,0)) AS FQty,
SUM(IIF(MYTYPE='F',FAmt,0)) AS FAmt,
SUM(IIF(MYTYPE='O',OQty,0)) AS OQty,
SUM(IIF(MYTYPE='O',OAmt,0)) AS OAmt
FROM (
SELECT A.YYYYMM,A.Item,A.Qty AS FQty,A.Amt AS FAmt,
0 AS OQty,0 AS OAmt,'F' AS MYTYPE
FROM ForecastTB AS A
WHERE A.YYYYMM = @PREMONTH
OR A.YYYYMM = @ACTMONTH
UNION ALL
SELECT B.YYYYMM,B.Item,0 AS FQty,0 AS FAmt,
B.Qty AS OQty,B.Amt AS OAmt,'O' AS MYTYPE
FROM OrderTB AS B
WHERE B.YYYYMM = @PREMONTH
OR B.YYYYMM = @ACTMONTH) AS C
GROUP BY C.YYYYMM,C.Item) AS D
ORDER BY D.YYYYMM,D.Item

登入發表回應