This looks a little bit like homework.
First, let's set up your sample data so that people can use it: -
DECLARE @Store_Out_Details AS TABLE (PurchaseOrderNo CHAR(3), SerialNo CHAR(4), Qty INT);
INSERT INTO @Store_Out_Details
VALUES ('001','I000',20),('001','I001',10),('002','I000',50),('003','I002',20);
DECLARE @Store_PO_Details AS TABLE (IssueNo CHAR(4), SerialNo CHAR(4), Qty INT);
INSERT INTO @Store_PO_Details
VALUES('S001','I000',10),('S002','I001',5);
OK, now what you want to do is group each table together before you do the arithmetic. Let's break it down a little: -
SELECT SerialNo, SUM(Qty)
FROM @Store_Out_Details
GROUP BY SerialNo;
So we now have: -
SerialNo
-------- -----------
I000 70
I001 10
I002 20
Which are the totals from the OutDetails table.
Next, let's group up the PODetails table: -
SELECT SerialNo, SUM(Qty)
FROM @Store_PO_Details
GROUP BY SerialNo;
Now we have: -
SerialNo
-------- -----------
I000 70
I001 10
I002 20
(3 row(s) affected)
SerialNo
-------- -----------
I000 10
I001 5
(2 row(s) affected)
So all we want to do is join the two result-sets together. We'll do a LEFT OUTER join to preserve the rows that exist in the Out table but not in the PO table.
SELECT OutDetails.SerialNo,
OutDetails.Qty - PODetails.Qty AS BalanceQty
FROM (SELECT SerialNo, SUM(Qty)
FROM @Store_Out_Details
GROUP BY SerialNo
) OutDetails(SerialNo, Qty)
LEFT OUTER JOIN (SELECT SerialNo, SUM(Qty)
FROM @Store_PO_Details
GROUP BY SerialNo
) PODetails(SerialNo, Qty) ON OutDetails.SerialNo = PODetails.SerialNo;
And now we have: -
SerialNo BalanceQty
-------- -----------
I000 60
I001 5
I002 NULL
Wait, what's that NULL doing there? Can you see the deliberate mistake? The clue is in the outer join. I'm going to assume that you spotted it 😉
SELECT OutDetails.SerialNo,
OutDetails.Qty - ISNULL(PODetails.Qty,0) AS BalanceQty
FROM (SELECT SerialNo, SUM(Qty)
FROM @Store_Out_Details
GROUP BY SerialNo
) OutDetails(SerialNo, Qty)
LEFT OUTER JOIN (SELECT SerialNo, SUM(Qty)
FROM @Store_PO_Details
GROUP BY SerialNo
) PODetails(SerialNo, Qty) ON OutDetails.SerialNo = PODetails.SerialNo;
SerialNo BalanceQty
-------- -----------
I000 60
I001 5
I002 20