Customer Opening is not displaying Correctly ,as mentioned in Customer table.

Question

@MelissaMa-MSFT
Below is my query with data,i add two column in ,one column in #tbl_InvM and Second column in #tbl_Received table.i added these column in query,then Opening amount is not coming correctly in output please have a look.

as you can see below,customer_ID = 1 ,has opening is 2000,but in output ,Opening is 14000 coming ,which is wrong.

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,Rec_rmk varchar(50));  
   Create table #tbl_Shop_InvM (Inv_ID int ,Customer_ID int,E_Date date,Rec_Amount varchar(50),Inv_type varchar(50),item_Weight int,Inv_Rmk varchar(50));  
    Create table #tbl_Customer ( Customer_ID int ,Customer_Name varchar(50),opening int);  
                      
    insert into #tbl_Customer values (1,'Akhter',2000), (2,'Hussian',0);  
          
      INSERT INTO #tbl_Shop_InvM VALUES  
    (211,1,'2020-03-06',5000,'Payable',0,'tt'),  
          
    (222,1,'2020-03-08',8000,'Payable',0,'tt'),  
    (223,2,'2020-03-08',2000,'Payable',0,'tt'),  
      (224,1,'2020-03-09',5500,'Payable',0,'tt'),  
    (225,2,'2020-03-10',15000,'Payable',500,'tt')  
                      
    INSERT INTO #tbl_Receivable VALUES  
           
    (112,211,1,'2020-03-07',2000,'Received',0,'text'),  
    (115,222,1,'2020-03-09',4000,'Received',0,'text'),  
     (116,222,1,'2020-03-09',2000,'Received',0,'text'),  
          
    (117,211,1,'2020-03-12',1000,'Received',0,'text'),  
    (118,225,2,'2020-03-11',15000,'Received',0,'text');  
          
   declare @begin_date date = '2020-03-06';  
     declare @end_date date = '2020-03-11';  
  
              
 with cte as (  
  select inv_ID, Customer_id ,'' Descript,Inv_Rmk  ,E_Date,Convert(VARCHAR(50),rec_amount) Payable,'' received  
  from #tbl_Shop_InvM  
  where #tbl_Shop_InvM.Customer_ID=1 and  (Inv_Type='Payable' ) --and Inv_Del is null  
  --and (E_date between @begin_date and @end_date)  
  union   
  select rec_id inv_ID,Customer_id ,'' Descript,rec_rmk  Inv_Rmk,rec_date  E_Date,'' Payable,Convert(VARCHAR(50),rec_amount) received  
  from #tbl_Receivable   
  
  where #tbl_Receivable.Customer_ID=1 and (Inv_Type='Received' )  --and Rec_Del is null  
  -- and (Rec_Date between  @begin_date and @end_date)  
  ),cte1 as (  
  select *,Payable+ received*-1 balance from cte   
  union all  
  select distinct inv_ID,a.Customer_id,'Openning' Descript,Inv_Rmk, dateadd(day,-1,@begin_date) E_Date,0 Payable,0 Received, b.opening Balance  
  from cte a  
  inner join #tbl_Customer b on a.Customer_ID=b.Customer_ID  
  ),  
  cte2 as (  
  select Customer_id,Descript,Inv_Rmk,E_Date,Payable,Received,  
  sum(balance) OVER (PARTITION BY Customer_id ORDER BY e_date,inv_ID ROWS UNBOUNDED PRECEDING) Balance   
  from cte1)  
  ,cte3 as   
  (select  Customer_id,'Openning' Descript,Inv_Rmk, E_Date,0 Payable,0 Received, Balance  
  ,ROW_NUMBER() OVER(PARTITION BY Customer_id ORDER BY (select null))  rn   
  from cte2  
  where E_Date < @begin_date)  
  select Customer_id,Descript,Inv_Rmk as Remarks,dateadd(day,-1,@begin_date) E_Date,Payable as Sale,Received,Balance from cte3 where rn=(select max(rn) from cte3)  
  union all  
  select * from cte2 where E_Date between @begin_date and @end_date  
      

Ouput

Answer 1

Your query was getting more than one opening row per customer and then getting the sum of all those opening rows. You only want one opening row per customer. In the code below, I changed the line following the Union All in cte1 to

  select distinct 0,a.Customer_id,'Openning' Descript,'', dateadd(day,-1,@begin_date) E_Date,0 Payable,0 Received, b.opening Balance  

I also added an order by to the final select in the query. That changes your code to

   declare @begin_date date = '2020-03-06';  
     declare @end_date date = '2020-03-11';  
     
                 
 with cte as (  
  select inv_ID, Customer_id ,'' Descript,Inv_Rmk  ,E_Date,Convert(VARCHAR(50),rec_amount) Payable,'' received  
  from #tbl_Shop_InvM  
  where #tbl_Shop_InvM.Customer_ID=1 and  (Inv_Type='Payable' ) --and Inv_Del is null  
  --and (E_date between @begin_date and @end_date)  
  union   
  select rec_id inv_ID,Customer_id ,'' Descript,rec_rmk  Inv_Rmk,rec_date  E_Date,'' Payable,Convert(VARCHAR(50),rec_amount) received  
  from #tbl_Receivable   
     
  where #tbl_Receivable.Customer_ID=1 and (Inv_Type='Received' )  --and Rec_Del is null  
  -- and (Rec_Date between  @begin_date and @end_date)  
  ),cte1 as (  
  select *,Payable+ received*-1 balance from cte   
  union all  
  select distinct 0,a.Customer_id,'Openning' Descript,'', dateadd(day,-1,@begin_date) E_Date,0 Payable,0 Received, b.opening Balance  
  from cte a  
  inner join #tbl_Customer b on a.Customer_ID=b.Customer_ID  
  ),  
  cte2 as (  
  select Customer_id,Descript,Inv_Rmk,E_Date,Payable,Received,  
  sum(balance) OVER (PARTITION BY Customer_id ORDER BY e_date,inv_ID ROWS UNBOUNDED PRECEDING) Balance   
  from cte1)  
  ,cte3 as   
  (select  Customer_id,'Openning' Descript,Inv_Rmk, E_Date,0 Payable,0 Received, Balance  
  ,ROW_NUMBER() OVER(PARTITION BY Customer_id ORDER BY (select null))  rn   
  from cte2  
  where E_Date < @begin_date)  
  select Customer_id,Descript,Inv_Rmk as Remarks,dateadd(day,-1,@begin_date) E_Date,Payable as Sale,Received,Balance from cte3 where rn=(select max(rn) from cte3)  
  union all  
  select * from cte2 where E_Date between @begin_date and @end_date Order By Customer_ID, E_Date  

Tom