Stock Report Required

Akhter HUssain

6y1.2kSQL

i have below data ...

I am Making product in Probale table ,then i am creating Packs in (PackM,PackD"MasterDetail" ) table ,then i am dispatching Packs in (DispatachM ,DispatachD"MasterDetail") table

CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50))
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Entrydate DATETIME)
CREATE TABLE #PackM(PID INT, Entrydate DATETIME)
CREATE TABLE #PackD(DID INT,PID int,BID int,Codeitem int,prdQTY int)
CREATE TABLE #DispatchM(DID INT, Entrydate DATETIME)
Create Table #DispatachD(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 #PackM VALUES(1001,'01-06-2019')
INSERT INTO #PackM VALUES(1002,'01-06-2019')
INSERT INTO #PackD VALUES(1,1001,10011,1,1)
INSERT INTO #PackD VALUES(2,1001,10012,3,1)
INSERT INTO #PackD VALUES(3,1001,10013,11,1)
INSERT INTO #PackD VALUES(4,1001,10014,10,1)
INSERT INTO #PackD VALUES(5,1002,10015,8,1)
INSERT INTO #PackD VALUES(6,1002,10016,9,1)
INSERT INTO #PackD VALUES(7,1002,10017,9,1)
INSERT INTO #DispatchM VALUES(101,'01-06-2019')
INSERT INTO #DispatchM VALUES(102,'01-06-2019')
INSERT INTO #DispatachD VALUES(1,101,1001)
INSERT INTO #DispatachD VALUES(1,102,1002)

Require Ouput is

Total Probale(Sum of Probale QTY)

Dispatch(Sum of PackD QTY) ,if PackM table PID is exit in DispatachD

Pending (Total Probale Minus From Dispatch)

Item	Total probale	Dispatch	Pending
A	1	1	0
B	0	0	0
C	1	1	0
D	0	0	0
E	0	0	0
F	0	0	0
G	0	0	0
H	1	1	0
K	2	2	0
L	1	1	0
M	1	1	0

13 Replies

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

Priyanka Jain6y ago
Accepted answer
Yes as you are having same references in PAckM and Probable table updated query
select i.Descriptionitem as 'Item',ISNULL(sum(P.prdQTY),0) as 'Totalprobale',ISNULL(sum(D.prdQTY),0) as 'Despatch' ,ISNULL(sum(P.prdQTY)-sum(D.prdQTY),0) as 'pending'
from #ItemMasterFile i
LEFT JOIN #Probale P on i.CodeItem = P.CodeItem
LEFT JOIN #PackD D on P.Codeitem = D.Codeitem   AND P.BID  = D.BID
LEFT JOIN #DispatchM DM on D.DID = DM.DID
LEFT JOIN #DispatachD DD on DM.DID = DD.DID
group by i.Descriptionitem
+2
Priyanka Jain6y ago
Try below query may resolve your issue
CREATE TABLE #Salesorder(orderno INT,customer varchar(50),Entrydate DATETIME)
CREATE TABLE #OrderDetail(ID INT,orderno INT,CodeItem INT,qty INT)
CREATE TABLE #Probale(BID INT,CodeItem INT,orderno INT,prdQTY INT,Entrydate DATETIME)
CREATE TABLE #PackM(PID INT,orderno INT, Entrydate DATETIME)

CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50))

CREATE TABLE #PackD(DID INT,PID int,BID int,Codeitem int,prdQTY int)

CREATE TABLE #DispatchM(DID INT, Entrydate DATETIME)
Create Table #DispatachD(ID int ,DID int,PID int )

INSERT INTO #Salesorder VALUES(11,'Test','01-02-2019')
INSERT INTO #Salesorder VALUES(12,'Test2','03-02-2019')

