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

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

Without Date Filter

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

Ouput is

as You can see in 2020-03-07 Balance is 3000,

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar5y ago
Hello Aktar, When youretrive data with two dates using where condition as you mentioned
where Rec_Date between '2020-03-08' and '2020-03-09'
your query will not retrive previous balance so occording to above two dates you should retrive what is balance amount on 2020-03-07 date then add to sum(Payable - Received) may be as sum(Payable - (Received+Balance)). Hope you understand my point.