Loading

Need a query (Balance of item display according to iM_Name

Need a query (Balance of item display according to iM_Name

Akhter HUssain

Akhter HUssain

6y945SQL

I have data below

CREATE TABLE #tbl_item (IT_ID INT,IT_Name varchar(50))
CREATE TABLE #Importer (IM_ID INT,IM_Name varchar(50))
CREATE TABLE #tbl_GRNM (GRN_ID INT,IM_ID int)
CREATE TABLE #tbl_GRND (GRN_DID INT,GRN_ID int,IT_ID int,GRN_QTY int)
CREATE TABLE #tbl_Dispatch_Master (D_ID INT,IM_ID int)
Create Table #tbl_Dispatch_Detail (DID_ID int,D_ID int,IT_ID int ,D_QTY int)
INSERT INTO #tbl_item VALUES(1001, 'A')
INSERT INTO #tbl_item VALUES(1002, 'B')
INSERT INTO #tbl_item VALUES(1003, 'C')
INSERT INTO #Importer VALUES(101, 'Akhter')
INSERT INTO #Importer VALUES(102, 'Babar')
INSERT INTO #tbl_GRNM VALUES(201, 101)
INSERT INTO #tbl_GRND VALUES(1, 201,1001,5)
INSERT INTO #tbl_GRND VALUES(2, 201,1002,4)
INSERT INTO #tbl_GRNM VALUES(202, 102)
INSERT INTO #tbl_GRND VALUES(3, 202,1002,6)
INSERT INTO #tbl_GRND VALUES(4, 202,1003,4)
INSERT INTO #tbl_GRND VALUES(5, 202,1001,2)
INSERT INTO #tbl_GRNM VALUES(203, 101)
INSERT INTO #tbl_GRND VALUES(6, 203,1001,3)
INSERT INTO #tbl_GRND VALUES(7, 203,1002,2)
INSERT INTO #tbl_Dispatch_Master VALUES(301, 101)
INSERT INTO #tbl_Dispatch_Detail VALUES(1,301, 1001,2)
INSERT INTO #tbl_Dispatch_Detail VALUES(2,301, 1002,2)
INSERT INTO #tbl_Dispatch_Master VALUES(302, 101)
INSERT INTO #tbl_Dispatch_Detail VALUES(3,302, 1001,1)
INSERT INTO #tbl_Dispatch_Detail VALUES(4,302, 1002,1)

Output

Output Detail

IM_Name is taken from #Importer

IT_Name is taken from #tbl_Item

G_QTY is taken from #tbl_GRND,which qty will be get sum according to Im_Name

D_QTY is taken from #tbl_Dispatch_Detail,which qty will be get sum according to Im_Name

Balance Sum of G_QTY Minus from sum of D_QTY

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Nisha Regil6y ago
Accepted answer
You may be able to do this either with CTE or with SQL cursor.
For that you will first have to join the tables as below to obtain GQTY and DQTY.

select Importer.IM_ID, IM_Name,IT_name,tbl_item.IT_ID, sum(GRN_qty) as g_qty from Importer join tbl_GRNM
on Importer.IM_ID = tbl_GRNM.IM_ID join tbl_GRND on
tbl_GRNM.GRN_ID = tbl_GRND.GRN_ID join tbl_item
on tbl_item.IT_ID = tbl_GRND.IT_ID and tbl_GRNM.GRN_ID = tbl_GRND.GRN_ID
group by Importer.IM_ID, IM_Name,tbl_item.IT_name,IT_ID

select Importer.IM_ID, IM_Name,IT_name,tbl_item.IT_ID, sum(D_QTY) as d_qty from Importer
left join tbl_Dispatch_Master
on tbl_Dispatch_Master.IM_ID = Importer.IM_ID
left join tbl_Dispatch_Detail on tbl_Dispatch_Master.D_ID = tbl_Dispatch_Detail.D_ID
join tbl_item on tbl_item.IT_ID = tbl_Dispatch_Detail.IT_ID
group by Importer.IM_ID, IM_Name,tbl_item.IT_name,IT_ID
+1