Query is not giving correctly result ?

CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50))
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Entrydate DATETIME)
CREATE TABLE #tbl_BalPacM(PID INT, Entrydate DATETIME)
CREATE TABLE #tbl_PckDetail(DID INT,PID int,BID int,Codeitem int,prdQTY int)
CREATE TABLE #tbl_SBDispatchM(DID INT, Entrydate DATETIME)
Create Table #tbl_SBDispatachD(ID int ,DID int,PID int )
INSERT INTO #ItemMasterFile VALUES(1,'A')
INSERT INTO #ItemMasterFile VALUES(2,'B')
INSERT INTO #ItemMasterFile VALUES(3,'C')
INSERT INTO #ItemMasterFile VALUES(4,'D')
INSERT INTO #ItemMasterFile VALUES(5,'e')
INSERT INTO #ItemMasterFile VALUES(6,'f')
INSERT INTO #ItemMasterFile VALUES(7,'g')
INSERT INTO #ItemMasterFile VALUES(8,'h')
INSERT INTO #ItemMasterFile VALUES(9,'K')
INSERT INTO #ItemMasterFile VALUES(10,'L')
INSERT INTO #ItemMasterFile VALUES(11,'M')
INSERT INTO #Probale VALUES(10011,1,1,'01-06-2019')
INSERT INTO #Probale VALUES(10012,3,1,'02-06-2019')
INSERT INTO #Probale VALUES(10013,11,1,'03-06-2019')
INSERT INTO #Probale VALUES(10014,10,1,'08-06-2019')
INSERT INTO #Probale VALUES(10015,8,1,'03-06-2019')
INSERT INTO #Probale VALUES(10016,9,1,'08-06-2019')
INSERT INTO #Probale VALUES(10017,9,1,'08-06-2019')
INSERT INTO #tbl_BalPacM VALUES(1001,'01-06-2019')
INSERT INTO #tbl_BalPacM VALUES(1002,'01-06-2019')
INSERT INTO #tbl_PckDetail VALUES(1,1001,10011,1,1)
INSERT INTO #tbl_PckDetail VALUES(2,1001,10012,3,1)
INSERT INTO #tbl_PckDetail VALUES(3,1001,10013,11,1)
INSERT INTO #tbl_PckDetail VALUES(4,1001,10014,10,1)
INSERT INTO #tbl_PckDetail VALUES(5,1002,10015,8,1)
INSERT INTO #tbl_PckDetail VALUES(6,1002,10016,9,1)
INSERT INTO #tbl_PckDetail VALUES(7,1002,10017,9,1)
INSERT INTO #tbl_SBDispatchM VALUES(101,'01-06-2019')
INSERT INTO #tbl_SBDispatchM VALUES(102,'01-06-2019')
INSERT INTO #tbl_SBDispatachD VALUES(1,101,1001)
INSERT INTO #tbl_SBDispatachD VALUES(1,102,1002)
select pro.Item,pro.[Total probale],dis.Dispatch,(pro.[Total probale] - dis.Dispatch) as Pending from
(select a.Descriptionitem as Item,ISNULL(sum(p.prdQTY), 0 ) as 'Total probale'
from #ItemMasterFile a left join #Probale p on a.CodeItem = p.CodeItem group by a.Descriptionitem
) pro
left join
(select a.Descriptionitem as Item,ISNULL(sum(pds.prdQTY), 0 ) as 'Dispatch' from #ItemMasterFile a
left join
(select * FROM tbl_PckDetail PD where pd.PID in
(select pm.PID from tbl_BalPacM pm left join #tbl_SBDispatachD dd on pm.PID = dd.PID
))
pds on a.Codeitem = pds.Codeitem group by a.Descriptionitem) dis
on pro.Item = dis.Item

Hi,

It seems you need to give your table name start with # because its temp table. I am getting proper result using below query,

select pro.Item,pro.[Total probale],dis.Dispatch,(pro.[Total probale] - dis.Dispatch) as Pending from
(select a.Descriptionitem as Item,ISNULL(sum(p.prdQTY), 0 ) as 'Total probale'
from #ItemMasterFile a left join #Probale p on a.CodeItem = p.CodeItem group by a.Descriptionitem
) pro
left join
(select a.Descriptionitem as Item,ISNULL(sum(pds.prdQTY), 0 ) as 'Dispatch' from #ItemMasterFile a
left join
(select * FROM #tbl_PckDetail PD where pd.PID in
(select pm.PID from #tbl_BalPacM pm left join #tbl_SBDispatachD dd on pm.PID = dd.PID
))
pds on a.Codeitem = pds.Codeitem group by a.Descriptionitem) dis
on pro.Item = dis.Item

2 Replies

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

Akhter HUssain6y ago
Hi jignesh Kumar,

your little more consideration require ,which is ,if #tbl_BalPacM(PID) exit in table #tbl_SBDispatachD then quantity get minus from Probale table qty other wise not.

Above query is doing ,qty minus from #tbl_PckDetail to Probale table which is wrong,

also i need date filter from #tbl_SBDispatchM

thanks
Jignesh Kumar6y ago
Hi,

It seems you need to give your table name start with # because its temp table. I am getting proper result using below query,
select pro.Item,pro.[Total probale],dis.Dispatch,(pro.[Total probale] - dis.Dispatch) as Pending from
(select  a.Descriptionitem as Item,ISNULL(sum(p.prdQTY), 0 ) as 'Total probale'
from #ItemMasterFile a left join #Probale  p on  a.CodeItem = p.CodeItem group by a.Descriptionitem
) pro
left join
(select a.Descriptionitem as Item,ISNULL(sum(pds.prdQTY), 0 ) as 'Dispatch' from #ItemMasterFile a
left join
(select * FROM #tbl_PckDetail  PD where pd.PID in
(select pm.PID from #tbl_BalPacM  pm left join  #tbl_SBDispatachD dd on  pm.PID = dd.PID
))
pds  on a.Codeitem = pds.Codeitem  group by a.Descriptionitem) dis
on pro.Item = dis.Item