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 |