In **credits** table I have

> 
> (id, user\_id, process, amount, date\_add, date\_exp, date\_redeemed, remark)
> 
> 
> 

```
SELECT * FROM credits WHERE user_id = 2;

```

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

```

The following query I wrote will only calculate the balance, but I don't know whether the credit is expired and used before expired.

```sql
SELECT 
    u.id,
    email,
    CONCAT(first_name, ' ', last_name) AS name,
    type,
    (CASE
        WHEN (SUM(amount) IS NULL) THEN 0.00
        ELSE CASE
            WHEN
                (SUM(CASE
                    WHEN process = 'Add' THEN amount
                END) - SUM(CASE
                    WHEN process = 'Deduct' THEN amount
                END)) IS NULL
            THEN
                SUM(CASE
                    WHEN process = 'Add' THEN amount
                END)
            ELSE SUM(CASE
                WHEN process = 'Add' THEN amount
            END) - SUM(CASE
                WHEN process = 'Deduct' THEN amount
            END)
        END
    END) AS balance
FROM
    users u
        LEFT JOIN
    credits c ON u.id = c.user_id
GROUP BY u.id;

```

Or I am doing it in the wrong way? Maybe I should have done the calculation in my backend instead of SQL?

**EDIT 1:**

I want to calculate the balance of every user's e-wallet, but the credit will expire, 

IF it was expired AND not redeemed then exclude from the balance

ELSE IF used before expired AND redeem amount < expire amount
 THEN (balance - (expire amount - redeem amount))

ELSE IF used before expired AND redeem amount > expire amount
 THEN the usable balance will be deducted as expire amount is not enough to deduct redeemed amount

**EDIT 2:**

The query above will output 351.45, my expected output is 201.45. Which will not calculate the redemption on 2018-08-30 as the redeem amount is lower than the expired amount

**EDIT 3:**

User table:

```
+----+------------+-----------+----------+----------------+----------+
| id | first_name | last_name |   type   |     email      | password |
+----+------------+-----------+----------+----------------+----------+
|  2 | Test       | Oyster    | Employee | test@gmail.com | NULL     |
+----+------------+-----------+----------+----------------+----------+

```

My output:

```
+----+----------------+-------------+----------+---------+
| id |     email      |    name     |   type   | balance |
+----+----------------+-------------+----------+---------+
|  2 | test@gmail.com | Test Oyster | Employee |  351.45 |
+----+----------------+-------------+----------+---------+

```

Expected output: 

total (200+200+200) 600

redeemed amount 448.55 (200+50+198.55)

Remaining balance is 151.45

```
+----+----------------+-------------+----------+---------+
| id |     email      |    name     |   type   | balance |
+----+----------------+-------------+----------+---------+
|  2 | test@gmail.com | Test Oyster | Employee |  151.45 |
+----+----------------+-------------+----------+---------+

```