The sum of Opening and Closing Balances for each Financial Year Grouped by Category in MYSQL

I have used three days attempting how to figure out this with no avail.I also did some search even from this forum but failed too.
It might look like Duplicate Question but to be honest this is different from others has been asked.
My question is how to get the sum of Balance Carry forward C/F and Closing balance for each financial year being GROUPED BY loan_id For each Date Range ie.Financial year?

---

transaction_t
Na    loan_id     date       credit_amount     debit_amount
1      1        2017-01-01     5,000            4,000
2      1        2017-05-01     6,000            2,000 
3      2        2017-10-01     1,000            1,500     
4      1        2018-10-30     2,000            400
5      2        2018-11-01     12,00            1,000   
6      2        2019-01-15      1,800            500   
7      1        2019-05-21     100               200      

The above table schema and its data have mysql fiddle here
I have also read this thread MySQL Open Balance Credit Debit Balance which is only working for single user.

So far I have tried:

SELECT loan_id,

 SUM(credit)-(SELECT SUM(a.debit) FROM transaction_t a 
   WHERE a.transaction_date 
   BETWEEN '2019-01-01' AND '2020-12-31' AND a.loan_id = loan_id) AS OpeningBalance, 

sum(credit),sum(debit), 
(@OpeningBalance+SUM(credit))-SUM(debit) AS closing_balance  
FROM transaction_t tr
WHERE transaction_date BETWEEN DATE('2019-01-01') AND DATE('2020-12-31') 
GROUP BY loan_id

But the above is not giving correct results ,How do i get the results like these ones?
A: Query made for date between 2017-01-01 and 2018-12-31

loan_id     opening_balance       sum(credit_amount)     sum(debit_amount)   closing_balance
1           0                     13,000.00                6,400.00           6,600.00 
2           0                     2,200.00                 2,500.00          -300

B: Query made for date between 2019-01-01 and 2020-12-31

loan_id     opening_balance       sum(credit_amount)     sum(debit_amount)   closing_balance
1           6,600                     100.00               200.00            6,500.00 
2           -300                      1,800.00             500.00            1,000

Answer

You are looking for conditional aggregation.

The key thing is that you need to start scanning the table from the beginning of the history in order to generate the initial balance. Then you just need to adjust the conditional sums:

Consider:

SET @start_date = '2017-01-01';
SET @end_date = '2018-12-31';

SELECT
    loan_id,
    SUM(CASE WHEN transaction_date < @start_date THEN credit - debit ELSE 0 END) opening_balance,
    SUM(CASE WHEN transaction_date BETWEEN @start_date AND @end_date THEN credit ELSE 0 END) sum_credit,
    SUM(CASE WHEN transaction_date BETWEEN @start_date AND @end_date THEN debit ELSE 0 END) sum_debit,
    SUM(CASE WHEN transaction_date <= @end_date THEN credit - debit ELSE 0 END) closing_balance
FROM transaction_t
WHERE transaction_date <= @end_date
GROUP BY loan_id

In your DB Fiddle, this returns:

loan_id opening_balance sum_credit  sum_debit   closing_balance
1       0               13000       6400        6600
2       0               2200        2500        -300

And when changing the dates to 2020-2021:

loan_id opening_balance sum_credit  sum_debit   closing_balance
1       6600            100         200         6500
2       -300            1800        500         1000

NB: that was a well-asked question, that SO could use more of!