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
-
- 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,