不好意思請教各位高手
目前我MSSQL的Forecast資料表筆數有50萬筆,訂單的筆數20萬筆
現在有個Forecast表格跟訂單資料表,有個邏輯是若是在每月5號之前,現在當年月份>Forecast當年月份要顯示在OrderQty/OrderAmt欄位,當月月份=<Forecast當年月份要顯示在Forecast Qty/ForecastaMT欄位,想請問這題是否能直接下T-SQL且performance高的解法?
範例如下:
假設今天在是2月,想要撈出1、2月 Forecast跟Order的比較表
Forecast表格
Order表格
希望求得的報表
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
太感謝前輩的詳細指教
自己實際在改寫時,發現如果按照自己的邏輯
範例應該求得的報表為如下:
依照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