locked
Query is not give correct ouput. RRS feed

  • Question

  • User-367318540 posted

    below is query ,which is not giving correct ouput ,means that inv_ID 224 Received amount should not display in second line,it must display in one row, as in image attached.

    Create table #tbl_Receivable (Rec_ID int,Inv_ID int ,Customer_ID int,Rec_Date date,Rec_Amount varchar(50),Inv_type varchar(50),item_Weight int);
      Create table #tbl_Customer ( Customer_ID int ,Customer_Name varchar(50));
        
      insert into #tbl_Customer values (1,'Akhter'),(2,'Hussian');
        
       INSERT INTO #tbl_Receivable VALUES
                
       (111,211,1,'2020-03-06',5000,'Payable',0),
       (112,211,1,'2020-03-07',2000,'Received',0),
       (113,222,1,'2020-03-08',8000,'Payable',0),
       (114,223,2,'2020-03-08',2000,'Payable',0),
        (115,222,1,'2020-03-09',4000,'Received',0),
           (116,224,1,'2020-03-10',15000,'Payable',500),
              (117,211,1,'2020-03-12',1000,'Received',0),
    		     (118,224,1,'2020-03-15',1000,'Received',0)
     ;
    
    
    ;with cte as (
     SELECT a.Inv_ID,item_Weight,b.Customer_ID,min(a.Rec_Date) Rec_Date
      ,SUM((CASE WHEN a.INV_TYPE= 'Payable' THEN CAST(a.Rec_Amount as int)  ELSE 0 END)) as Payable, 
      SUM((CASE WHEN a.INV_TYPE= 'Received' THEN CAST(a.Rec_Amount as int)  ELSE 0 END)) as Received, 
      SUM((CASE WHEN a.INV_TYPE= 'Payable' THEN CAST(a.Rec_Amount as int)  ELSE 0 END)) - 
      SUM((CASE WHEN a.INV_TYPE= 'Received' THEN CAST(a.Rec_Amount as int)  ELSE 0 END)) as Pending
      from  #tbl_Receivable a
      inner join #tbl_Customer b on a.Customer_ID = b.Customer_ID
      GROUP BY a.INV_ID,b.Customer_Name,a.item_Weight,b.Customer_ID)
        
      select cast(Inv_ID as varchar(100)) Inv_ID
     , cast(item_Weight as varchar(100)) item_Weight
     , cast(Customer_ID as varchar(100)) Customer_ID
     , cast(Rec_Date as varchar(100)) Rec_Date
     ,Payable,Received,Pending
     from cte 
      union all
      select '','','','Total',sum(Payable),sum(Received),sum(pending)
      from cte 

    https://ibb.co/HCPbh5N

    Friday, December 4, 2020 1:00 PM

Answers

  • User452040443 posted

    The item_weight column is preventing the grouping of these 2 rows.

    To group them you need to use an aggregate function with that column or remove it from the query.

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 4, 2020 5:43 PM

All replies

  • User452040443 posted

    The item_weight column is preventing the grouping of these 2 rows.

    To group them you need to use an aggregate function with that column or remove it from the query.

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 4, 2020 5:43 PM
  • User364663285 posted

    Hi,

    It can be data issue. Check relevant query criteria.

    Monday, December 7, 2020 7:15 AM