Debit Credit Balance calculating from previous records with Pagination MYSQL I have table with the following schema and demo data-
id  date        particulars          debit  credit  
104 29-12-2020  Crediting            NULL   1000    
105 29-12-2020  Purchased Item:A     2000   NULL    
106 29-12-2020  Purchased Item:B     200    NULL    
107 29-12-2020  Purchased Item:C     1000   NULL    
108 29-12-2020  Credited             NULL   1200    
109 29-12-2020  Credited             NULL   500     
110 29-12-2020  Credited             NULL   2100    
111 29-12-2020  Credited             NULL   1300    
112 01-01-2021  Purchased Item:Q     200    NULL    
113 02-01-2021  Purchased Item:A     2300   NULL

I want to calcualte running balance along with filters like from this date to this and pagination included.
So far by referring this Answer
I found the satisfied results, only the problem lies with WHERE clause.
Query i have used =
SET @variable = 0;
SELECT id, date, particulars, debit, credit, @variable := @variable + (COALESCE(`credit`,0) - COALESCE(`debit`,0)) `balance` 
FROM billing_ledger  ORDER BY id asc 

when i use this query im not getting expected balance as it should be
SET @variable = 0;
SELECT id, date, particulars, debit, credit, @variable := @variable + (COALESCE(`credit`,0) - COALESCE(`debit`,0)) `balance` 
FROM billing_ledger where date &gt;= '2021-01-01' ORDER BY id asc 

i want this second query to also consider the above rows but to give me results for the following dates with where clause and also with LIMIT
Here is the fiddle: FiddleLink
In fiddle, we can see that the second query is not considering the rows above, and thus the balance is not correct as it is in the 1st query.
As you are using the user-defined variables to iterate and so calculating the balnace, you have to set the initial variable ti the right amount


SET @variable = 0;
SELECT
-SUM(debit)  + SUM(credit) INTO @variable
FROM billing_ledger where date &lt; '2021-01-01';
SELECT id, date, particulars, debit, credit
, @variable := @variable + (COALESCE(`credit`,0) - COALESCE(`debit`,0)) `balance` 
FROM billing_ledger where date &gt;= '2021-01-01' ORDER BY id asc ;


✓

✓

 id | date       | particulars           | debit | credit | balance
--: | :--------- | :-------------------- | ----: | -----: | ------:
112 | 2021-01-01 | Purchased Item: Eggs  |   200 |   null |    2700
113 | 2021-01-02 | Purchased Item: Onion |  2300 |   null |     400