Akhter HUssain

Akhter HUssain

  • 689
  • 1.3k
  • 95.6k

Balance is not calculating correct ,if date filter is applied?

Nov 24 2020 11:38 AM
I am retrieving Data between two date, but Balance is not calculating correct,
 
Below data with query.
  1. Create table #tbl_Receivable (Rec_ID int,Customer_ID int,Rec_Date date,Rec_Amount varchar(50),Inv_type varchar(50));  
  2.   INSERT INTO #tbl_Receivable VALUES  
  3.           
  4.   (111,1,'2020-03-06',5000,'Payable'),  
  5.   (112,1,'2020-03-07',2000,'Received'),  
  6.   (113,1,'2020-03-08',1000,'Payable'),  
  7.   (114,1,'2020-03-08',2000,'Payable'),  
  8.    (115,1,'2020-03-09',4000,'Received')
  9.  ;  
  10.   with Q as  
  11.   (  
  12.       select Rec_ID, Customer_ID, '' as [ ], Rec_Date, Rec_Amount as Payable, 0 as Received  
  13.       from #tbl_Receivable  
  14.       where Inv_type = 'Payable'  
  15.       union all  
  16.       select Rec_ID, Customer_ID, '', Rec_Date, 0, Rec_Amount   
  17.       from #tbl_Receivable t1  
  18.       where Inv_type = 'Received'  
  19.       union all  
  20.       select 0, Customer_ID, 'Opening', DATEADD(d, -1, MIN(Rec_Date)), 0, 0  
  21.       from #tbl_Receivable  
  22.       group by Customer_ID  
  23.   )  
  24.   select Customer_ID, [ ], Rec_Date, Payable, Received, sum(Payable - Received) over (partition by Customer_ID order by Rec_Date, Rec_ID) as Balance   
  25.   from Q  
  26.  where Rec_Date between '2020-03-08' and '2020-03-09'  
  27.   order by Customer_ID, Rec_Date, Rec_ID  
  28.   Drop table #tbl_Receivable  
 
Without Date Filter
  1. Create table #tbl_Receivable (Rec_ID int,Customer_ID int,Rec_Date date,Rec_Amount varchar(50),Inv_type varchar(50));  
  2.   INSERT INTO #tbl_Receivable VALUES  
  3.    
  4.  (111,1,'2020-03-06',5000,'Payable'),  
  5.  (112,1,'2020-03-07',2000,'Received'),  
  6.  (113,1,'2020-03-08',1000,'Payable'),  
  7.  (114,1,'2020-03-08',2000,'Payable'),  
  8.  (115,1,'2020-03-09',4000,'Received')
  9.  ;  
  10.  with Q as  
  11.  (  
  12.  select Rec_ID, Customer_ID, '' as [ ], Rec_Date, Rec_Amount as Payable, 0 as Received  
  13.  from #tbl_Receivable  
  14.  where Inv_type = 'Payable'  
  15.  union all  
  16.  select Rec_ID, Customer_ID, '', Rec_Date, 0, Rec_Amount   
  17.  from #tbl_Receivable t1  
  18.  where Inv_type = 'Received'  
  19.  union all  
  20.  select 0, Customer_ID, 'Opening', DATEADD(d, -1, MIN(Rec_Date)), 0, 0  
  21.  from #tbl_Receivable  
  22.  group by Customer_ID  
  23.  )  
  24.  select Customer_ID, [ ], Rec_Date, Payable, Received, sum(Payable - Received) over (partition by Customer_ID order by Rec_Date, Rec_ID) as Balance   
  25.  from Q  
  26.  --where Rec_Date between '2020-03-08' and '2020-03-09'  
  27.   order by Customer_ID, Rec_Date, Rec_ID  
  28.  Drop table #tbl_Receivable 
Ouput is
 
 
as You can see in 2020-03-07 Balance is 3000, 

Answers (1)