Calculate stock quantity from multiple table transactions in SQL Server

Last Reply 5 months ago By pandeyism

Posted 5 months ago

I have required below condition. I have one table with Ordered quantities, another table is received quantities.

Table_Order: 

Order_Key Order_Qty
1 180.00
2 150.25
3 125.75

Table_Recived:

Rcvd_Key FK_Order_Key Recvd_Qty
1 1 50.00
2 3 27.25
3 1 87.00

by using above two tables, i want pending order quantity.

DECLARE @Table_Order AS TABLE(Order_Key INT, Order_Qty DECIMAL(18,2))
DECLARE @Table_Recived AS TABLE(Rcvd_Key INT,FK_Order_Key INT, Received_Qty DECIMAL(18,2))
INSERT INTO @Table_Order VALUES(1,180.00)
INSERT INTO @Table_Order VALUES(2,150.25)
INSERT INTO @Table_Order VALUES(3,125.75)

INSERT INTO @Table_Recived VALUES(1,1,50.00)
INSERT INTO @Table_Recived VALUES(2,3,27.25)
INSERT INTO @Table_Recived VALUES(3,1,87.00)

I want below output:

Order_Key Order_Qty Recvd_Qty Pending_Qty
1 180.00 137.00 43.00
2 150.25 0 150.25
3 125.75 27.25 98.50

 

Posted 5 months ago Modified on 5 months ago

Hi nagaraju60,

Refer below sample query.

SQL

DECLARE @Table_Order AS TABLE(Order_Key INT,Order_Qty DECIMAL(18,2))
INSERT INTO @Table_Order VALUES(1,180.00)
INSERT INTO @Table_Order VALUES(2,150.25)
INSERT INTO @Table_Order VALUES(3,125.75)
 
DECLARE @Table_Recived AS TABLE(Rcvd_Key INT,FK_Order_Key INT,Received_Qty DECIMAL(18,2))
INSERT INTO @Table_Recived VALUES(1,1,50.00)
INSERT INTO @Table_Recived VALUES(2,3,27.25)
INSERT INTO @Table_Recived VALUES(3,1,87.00)

SELECT Order_Key,Order_Qty,ISNULL(SUM(Received_Qty),0) 'Recvd_Qty',Order_Qty - ISNULL(SUM(Received_Qty),0) 'Pending_Qty' 
FROM (
    SELECT Order_Key,Order_Qty,Received_Qty 
    FROM @Table_Order tblo
    LEFT OUTER JOIN @Table_Recived tr
    ON tblo.Order_Key = tr.FK_Order_Key) t
GROUP BY Order_Key,Order_Qty
ORDER BY Order_Key

Output

Order_Key Order_Qty Recvd_Qty Pending_Qty
1 180.00 137.00 43.00
2 150.25 0.00 150.25
3 125.75 27.25 98.5