SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [Current] (
    Col1 VARCHAR(50),
    Col2 VARCHAR(10), 
    Col3 VARCHAR(2), 
    Col4 DATE,       
    Col5 INT,         
    Col6 NUMERIC(5,2)
);

INSERT INTO [Current] (Col1, Col2, Col3, Col4, Col5, Col6)
VALUES 
('ItemA', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-07-01', 12345, 10.50),
('ItemA', CASE WHEN LEFT('oH', 1) = UPPER(LEFT('oH', 1)) THEN 'Sell' ELSE 'Buy' END, 'oH', '2025-08-01', 23456, 20.75),
('ItemB', CASE WHEN LEFT('Br', 1) = UPPER(LEFT('Br', 1)) THEN 'Sell' ELSE 'Buy' END, 'Br', '2025-07-01', 34567, 30.80),
('ItemC', CASE WHEN LEFT('rB', 1) = UPPER(LEFT('rB', 1)) THEN 'Sell' ELSE 'Buy' END, 'rB', '2025-09-01', 45678, 40.25),
('ItemC', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-08-01', 56789, 50.60),
('ItemD', CASE WHEN LEFT('Br', 1) = UPPER(LEFT('Br', 1)) THEN 'Sell' ELSE 'Buy' END, 'Br', '2025-09-01', 67890, 60.10),
('ItemE', CASE WHEN LEFT('rB', 1) = UPPER(LEFT('rB', 1)) THEN 'Sell' ELSE 'Buy' END, 'rB', '2025-07-01', 78901, 70.95),
('ItemE', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-08-01', 89012, 15.35);
8 rows affected

CREATE TABLE [Previous] (
    Col1 VARCHAR(50),
    Col2 VARCHAR(10), 
    Col3 VARCHAR(2), 
    Col4 DATE,       
    Col5 INT,         
    Col6 NUMERIC(5,2)
);

INSERT INTO [Previous] (Col1, Col2, Col3, Col4, Col5, Col6)
VALUES 
('ItemA', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-07-01', 12350, 10.55),
('ItemA', CASE WHEN LEFT('oH', 1) = UPPER(LEFT('oH', 1)) THEN 'Sell' ELSE 'Buy' END, 'oH', '2025-08-01', 23461, 20.80),
('ItemB', CASE WHEN LEFT('Br', 1) = UPPER(LEFT('Br', 1)) THEN 'Sell' ELSE 'Buy' END, 'Br', '2025-07-01', 34572, 30.85),
('ItemC', CASE WHEN LEFT('rB', 1) = UPPER(LEFT('rB', 1)) THEN 'Sell' ELSE 'Buy' END, 'rB', '2025-09-01', 45683, 40.30),
('ItemC', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-08-01', 56794, 50.65),
('ItemD', CASE WHEN LEFT('Br', 1) = UPPER(LEFT('Br', 1)) THEN 'Sell' ELSE 'Buy' END, 'Br', '2025-09-01', 67905, 60.15),
('ItemE', CASE WHEN LEFT('rB', 1) = UPPER(LEFT('rB', 1)) THEN 'Sell' ELSE 'Buy' END, 'rB', '2025-07-01', 78916, 70.90),
('ItemE', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-08-01', 89027, 15.40);
8 rows affected

WITH cte AS (
    SELECT Col1, Col5 [Current Sum], NULL [Previous Sum]
    FROM [Current]
    UNION ALL
    SELECT Col1, NULL, Col5
    FROM [Previous]
)
SELECT
    Col1
    , SUM([Current Sum]) [Current Sum]
    , SUM([Previous Sum]) [Previous Sum]
    , SUM([Previous Sum]) - SUM([Current Sum]) Difference
FROM cte
GROUP BY Col1
ORDER BY Col1


Col1
Current Sum
Previous Sum
Difference


ItemA
35801
35811
10

ItemB
34567
34572
5

ItemC
102467
102477
10

ItemD
67890
67905
15

ItemE
167913
167943
30


Warning: Null value is eliminated by an aggregate or other SET operation.
Col1	Current Sum	Previous Sum	Difference
ItemA	35801	35811	10
ItemB	34567	34572	5
ItemC	102467	102477	10
ItemD	67890	67905	15
ItemE	167913	167943	30