Calculate dispatch and balance quantity from multiple tables in SQL Server

Hi akhter,

Check this query.

SQL

CREATE TABLE #item (ItemCode int,ItemName varchar(max))
CREATE TABLE #bigbale(ID int,ItemCode int,QTY int)
CREATE TABLE #Distpatch(ID int,Itemcode int,DisQTY int,A_Date datetime)
 
INSERT INTO #item VALUES(1001, 'A')
INSERT INTO #item VALUES(1002, 'B')
INSERT INTO #item VALUES(1003, 'C')
INSERT INTO #item VALUES(1004, 'D')
INSERT INTO #item VALUES(1005, 'E')
INSERT INTO #item VALUES(1006, 'F')
INSERT INTO #item VALUES(1007, 'G')
 
INSERT INTO #bigbale values(1,1001, 8)
INSERT INTO #bigbale values(2,1002, 5)
INSERT INTO #bigbale values(3,1003, 2)
INSERT INTO #bigbale values(4,1004, 2)
INSERT INTO #bigbale values(5,1005, 3)
INSERT INTO #bigbale values(6,1006, 25)
INSERT INTO #bigbale values(7,1007, 7)
INSERT INTO #bigbale values(8,1006, 25)
 
INSERT INTO #Distpatch values(1,1001,2, '2019-05-28 17:18')
INSERT INTO #Distpatch values(2,1002,3 ,'2018-05-28 17:18')
INSERT INTO #Distpatch values(3,1003,2 ,'2018-05-28 17:18')
INSERT INTO #Distpatch values(4,1006,2, '2018-05-28 17:18')
INSERT INTO #Distpatch values(5,1006,2, '2018-05-28 17:18')
INSERT INTO #Distpatch values(6,1006,2, '2018-05-28 17:18')
INSERT INTO #Distpatch values(7,1001,2, '2019-05-28 17:18')
 
SELECT 
 i.ItemName Name
 ,ISNULL(SUM(d.DisQTY),0) Dispatch_QTY
 ,(SELECT SUM(QTY) 
 FROM #bigbale b 
 WHERE b.ItemCode = i.ItemCode) - ISNULL(SUM(d.DisQTY),0) Balance
FROM #item i
LEFT OUTER JOIN #Distpatch d ON i.ItemCode = d.ItemCode
GROUP BY i.ItemName,d.DisQTY,i.ItemCode
ORDER BY i.ItemName
 
DROP TABLE #item
DROP TABLE #bigbale
DROP TABLE #Distpatch

Output

Name Dispatch_QTY Balance

    A             4                 4

    B             3                 2

    C             2                 0

    D             0                 2

    E             0                 3

    F             6                 44

    G             0                 7