locked
Query is not giving output correctly? RRS feed

  • Question

  • User-367318540 posted
     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

    Result expected

    https://ibb.co/JqzzXmc

    Wednesday, February 19, 2020 8:54 AM

Answers

  • User1535942433 posted

    Hi akhterr,

    Accroding to your query,I create a test.I suggest you could add # before the tbl_PckDetail in your jquery.Tbl_PckDetail and tbl_BalPacM tables cannot be found due to missing #

    Just like this:

    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

    Result:

    Best ragards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 20, 2020 2:03 AM

All replies

  • User753101303 posted

    Hi,

    Please always be explicit. I had to try and the "desirred output" structure and data seems basically to match?

    For now my guess is that you are looking for the https://docs.microsoft.com/en-us/sql/t-sql/functions/upper-transact-sql?view=sql-server-ver15 function to show data in upper case ????

    Edit: if confrmed it would be easier to just ask directly how to convert  data to upper case. If not, you perhaps posted the actual output rather than what you really want ???

    Wednesday, February 19, 2020 9:45 AM
  • User-367318540 posted

    I don't need in upper case

    Wednesday, February 19, 2020 9:48 AM
  • User1535942433 posted

    Hi akhterr,

    Accroding to your query,I create a test.I suggest you could add # before the tbl_PckDetail in your jquery.Tbl_PckDetail and tbl_BalPacM tables cannot be found due to missing #

    Just like this:

    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

    Result:

    Best ragards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 20, 2020 2:03 AM
  • User-367318540 posted

    Yijing Sun,

    Now issue is that ,i want if #tbl_BalPacM table column (PID) is exit in #tbl_SBDispatchD ,then #tbl_PckDetail qty get minus from #Probale table otherwise it does not get minus,

    I do not want to directly minus #Probale qty to #tbl_Pckdetail qty ,but if PID exit in #tbl_SBDispatchD then qty get minus from Probale table...

    Thursday, February 20, 2020 5:11 AM
  • User1535942433 posted

    Hi akhterr,

    Accroding to your description,I suggest you could mark the answer that helps you.One thread post one question,you could open a new thread to ask this question.

    It will have more professional community members to help you and solve your problem.

    Best regards,

    Yijing Sun

    Monday, February 24, 2020 9:52 AM