Get the ending inventory of each product using sql queries

1

Hi Dear SQL Experts,

May I humbly ask your help on how to compute the ending inventory of each product to complete my inventory program please.

I want to calculate the remaining qty of each product using SQL sirs,

Is there any ways to attached my DB design here

My formula is : ENDING INVENTORY = (BEGINNING INVENTORY + PURCHASES) - SALES + SALES RETURN - STOCKS RETURN

Thank you very much for your help

Here are my tables
ITEMS
Itemcode
Description

BEGINNING_INVENTORY
itemcode
Qty
Transdate

PURCHASES HEADER
Ponumber
Suppliercode
Refno
Transdate
Statuscode

PURCHASE DETAIL
ponumber
Itemcode
Qty

SALED ORDER HEADER
Sonumber
Customercode
Refno
Transdate
Statuscode

SALES ORDER DETAIL
Sonumber
Itemcode
Qty

SALES RETURN HEADER
salesreturnnumber
Customercode
Refno
Transdate
Statuscode

SALES RETURN DETAIL
Salesreturnnumber
Itemcode
Qty

STOCK RETURN HEADER
IDnumber
Suppliercode
Refno
Transdate
Statuscode

STOCK RETURN DETAIL
IDnumber
Itemcode
Qty

EXPECTED OUTPUT
ITEM CODE DESCRIPTION BEGINNING PURCHASES SALES SALESRETURN STOCKRETURN ENDING

regards
leo

2

You don't need to post the entire database, you just need to post the info about the tables in which you have the data stored. This page gives you some info on how to do that.

Your query may be something like this (not exactly this, just the idea )

SELECT
	Inventory_ID,
	BeginningInventory
	+Purchases
	-Sales
	+SalesReturns
	-StockReturns AS EndingInventory
FROM
	YourTable
GROUP BY
	Inventory_ID;

More likely than not, not all of the values you want are in one table, in which case you will need to JOIN the relevant tables. Also, you may have other constraints or specifics in your data and tables that require additional changes.

3

Thank you so much sir. How i can achieve that please.

4

We cannot do homework / other class assignments for you. But we can give you general guidelines.