Running Total is not calculating correctly on Date Filter?

Question

Running Total is not calculating correctly on Date Filter?

Analyst_SQL 3,576

When i am filtering data on date filter then running total is not calculating correctly.

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)  
   
   
 ;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  where Date between '2021-10-02' and '2021-10-05'  
 Order By S_Name, S_Date, Type, [F_ID/R_ID];

Currently Output ,which is wrong

Correct ouput

EchoLiu-MSFT 14,621 Reputation points

2021-10-11T02:54:12.923+00:00

How is Running Total calculated? If you follow the rule of Sum(F_Amt-R_Amt) Over(Partition By S_Name Order By Date, Type, FRID) As Running_Total, you get the correct result.
Analyst_SQL 3,576 Reputation points

2021-10-11T06:21:30.357+00:00

if I see all my data from date Date between '2021-01-01' and '2021-10-05'

then below output is coming,

as you can see on date 2021-10-02 Running Total is 2000
EchoLiu-MSFT 14,621 Reputation points

2021-10-11T06:27:32.077+00:00

SUM is executed after WHERE, so when you add a date in WHERE, it does not get the result you want. Please refer to the latest answer.

Accepted answer

1 additional answer

Your answer

EchoLiu-MSFT 14,621 Reputation points

2021-10-11T02:54:12.923+00:00

How is Running Total calculated? If you follow the rule of Sum(F_Amt-R_Amt) Over(Partition By S_Name Order By Date, Type, FRID) As Running_Total, you get the correct result.
Analyst_SQL 3,576 Reputation points

2021-10-11T06:21:30.357+00:00

if I see all my data from date Date between '2021-01-01' and '2021-10-05'

then below output is coming,

as you can see on date 2021-10-02 Running Total is 2000
EchoLiu-MSFT 14,621 Reputation points

2021-10-11T06:27:32.077+00:00

SUM is executed after WHERE, so when you add a date in WHERE, it does not get the result you want. Please refer to the latest answer.

Answer 1

Please check:

 ;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)
 ,cte2 as(Select FRID As [F_ID/R_ID], S_Name, Date As S_Date, F_amt As S_Fee,
    R_Amt,Type,
    Sum(F_Amt - R_Amt) Over(Partition By S_Name Order By Date, Type, FRID) As Running_Total
 From cte)

 SELECT * FROM cte2
 WHERE S_Date between '2021-10-02' and '2021-10-06'
 Order By S_Name, S_Date, Type, [F_ID/R_ID];

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Your problem is not the running total is not working, it is working perfectly.

The problem is you need to calculate the starting balance before 10/2/2021 to start the process.

The simplest answer to your problem is to calculate the running total for all records, and only DISPLAY the date range in question.

;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)
, final as (
  Select FRID As [F_ID/R_ID], S_Name, Date As S_Date, F_amt As S_Fee,
     R_Amt,Type, FRID,
     Sum(F_Amt - R_Amt) Over(Partition By S_Name Order By Date, Type, FRID) As Running_Total
  From cte  
)
SELECT *
FROM final 
where S_Date between '2021-10-02' and '2021-10-05'
Order By S_Name, S_Date, Type, [F_ID/R_ID], FRID

Share via

Running Total is not calculating correctly on Date Filter?

1 additional answer

Your answer