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

Analyst_SQL 3,531 Reputation points
2020-12-13T06:05:47.387+00:00

@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
47624-openingr.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,691 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,466 Reputation points
    2020-12-13T06:52:15.427+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful