Running total with Date Range

Question

I want running total between two dates ,below is data

From Date 01/10/2021 to 06/10/2021

Create table #tbl_Student (S_ID int,S_Name varchar(50))  
create table #tbl_Fees (F_ID int,F_Date date,S_ID int,F_amt int)  
create table #tbl_Receive(R_ID int,R_Date date,S_ID int,R_Amt int)  
  
  
insert into  #tbl_Student values (1001,'AKhter');  
insert into #tbl_Fees values(1,'2021-10-01',1001,5000)  
insert into #tbl_Fees values(2,'2021-10-01',1001,5000)  
insert into #tbl_Fees values(3,'2021-10-03',1001,5000)  
insert into #tbl_Fees values(4,'2021-10-06',1001,5000)  
  
  
Insert into #tbl_Receive values(1, '2021-10-02',1001,8000)  
Insert into #tbl_Receive values(2, '2021-10-03',1001,4000)  
Insert into #tbl_Receive values(3, '2021-10-05',1001,2000)  

Answer 1

;With cte As
(Select f.F_ID As FRID, f.F_Date As Date, s.S_Name, f.F_Amt, 0 As R_Amt, 'F' As Type
From #tbl_Student s
Inner Join #tbl_Fees f On s.S_ID = f.S_ID
Union All
Select r.R_ID As FRID, r.R_Date As Date, s.S_Name, 0 As F_Amt, r.R_Amt, 'R' As Type
From #tbl_Student s
Inner Join #tbl_Receive r On s.S_ID = r.S_ID)
Select FRID As [F_ID/R_ID], S_Name, Date As S_Date, F_amt As S_Fee,
   R_Amt,
   Sum(F_Amt - R_Amt) Over(Partition By S_Name Order By Date, Type, FRID) As Running_Total
From cte
Order By S_Name, S_Date, Type, [F_ID/R_ID];

Tom