SQL Server 2017

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


(No column name)


Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64) 
	Jan 23 2020 21:00:04 
	Copyright (C) 2017 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table stock_items (id varchar(4), stock_id varchar(4), stock_type varchar(20), out_warehouse_id varchar(4), in_warehouse_id varchar(4), warehouse_id varchar(4), product_id int, amount decimal(8,2))
INSERT INTO stock_items(id, stock_id, stock_type, out_warehouse_id, in_warehouse_id, warehouse_id, product_id, amount)
VALUES
	(1, 16, 'in', NULL, NULL, 1, 1, 200),
	(2, 16, 'in', NULL, NULL, 1, 1, 25.52),
	(3, 16, 'in', NULL, NULL, 1, 6, 22),
	(4, 7, 'in', NULL, NULL, 2, 6, 12),
	(5, 6, 'in', NULL, NULL, 2, 5, 112),
	(7, 1, 'out', NULL, NULL, 2, 5, 11),
	(8, 1, 'out_return', NULL, NULL, 2, 6, 25),
	(10, 1, 'adjust', NULL, NULL, 1, 1, -50),
	(11, 1, 'adjust', NULL, NULL, 1, 1, -20),
	(12, 17, 'in', NULL, NULL, 1, 7, 100),
	(13, 17, 'in', NULL, NULL, 1, 19, 20),
	(14, 18, 'in', NULL, NULL, 1, 19, 398),
	(20, 18, 'in', NULL, NULL, 1, 13, 20),
	(27, 7, 'in_return', NULL, NULL, 1, 19, 1),
	(28, 7, 'in_return', NULL, NULL, 1, 13, 1),
	(29, 2, 'out', NULL, NULL, 1, 19, 20),
	(30, 2, 'out', NULL, NULL, 1, 7, 20),
	(31, 2, 'out_return', NULL, NULL, 1, 19, 5),
	(32, 2, 'out_return', NULL, NULL, 1, 7, 5),
	(35, 21, 'in', NULL, NULL, 1, 5, 12),
	(36, 2, 'adjust', NULL, NULL, 1, 19, -2),
	(37, 2, 'adjust', NULL, NULL, 1, 13, -1),
	(41, 5, 'adjust', NULL, NULL, 1, 19, -1),
	(42, 4, 'transfer', 1, 2, NULL, 19, 30),
	(43, 5, 'transfer', 1, 2, NULL, 19, 10);
25 rows affected

select warehouse_id , product_id, sum(amount) as amount from  (select warehouse_id , product_id, 
sum(case when stock_type='in' then amount  
when stock_type='out' then amount*(-1)
when stock_type='in_return' then amount*(-1)
else amount end)  amount from stock_items 
where warehouse_id is not null group by warehouse_id,product_id 
UNION ALL 
select out_warehouse_id as warehouse_id,product_id,amount*(-1) amount from stock_items where stock_type='transfer'
UNION ALL 
select in_warehouse_id as warehouse_id,product_id, amount from stock_items where stock_type='transfer') t1 
group by warehouse_id , product_id order by warehouse_id , product_id


warehouse_id
product_id
amount


1
1
155.52

1
5
12.00

1
6
22.00

1
7
85.00

1
13
18.00

1
19
359.00

2
5
101.00

2
6
37.00

2
19
40.00
warehouse_id	product_id	amount
1	1	155.52
1	5	12.00
1	6	22.00
1	7	85.00
1	13	18.00
1	19	359.00
2	5	101.00
2	6	37.00
2	19	40.00