SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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);

SELECT * 
FROM OrderTB


id
YYYYMM
Order_ID
Item
Qty
Amt


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

SELECT * 
FROM ForecastTB


id
YYYYMM
Forecast_Version
Item
Qty
Amt


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


NO
YYYYMM
Item
FQty
FAmt
OQty
OAmt


1
202201
Apple
0
0
30
300

2
202201
Banana
0
0
80
800

3
202201
Cherry
0
0
20
200

4
202201
Mango
0
0
250
2500

5
202201
Melon
0
0
120
1200

6
202201
Strawberry
0
0
100
1000

7
202202
Apple
20
150
0
0

8
202202
Banana
50
400
0
0

9
202202
Cherry
0
0
0
0

10
202202
Melon
30
600
50
500

11
202202
Orange
0
0
1250
12500

12
202202
Strawberry
40
500
0
0
id	YYYYMM	Order_ID	Item	Qty	Amt
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