INSERT INTO #OrderDetail VALUES(1,11,111,23)
INSERT INTO #OrderDetail VALUES(2,12,112,55)


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,11,1,'01-06-2019')
INSERT INTO #Probale VALUES(10012,3,11,1,'02-06-2019')
INSERT INTO #Probale VALUES(10013,11,11,1,'03-06-2019')
INSERT INTO #Probale VALUES(10014,10,12,1,'08-06-2019')
INSERT INTO #Probale VALUES(10015,8,12,1,'03-06-2019')
INSERT INTO #Probale VALUES(10016,9,12,1,'08-06-2019')
INSERT INTO #Probale VALUES(10017,9,12,1,'08-06-2019')

INSERT INTO #PackM VALUES(1001,11,'01-06-2019')
INSERT INTO #PackM VALUES(1002,12,'01-06-2019')

INSERT INTO #PackD VALUES(1,1001,10011,1,1)
INSERT INTO #PackD VALUES(2,1001,10012,3,1)
INSERT INTO #PackD VALUES(3,1001,10013,11,1)
INSERT INTO #PackD VALUES(4,1001,10014,10,1)
INSERT INTO #PackD VALUES(5,1002,10015,8,1)
INSERT INTO #PackD VALUES(6,1002,10016,9,1)
INSERT INTO #PackD VALUES(7,1002,10017,9,1)

INSERT INTO #DispatchM VALUES(1,'01-06-2019')
INSERT INTO #DispatchM VALUES(2,'01-06-2019')

INSERT INTO #DispatachD VALUES(1,1,1001)
INSERT INTO #DispatachD VALUES(1,2,1002)

select i.Descriptionitem as 'Item',ISNULL(sum(P.prdQTY),0) as 'Totalprobale',ISNULL(sum(D.prdQTY),0) as 'Despatch' ,ISNULL(sum(P.prdQTY)-sum(D.prdQTY),0) as 'pending'
from #ItemMasterFile i
LEFT JOIN #Probale P on i.CodeItem = P.CodeItem
LEFT JOIN #PackD D on P.Codeitem = D.Codeitem
LEFT JOIN #DispatchM DM on D.DID = DM.DID
LEFT JOIN #DispatachD DD on DM.DID = DD.DID
group by i.Descriptionitem
+2
Akhter HUssain6y ago
Your last provided query display result of item (K) 4 totalProbale ,but i have 2 (K)
please review image
0
Akhter HUssain6y ago
Item A qty is just one,in your query showing 4 quantity which is wrong
0
Priyanka Jain6y ago
Try below query may resolve your issue,

It is due to you data not properly inserted
CREATE TABLE #Salesorder(orderno INT,customer varchar(50),Entrydate DATETIME)
CREATE TABLE #OrderDetail(ID INT,orderno INT,CodeItem INT,qty INT)
CREATE TABLE #Probale(BID INT,CodeItem INT,orderno INT,prdQTY INT,Entrydate DATETIME)
CREATE TABLE #PackM(PID INT,orderno INT, Entrydate DATETIME)

CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50))

CREATE TABLE #PackD(DID INT,PID int,BID int,Codeitem int,prdQTY int)

CREATE TABLE #DispatchM(DID INT, Entrydate DATETIME)
Create Table #DispatachD(ID int ,DID int,PID int )

INSERT INTO #Salesorder VALUES(11,'Test','01-02-2019')
INSERT INTO #Salesorder VALUES(12,'Test2','03-02-2019')

INSERT INTO #OrderDetail VALUES(1,11,111,23)
INSERT INTO #OrderDetail VALUES(2,12,112,55)


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,11,1,'01-06-2019')
INSERT INTO #Probale VALUES(10012,3,11,1,'02-06-2019')
INSERT INTO #Probale VALUES(10013,11,11,1,'03-06-2019')
INSERT INTO #Probale VALUES(10014,10,12,1,'08-06-2019')
INSERT INTO #Probale VALUES(10015,8,12,1,'03-06-2019')
INSERT INTO #Probale VALUES(10016,9,12,1,'08-06-2019')
INSERT INTO #Probale VALUES(10017,9,12,1,'08-06-2019')

INSERT INTO #PackM VALUES(1001,11,'01-06-2019')
INSERT INTO #PackM VALUES(1002,12,'01-06-2019')

