SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- INVENTORY TABLE DECLARATION AND DATA INSERTION
DECLARE @COLORSIZEQTYS TABLE(ITEID int, COLORCODE varchar(15), QTYMODE smallint, SIZE4 float, SIZE5 float, SIZE6 float, SIZE7 float);

INSERT @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(5594, 'Grey',  1, -2, -1,  3,  4);
INSERT @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(5594, 'Red',   1,  2,  5,  5,  2);
INSERT @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(5594, 'Green', 1,  2,  2,  4, -1);
INSERT @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(5594, 'Grey',  0,  2,  0, -1,  1);
INSERT @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(5594, 'White', 0,  2,  3,  3,  0);
INSERT @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(5594, 'Blue',  1, -1,  6,  0,  3);
INSERT @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(5594, 'Grey',  1,  7,  2, -1,  4);
INSERT @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(5594, 'Grey',  1,  1,  0, -1, -1);

-- PURCHASES TABLE DECLARATION AND DATA INSERTION
DECLARE @STORECOLORSIZEEST TABLE(ID int, ITEID int, COLORCODE varchar(15), SIZE4 float, SIZE5 float, SIZE6 float, SIZE7 float);

INSERT @STORECOLORSIZEEST(ID, ITEID, COLORCODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(1703, 5594, 'Grey', 0, 1, 0, 1);
INSERT @STORECOLORSIZEEST(ID, ITEID, COLORCODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(1704, 6811, 'Red',  0, 1, 0, 1);
INSERT @STORECOLORSIZEEST(ID, ITEID, COLORCODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(1706, 5594, 'Grey', 0, 1, 1, 1);
INSERT @STORECOLORSIZEEST(ID, ITEID, COLORCODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(1707, 5594, 'Grey', 1, 1, 0, 1);
INSERT @STORECOLORSIZEEST(ID, ITEID, COLORCODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(1709, 8372, 'Blue', 0, 1, 0, 1);
INSERT @STORECOLORSIZEEST(ID, ITEID, COLORCODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(1712, 5594, 'Grey', 0, 0, 1, 1);

-- QUERY SUMMING INVENTORY ROWS
SELECT SUM(SIZE4) AS QTY4,
       SUM(SIZE5) AS QTY5,
       SUM(SIZE6) AS QTY6,
       SUM(SIZE7) AS QTY7
FROM @COLORSIZEQTYS
WHERE ITEID = 5594
AND COLORCODE = 'Grey'
AND QTYMODE = 1

-- QUERY SUMMING INVENTORY ROWS AND ZERO-ING NEGATIVE VALUES
SELECT SUM(CASE WHEN SIZE4 < 0 THEN 0 ELSE SIZE4 END) AS QTY4,
       SUM(CASE WHEN SIZE5 < 0 THEN 0 ELSE SIZE5 END) AS QTY5,
       SUM(CASE WHEN SIZE6 < 0 THEN 0 ELSE SIZE6 END) AS QTY6,
       SUM(CASE WHEN SIZE7 < 0 THEN 0 ELSE SIZE7 END) AS QTY7
FROM @COLORSIZEQTYS
WHERE ITEID = 5594
AND COLORCODE = 'Grey'
AND QTYMODE = 1

-- QUERY SUMMING PURCHASE ROWS
SELECT SUM(SIZE4) AS RSV4,
       SUM(SIZE5) AS RSV5,
       SUM(SIZE6) AS RSV6,
       SUM(SIZE7) AS RSV7
FROM @STORECOLORSIZEEST
WHERE ITEID = 5594
AND COLORCODE = 'Grey'

-- SUBTRACTING THE TWO SUMS TO FIND THE FINAL INVENTORY QUANTITIES - CLEARLY THIS DOESN'T WORK
SELECT (SUM(CASE WHEN inv.SIZE4 < 0 THEN 0 ELSE inv.SIZE4 END) - SUM(pcs.SIZE4)) AS TOT4,
       (SUM(CASE WHEN inv.SIZE5 < 0 THEN 0 ELSE inv.SIZE5 END) - SUM(pcs.SIZE5)) AS TOT5,
       (SUM(CASE WHEN inv.SIZE6 < 0 THEN 0 ELSE inv.SIZE6 END) - SUM(pcs.SIZE6)) AS TOT6,
       (SUM(CASE WHEN inv.SIZE7 < 0 THEN 0 ELSE inv.SIZE7 END) - SUM(pcs.SIZE7)) AS TOT7
FROM @COLORSIZEQTYS AS inv
INNER JOIN @STORECOLORSIZEEST AS pcs ON pcs.ITEID = inv.ITEID AND pcs.COLORCODE = inv.COLORCODE
WHERE inv.ITEID = 5594 AND inv.COLORCODE = 'Grey' AND inv.QTYMODE = 1

QTY4	QTY5	QTY6	QTY7
6	1	1	7

QTY4	QTY5	QTY6	QTY7
8	2	3	8

RSV4	RSV5	RSV6	RSV7
1	3	2	4

TOT4	TOT5	TOT6	TOT7
29	-1	6	20