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

Question

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

akhter hussain 2,881

Hi @Viorel
I am retrieving Data between two date, but Balance is not calculating correct,
Below data with query.

Create table #tbl_Receivable (Rec_ID int,Customer_ID int,Rec_Date date,Rec_Amount varchar(50),Inv_type varchar(50));  
 INSERT INTO #tbl_Receivable VALUES  
      
 (111,1,'2020-03-06',5000,'Payable'),  
 (112,1,'2020-03-07',2000,'Received'),  
 (113,1,'2020-03-08',1000,'Payable'),  
 (114,1,'2020-03-08',2000,'Payable'),  
  (115,1,'2020-03-09',4000,'Received')  
  
  
  
;  
 with Q as  
 (  
     select Rec_ID, Customer_ID, '' as [ ], Rec_Date, Rec_Amount as Payable, 0 as Received  
     from #tbl_Receivable  
     where Inv_type = 'Payable'  
     union all  
     select Rec_ID, Customer_ID, '', Rec_Date, 0, Rec_Amount   
     from #tbl_Receivable t1  
     where Inv_type = 'Received'  
     union all  
     select 0, Customer_ID, 'Opening', DATEADD(d, -1, MIN(Rec_Date)), 0, 0  
     from #tbl_Receivable  
     group by Customer_ID  
 )  
 select Customer_ID, [ ], Rec_Date, Payable, Received, sum(Payable - Received) over (partition by Customer_ID order by Rec_Date, Rec_ID) as Balance   
 from Q  
where Rec_Date between '2020-03-08' and '2020-03-09'  
 order by Customer_ID, Rec_Date, Rec_ID  
 Drop table #tbl_Receivable

Output Without Date Filter ,in which 2020-03-07 Balance is 3000.

Create table #tbl_Receivable (Rec_ID int,Customer_ID int,Rec_Date date,Rec_Amount varchar(50),Inv_type varchar(50));  
 INSERT INTO #tbl_Receivable VALUES  
      
 (111,1,'2020-03-06',5000,'Payable'),  
 (112,1,'2020-03-07',2000,'Received'),  
 (113,1,'2020-03-08',1000,'Payable'),  
 (114,1,'2020-03-08',2000,'Payable'),  
  (115,1,'2020-03-09',4000,'Received')  
  
  
  
;  
 with Q as  
 (  
     select Rec_ID, Customer_ID, '' as [ ], Rec_Date, Rec_Amount as Payable, 0 as Received  
     from #tbl_Receivable  
     where Inv_type = 'Payable'  
     union all  
     select Rec_ID, Customer_ID, '', Rec_Date, 0, Rec_Amount   
     from #tbl_Receivable t1  
     where Inv_type = 'Received'  
     union all  
     select 0, Customer_ID, 'Opening', DATEADD(d, -1, MIN(Rec_Date)), 0, 0  
     from #tbl_Receivable  
     group by Customer_ID  
 )  
 select Customer_ID, [ ], Rec_Date, Payable, Received, sum(Payable - Received) over (partition by Customer_ID order by Rec_Date, Rec_ID) as Balance   
 from Q  
--where Rec_Date between '2020-03-08' and '2020-03-09'  
 order by Customer_ID, Rec_Date, Rec_ID  
 Drop table #tbl_Receivable

Answer 1

This fixes David's post to do your requirement.

DROP TABLE IF EXISTS #tbl_Receivable;

Create table #tbl_Receivable (Rec_ID int,Customer_ID int,Rec_Date date,Rec_Amount varchar(50),Inv_type varchar(50));
   INSERT INTO #tbl_Receivable VALUES

   (111,1,'2020-03-06',5000,'Payable'),
   (112,1,'2020-03-07',2000,'Received'),
   (113,1,'2020-03-08',1000,'Payable'),
   (114,1,'2020-03-08',2000,'Payable'),
   (115,1,'2020-03-09',4000,'Received');


 declare @begin_date date = '2020-03-07';
 declare @end_date date = '2020-03-09';

 with  q as
 (
  select Rec_ID, 
         Customer_ID, 
         Rec_Date,
         case when Inv_type = 'Received' then Rec_Amount else 0 end Received,
         case when Inv_type = 'Payable' then Rec_Amount else 0 end Payable
  from #tbl_Receivable
 ), opening_balances as
 (
     select 0 as linenum,
            Customer_ID, 
            'Opening' as Descript, 
            null Rec_ID,
            dateadd(day,-1,@begin_date) as Rec_Date, 
            0 as Payable, 
            0 as Received, 
            sum(Payable)-sum(Received) as Balance 
     from q
     where Rec_Date < @begin_date
     group by Customer_ID
 ), daily_balances as
 (
     select * from opening_balances
     union all
     select  ROW_NUMBER() OVER (ORDER BY Rec_date, Payable, Received ) AS linenum,
        Customer_ID, null as Descript, Rec_ID, Rec_date, Payable, Received, Payable-Received Balance 
     from q
     where q.Rec_Date between @begin_date and @end_date
 )
 select Customer_ID, Descript, Rec_date, Payable Payable, Received Received, 
       sum(Balance) over (partition by customer_id  order by rec_date,Rec_id) Balance
 from daily_balances
 ORDER BY linenum

