Answered by:
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 Friday, December 27, 2019 1:38 PM
Answers
-
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.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, December 30, 2019 5:25 AM
All replies
-
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.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, December 30, 2019 5:25 AM -
User-367318540 posted
thanks YongQing,
for make me clear....
Monday, December 30, 2019 7:13 AM