title: Calculate Available Balance from Wallet system excluding Expired Credits
body: <p>In <strong>credits</strong> table I have</p>
  
  <blockquote>
    <p>(id, user_id, process, amount, date_add, date_exp, date_redeemed, remark)</p>
  </blockquote>
  
  <pre><code>SELECT * FROM credits WHERE user_id = 2;
  </code></pre>
  
  <pre><code>+----+---------+---------+--------+------------+------------+---------------+----------+
  | 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 |
  +----+---------+---------+--------+------------+------------+---------------+----------+
  </code></pre>
  
  <p>The following query I wrote will only calculate the balance, but I don't know whether the credit is expired and used before expired.</p>
  
  <pre class="lang-sql prettyprint-override"><code>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;
  </code></pre>
  
  <p>Or I am doing it in the wrong way? Maybe I should have done the calculation in my backend instead of SQL?</p>
  
  <p><strong>EDIT 1:</strong></p>
  
  <p>I want to calculate the balance of every user's e-wallet, but the credit will expire, </p>
  
  <p>IF it was expired AND not redeemed then exclude from the balance</p>
  
  <p>ELSE IF used before expired AND redeem amount &lt; expire amount
    THEN (balance - (expire amount - redeem amount))</p>
  
  <p>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</p>
  
  <p><strong>EDIT 2:</strong></p>
  
  <p>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</p>
  
  <p><strong>EDIT 3:</strong></p>
  
  <p>User table:</p>
  
  <pre><code>+----+------------+-----------+----------+----------------+----------+
  | id | first_name | last_name |   type   |     email      | password |
  +----+------------+-----------+----------+----------------+----------+
  |  2 | Test       | Oyster    | Employee | test@gmail.com | NULL     |
  +----+------------+-----------+----------+----------------+----------+
  </code></pre>
  
  <p>My output:</p>
  
  <pre><code>+----+----------------+-------------+----------+---------+
  | id |     email      |    name     |   type   | balance |
  +----+----------------+-------------+----------+---------+
  |  2 | test@gmail.com | Test Oyster | Employee |  351.45 |
  +----+----------------+-------------+----------+---------+
  </code></pre>
  
  <p>Expected output: </p>
  
  <p>total (200+200+200) 600</p>
  
  <p>redeemed amount     448.55 (200+50+198.55)</p>
  
  <p>Remaining balance is 151.45</p>
  
  <pre><code>+----+----------------+-------------+----------+---------+
  | id |     email      |    name     |   type   | balance |
  +----+----------------+-------------+----------+---------+
  |  2 | test@gmail.com | Test Oyster | Employee |  151.45 |
  +----+----------------+-------------+----------+---------+
  </code></pre>