How to add and deduct quantity in inventory for SQL Server-CodePudding

I am developing simple inventory system but I am having hard time getting the accurate stock on hand after calculating the 3 quantities from 3 different tables. My goal is to add the sum of receiving_stock sum of returning_stock - the outgoing_stock. But if outgoing_stock has no data all the records are null.

Here’s my data and query.

receiving_stock
Prod_ID, Qty
123      10
124      10

returning_stock
Prod_ID, Qty
124      10
125      10

outgoing_stock
No Data Yet


Actual Result:
  Prod_id, qty
  Null      10
  Null      20
  Null      10


Desired Result:
  Prod_id, qty
  123      10
  124      20
  125      10

  Query:
 Select prod_id, isnull(qty,0)-isnull(sold,0) on-hand
 Select prod_id, sum(qty) qty
 (
 select prod_id,qty
 From receiving_stock
 Union all
 select prod_id,qty
 From returning_stock
 ) Za
 Group by prod_id
 ) Zb
 Left join
 (
 From
 Select prod_id, sum(qty) sold
 From outgoing_stock
 Group by prod_id
 ) zc
 ) zd
 On
 Zb.prod_id=zd.prod_id

CodePudding user response：

You can include the third table in UNION ALL with a negative quantity :

SELECT prod_id,qty
From receiving_stock
UNION ALL
SELECT prod_id,qty
From returning_stock
UNION ALL
SELECT prod_id, -qty
From outgoing_stock