INSERT INTO #PackD VALUES(1,1001,10011,1,1)
INSERT INTO #PackD VALUES(2,1001,10012,3,1)
INSERT INTO #PackD VALUES(3,1001,10013,11,1)
INSERT INTO #PackD VALUES(4,1001,10014,10,1)
INSERT INTO #PackD VALUES(5,1002,10015,8,1)
INSERT INTO #PackD VALUES(6,1002,10016,9,1)
INSERT INTO #PackD VALUES(7,1002,10017,9,1)

INSERT INTO #DispatchM VALUES(1,'01-06-2019')
INSERT INTO #DispatchM VALUES(2,'01-06-2019')

INSERT INTO #DispatachD VALUES(1,1,1001)
INSERT INTO #DispatachD VALUES(1,2,1002)

select i.Descriptionitem as 'Item',ISNULL(sum(P.prdQTY),0) as 'Totalprobale',ISNULL(sum(D.prdQTY),0) as 'Despatch' ,ISNULL(sum(P.prdQTY)-sum(D.prdQTY),0) as 'pending'
from #ItemMasterFile i
LEFT JOIN #Probale P on i.CodeItem = P.CodeItem
LEFT JOIN #PackM M on P.orderno = M.Orderno
LEFT JOIN #PackD D on M.PID = D.PID
LEFT JOIN #DispatchM DM on D.DID = DM.DID
LEFT JOIN #DispatachD DD on DM.DID = DD.DID
LEFT JOIN #Salesorder so ON so.orderno = m.Orderno
group by i.Descriptionitem
Giving output as below,
0
Akhter HUssain6y ago
hi
Laxmidhar Sahoo

i didn't get you.

why left join . Can show the insert values to the tables

0
Laxmidhar Sahoo6y ago
hey
why left join . Can show the insert values to the tables
0
Akhter HUssain6y ago
hi laxmidhar sahoo,

query is executing but no result is coming ,please below is update query
select i.Descriptionitem as 'Item',sum(P.prdQTY) as 'Totalprobale',sum(D.prdQTY) as 'Despatch' ,sum(P.prdQTY)-sum(D.prdQTY) as 'pending' from #ItemMasterFile i
inner join #Probale P on i.CodeItem = P.CodeItem
inner join #PackM M on P.orderno = M.Orderno
inner join #PackD D on M.PID = D.PID
left join #DispatchM DM on D.DID = DM.DID
left join #DispatachD DD on DM.DID = DD.DID
group by i.Descriptionitem
0
Laxmidhar Sahoo6y ago
1. select i.Descriptionitem as 'Item',sum(P.prQTY) as 'Totalprobale',sum(D.prdQTY) as 'Despatch' ,sum(P.prQTY)-sum(D.prdQTY) as 'pending' from ItemMasterFile
2. inner join Prpbale P on i.CodeItem = P.CodeItem
3. inner join PackM M on P.orderno = M.oderno
4. inner join PackD D on M.PID = D.PID
5. inner join DispatchM DM on D.DID = DM.DID
6. inner join DispatachD DD on DM.DID = DD.DID
I don have system.IF any error convey me
0

Akhter HUssain6y ago

hi laxmidhar,

Below is two more table i have defined ,sales order with detail ..

CREATE TABLE #Salesorder(orderno INT,customer varchar(50),Entrydate DATETIME)
CREATE TABLE #OrderDetail(ID INT,orderno INT,CodeItem INT,qty INT)
CREATE TABLE #Probale(BID INT,CodeItem INT,orderno INT,prdQTY INT,Entrydate DATETIME)
CREATE TABLE #PackM(PID INT,orderno INT, Entrydate DATETIME)

Laxmidhar Sahoo6y ago

In these tables no orderno

CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Entrydate DATETIME)
CREATE TABLE #PackM(PID INT, Entrydate DATETIME)

Akhter HUssain6y ago

yes orderno

Laxmidhar Sahoo6y ago

hey there should be a common key between Probale and packm