title: Handle expiry credits in a transaction table?
body: <p>Consider table <code>Credits</code> </p>
  
  <pre><code> CREATE TABLE `Credits` (
    `UserID` int(11) unsigned NOT NULL DEFAULT '0',
    `Amount` int(11) NOT NULL DEFAULT '0',
    `Created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    `Expire` datetime NOT NULL DEFAULT '9999-12-31 23:59:59'
  ) 
  </code></pre>
  
  <p>With data:</p>
  
  <pre><code>UserID    Amount    Created       Expire
  1         10        2016-01-14    2016-05-30
  1         -2        2016-02-04    9999-12-31
  1         3         2016-06-01    2016-09-30
  </code></pre>
  
  <p>Without the <strong>Expiry</strong> handing, to get the current amount of a user, it can be handled by a simple select</p>
  
  <pre><code> SELECT SUM(Amount) FROM Credits WHERE UserID = 1;
  </code></pre>
  
  <p>Now, I need to write a SELECT query, with an input parameter of date, and able to get the usable amount of credits at that time, like the following..</p>
  
  <p>At..</p>
  
  <ul>
  <li>2016-01-15 => 10</li>
  <li>2016-02-06 => 8</li>
  <li>2016-05-31 => 0</li>
  <li>2016-06-03 => 3</li>
  </ul>
  
  <p>Is it possible with just the above schema? Or I need to add extra field?</p>
  
  <p>SQLFiddle: <a href="http://sqlfiddle.com/#!9/3a52b/3" rel="nofollow">http://sqlfiddle.com/#!9/3a52b/3</a></p>