Stock Report Required

Question

User-367318540 posted

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)  

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)

Desire Output Format

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

Answer 1

User665608656 posted

Hi akhterr,

Based on the tables and data you provided, you can use the following SQL to implement Total Probale column.

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

For Dispatch column, you need to determine first PackM table PID is exit in DispatachD:

select pm.PID from #PackM pm left join #DispatachD dd on pm.PID = dd.PID

Then put the eligible PID into the #PackD where condition and combine it with the #ItemMasterFile table to obtain the Dispatch：

select a.Descriptionitem as Item,ISNULL(sum(pds.prdQTY), 0 ) as 'Dispatch' from #ItemMasterFile a 
left join 
(select * FROM #PackD  PD where pd.PID in 
(select pm.PID from #PackM  pm left join  #DispatachD dd on  pm.PID = dd.PID
))
pds  on a.Codeitem = pds.Codeitem  group by a.Descriptionitem

Finally, combine these two sql and add the Pending column with their two columns subtracted after the select, here is the final SQL statement to get the results, you can refer to:

 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 #PackD  PD where pd.PID in 
(select pm.PID from #PackM  pm left join  #DispatachD dd on  pm.PID = dd.PID
))
pds  on a.Codeitem = pds.Codeitem  group by a.Descriptionitem) dis
on pro.Item = dis.Item

Here is the result :

Best Regards,

YongQing.

Answer 2

User-367318540 posted

thanks YongQing, 

for make me clear....