locked
Stock Report Required RRS feed

  • 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