akhter hussain 2,881 Reputation points

2020-11-24T20:29:22.723+00:00

Thanks @Tom Phillips

Answer 2

HI

I am not sure what specific date you are after but based on your other sql query without date filter ,I think this might work for you. Change your where clause to rec_date>'2020-03-05'

SQL

Create table #tbl_Receivable (Rec_ID int,Customer_ID int,Rec_Date date,Rec_Amount varchar(50),Inv_type varchar(50));
INSERT INTO #tbl_Receivable VALUES

(111,1,'2020-03-06',5000,'Payable'),
(112,1,'2020-03-07',2000,'Received'),
(113,1,'2020-03-08',1000,'Payable'),
(114,1,'2020-03-08',2000,'Payable'),
(115,1,'2020-03-09',4000,'Received')

;
with Q as
(
select Rec_ID, Customer_ID, '' as [ ], Rec_Date, Rec_Amount as Payable, 0 as Received
from #tbl_Receivable
where Inv_type = 'Payable'
union all
select Rec_ID, Customer_ID, '', Rec_Date, 0, Rec_Amount
from #tbl_Receivable t1
where Inv_type = 'Received'
union all
select 0, Customer_ID, 'Opening', DATEADD(d, -1, MIN(Rec_Date)), 0, 0
from #tbl_Receivable
group by Customer_ID
)
select Customer_ID, [ ], Rec_Date, Payable, Received, sum(Payable - Received) over (partition by Customer_ID order by Rec_Date, Rec_ID) as Balance
from Q
where Rec_Date >'2020-03-05'
order by Customer_ID, Rec_Date, Rec_ID
Drop table #tbl_Receivable

akhter hussain 2,881 Reputation points

2020-11-24T17:33:02.227+00:00

Hi @Farhan Jamil ,
,
I want ,take your data as example, when i am giving date filter ,from 2020-03-07 to 2020-03-09 ,then Opening Balance ,whatever date will be exit before 2020-03-07 ,it will be opening Balance and no Payable and no Receivable will come in opening ,just Balance will come.

Answer 3

Something like this, perhaps:

 Create table #tbl_Receivable (Rec_ID int,Customer_ID int,Rec_Date date,Rec_Amount varchar(50),Inv_type varchar(50));
  INSERT INTO #tbl_Receivable VALUES

  (111,1,'2020-03-06',5000,'Payable'),
  (112,1,'2020-03-07',2000,'Received'),
  (113,1,'2020-03-08',1000,'Payable'),
  (114,1,'2020-03-08',2000,'Payable'),
  (115,1,'2020-03-09',4000,'Received');


declare @begin_date date = '2020-03-08';
declare @end_date date = '2020-03-09';

with  q as
(
 select Rec_ID, 
        Customer_ID, 
        Rec_Date,
        case when Inv_type = 'Received' then Rec_Amount else 0 end Received,
        case when Inv_type = 'Payable' then Rec_Amount else 0 end Payable
 from #tbl_Receivable
), opening_balances as
(
    select Customer_ID, 
           'Opening' x, 
           null Rec_ID,
           dateadd(day,-1,@begin_date) Rec_Date, 
           sum(Payable) Payable, 
           sum(Received) Received, 
           sum(Payable)-sum(Received)  Balance 
    from q
    where Rec_Date < @begin_date
    group by Customer_ID
), daily_balances as
(
    select * from opening_balances
    union all
    select Customer_ID, null  x, Rec_ID, Rec_date, Payable, Received, Payable-Received Balance 
    from q
    where q.Rec_Date between @begin_date and @end_date
)
select Customer_ID, null  x, Rec_date, Payable Payable, Received Received, 
      sum(Balance) over (partition by customer_id  order by rec_date,Rec_id) Balance
