Share via

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

Analyst_SQL 3,551 Reputation points
2020-11-24T16:19:28.35+00:00

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

42199-dateledger.png

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

42216-ledger.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,623 questions
0 comments
Accepted answer
  1. Tom Phillips 17,731 Reputation points
    2020-11-24T19:59:47.907+00:00

    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
    1 person found this answer helpful.

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Farhan Jamil 421 Reputation points
    2020-11-24T17:10:09.097+00:00

    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

  2. David Browne - msft 3,846 Reputation points
    2020-11-24T17:37:05.143+00:00

    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
  3. Guoxiong 8,206 Reputation points
    2020-11-24T17:46:19.927+00:00

    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;
  4. Guoxiong 8,206 Reputation points
    2020-11-24T19:05:27.837+00:00 
    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;

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.