Calculate Available Balance from Wallet system excluding Expired Credits

There are basic structural problems with your current table. So I would propose some alterations to the table structure and subsequently application code. Table structures for Wallet systems can be very detailed; but I would suggest minimum possible changes here. I am not suggesting that it would be ideal way; but it should work. Initially, I will layout some of the problems with the current approach.

Problems:

• What if there are multiple Credits available which are not yet expired ?
• Out of these available Credits, some may actually have been utilized already, but not yet expired. How do we ignore them for available balance ?
• Also, some may have been partially utilized. How do we account for partial utilization ?
• There may be a scenario where a redemption amount spans across multiple unexpired credits. Some may get partially utilized; while some may get fully utilized.

General Practice:

We generally follow FIFO (First In First Out) approach, to give maximum benefit to the customer. So the older credits (which has higher chance of getting expired without utilization) gets utilized first.

In order to follow FIFO, we will have to effectively use a Looping technique in the query/application code every-time, in order to compute basic things, such as, "Available Wallet Balance", "Expired and Underutilized Credit", etc. Writing a query for this will be cumbersome and possibly inefficient at bigger scales

Solution:

We can add one more column amount_redeemed in your current table. It basically represent the amount which has already been redeemed against a specific credit.

ALTER TABLE credits ADD COLUMN amount_redeemed DECIMAL (8,2);

So, a filled table would look something like below:

+----+---------+---------+--------+-----------------+------------+---------------+---------------+----------+
| id | user_id | process | amount | amount_redeemed |  date_add  |  date_exp     | date_redeemed |  remark  |
+----+---------+---------+--------+-----------------+------------+---------------+---------------+----------+
| 22 |       2 | Add     | 200.00 |      200.00     | 2018-01-01 | 2019-01-01    |               | Credit1  |
| 23 |       2 | Add     | 200.00 |      200.00     | 2018-03-31 | 2019-03-31    |               | Credit2  |
| 24 |       2 | Deduct  | 200.00 |                 |            |               | 2018-04-28    | Redeemed |
| 25 |       2 | Add     | 200.00 |      0.00       | 2018-07-11 | 2018-10-11    |               | Campaign |
| 26 |       2 | Deduct  | 50.00  |                 |            |               | 2018-08-30    | Redeemed |
| 27 |       2 | Add     | 200.00 |      48.55      | 2018-10-01 | 2019-09-30    |               | Credit3  |
| 28 |       2 | Deduct  | 198.55 |                 |            |               | 2018-10-20    | Redeemed |
+----+---------+---------+--------+-----------------+------------+---------------+---------------+----------+

Notice that the amount_redeemed against Credit of id = 25 is 0.00, using FIFO approach. It got a chance for redemption on 2018-10-20, but by that time, it has expired already (date_exp = 2018-10-11)

So, now once we have this setup, you can do the following things in your application code:

1. Populate amount_redeemed value in existing rows in the table:

This will be a one time activity. For this, formulating a single query would be difficult (that is why we are here in the first place). So I would suggest you to do it one time in your application code (eg: PHP), using Loops and FIFO approach. Look at Point 3 below, to get an idea of how to do it in application code.

2. Get Current Available Balance:

Query for this becomes trivial now, as we just need to calculate Sum of amount - amount_redeemed for all Add process, which are not yet expired.

SELECT SUM(amount - amount_redeemed) AS total_available_credit
FROM credits 
WHERE process = 'Add' AND 
      date_exp > CURDATE() AND 
      user_id = 2

3. Update amount_redeemed at the time of redemption:

In this, you can firstly get all available Credits, which has amount available for redemption, and not yet expired.

SELECT id, (amount - amount_redeemed) AS available_credit 
FROM credits 
WHERE process = 'Add' AND 
      date_exp > CURDATE() AND 
      user_id = 2 AND 
      amount - amount_redeemed > 0
ORDER BY id

Now, we can loop over the above query results, and utilize the amount accordingly

 // PHP code example

 // amount to redeem
 $amount_to_redeem = 100;

 // Map storing amount_redeemed against id
 $amount_redeemed_map = array();

 foreach ($rows as $row) {

     // Calculate the amount that can be used against a specific credit
     // It will be the minimum of available credit and amount left to redeem
     $amount_redeemed  = min($row['available_credit'], $amount_to_redeem);

     // Populate the map
     $amount_redeemed_map[$row['id']] = $amount_redeemed;

     // Adjust the amount_to_redeem
     $amount_to_redeem -= $amount_redeemed;

     // If no more amount_to_redeem, we can finish the loop
     if ($amount_to_redeem == 0) {
         break;
     } elseif ($amount_to_redeem < 0) {

        // This should never happen, still if it happens, throw error
        throw new Exception ("Something wrong with logic!");
        exit();
     }

     // if we are here, that means some more amount left to redeem
 }

Now, you can use two Update queries. First one would update amount_redeemed value against all the Credit id(s). Second one would Insert the Deduct row using the sum of all individual amount_redeemed value.

SELECT `id`, `email`, `NAME`, `type`,
    (
        ( SELECT SUM(amount) FROM credit_table AS ct1 WHERE u.id = ct1.id AND process = 'ADD' AND date_exp > CURDATE()) - 
        ( SELECT SUM(amount) FROM credit_table AS ct2 WHERE u.id = ct2.id AND process = 'Deduct' )
    ) AS balance
FROM
    `user_table` AS u
WHERE
    id = 2;

Hope it works as you wish