from daily_balances

outputs

Customer_ID x           Rec_date   Payable     Received    Balance
----------- ----------- ---------- ----------- ----------- -----------
1           NULL        2020-03-07 5000        2000        3000
1           NULL        2020-03-08 1000        0           4000
1           NULL        2020-03-08 2000        0           6000
1           NULL        2020-03-09 0           4000        2000

akhter hussain 2,881 Reputation points

2020-11-24T18:12:31.287+00:00

HI anonymous usere-msft ,

5000 is Opening Balance in 2020-03-06 will come like below

Answer 4

Guoxiong 8,056

Obviously the balance is not correct after the date range is added. The balance calculation is based on the whole data so you cannot add the filter of the date inside the calculation. Try this:

SELECT Customer_ID, No_Column_Name, Rec_Date, Payable, Received, Balance
FROM (
 SELECT Customer_ID, [ ] AS No_Column_Name, Rec_Date, Payable, Received, SUM(Payable - Received) OVER (PARTITION BY Customer_ID ORDER BY Rec_Date, Rec_ID) AS Balance 
 FROM Q

) AS temp
WHERE Rec_Date BETWEEN '2020-03-08' AND '2020-03-09'
ORDER BY Customer_ID, Rec_Date;

akhter hussain 2,881

I have one Column of Payable and Received ,

 Create table #tbl_Receivable (Rec_ID int,Customer_ID int,Rec_Date date,Rec_Amount varchar(50),Inv_type varchar(50));
  INSERT INTO #tbl_Receivable VALUES

  (111,1,'2020-03-06',5000,'Payable'),
  (112,1,'2020-03-07',2000,'Received'),
  (113,1,'2020-03-08',1000,'Payable'),
  (114,1,'2020-03-08',2000,'Payable'),
   (115,1,'2020-03-09',4000,'Received')

Guoxiong 8,056 Reputation points

2020-11-24T18:13:30.343+00:00

You still need that CTE Q to separate them.
akhter hussain 2,881 Reputation points

2020-11-24T18:18:36.23+00:00

How it will separate ?
Guoxiong 8,056 Reputation points

2020-11-24T19:05:21.543+00:00

I just used the code CTE you posted. For some reason, I cannot post the code here. See next answer.

Answer 5

CREATE TABLE #tbl_Receivable (
    Rec_ID int,
    Customer_ID int,
    Rec_Date date,
    Rec_Amount varchar(50),
    Inv_type varchar(50)
);
INSERT INTO #tbl_Receivable VALUES          
(111,1,'2020-03-06',5000,'Payable'),
(112,1,'2020-03-07',2000,'Received'),
(113,1,'2020-03-08',1000,'Payable'),
(114,1,'2020-03-08',2000,'Payable'),
(115,1,'2020-03-09',4000,'Received');

;WITH Q AS
(
    SELECT Rec_ID, Customer_ID, '' AS [ ], Rec_Date, Rec_Amount AS Payable, 0 AS Received
    FROM #tbl_Receivable
    WHERE Inv_type = 'Payable'
    UNION ALL
    SELECT Rec_ID, Customer_ID, '', Rec_Date, 0, Rec_Amount
    FROM #tbl_Receivable t1
    WHERE Inv_type = 'Received'
    UNION ALL
    SELECT 0, Customer_ID, 'Opening', DATEADD(d, -1, MIN(Rec_Date)), 0, 0
    FROM #tbl_Receivable
    GROUP BY Customer_ID
)

SELECT Customer_ID, No_Column_Name, Rec_Date, Payable, Received, Balance
FROM (
    SELECT Customer_ID, [ ] AS No_Column_Name, Rec_Date, Payable, Received, SUM(Payable - Received) OVER (PARTITION BY Customer_ID ORDER BY Rec_Date, Rec_ID) AS Balance 
    FROM Q

) AS temp
WHERE Rec_Date BETWEEN '2020-03-08' AND '2020-03-09'
ORDER BY Customer_ID, Rec_Date;

DROP TABLE #tbl_Receivable;

akhter hussain 2,881 Reputation points

2020-11-24T19:18:02.863+00:00

HI @Guoxiong ,

Below output i want,Means that ,if i am giving From Date :2020-03-08 ,then what ever date exit before it ,that will be opening balance ,as you can see image ,in which 2020-03-07 balance is 3000 which will be opening balance .

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

4 additional answers

Activity