MySQL: how to select record with latest date before a certain date
<p>Assume a simple table as follows containing status events for two users. A status_id of 1 makes them 'active', anything else makes them de-active.
I need to find out all those users that <strong>became</strong> inactive within one year of, for example, 2015-05-01 (not including that date).</p>

<pre><code>CREATE TABLE user_status(
      user_id INT,
      status_id INT,
      date_assigned VARCHAR(10) );

 INSERT INTO user_status( user_id, status_id, date_assigned)
 VALUES
 (1234, 1, '2015-01-01'), -- 1234 becomes active (status id = 1)
 (1234, 2, '2015-07-01'), -- 1234 de-activated for reason 2

 (5678, 1, '2015-02-01'), -- 5678 becomes active (status id = 1)
 (5678, 3, '2015-04-01'), -- 5678 de-activated for reason 3
 (5678, 5, '2015-06-01'); -- 5678 de-activated for reason 5
</code></pre>

<p>Using the query</p>

<pre><code>SELECT t1.*
FROM user_status t1
WHERE t1.date_assigned = (SELECT MIN(t2.date_assigned)       -- the first occurrence
                          FROM  user_status t2
                          WHERE t2.user_id =  t1.user_id     -- for this user
                          AND t2.status_id &lt;&gt; 1              -- where status not active
                          AND t2.date_assigned BETWEEN       -- within 1 yr of given date
                               '2015-05-01' + INTERVAL 1 DAY -- (not including that date)
                          AND 
                               '2015-05-01' + INTERVAL 1 YEAR 
                          )
</code></pre>

<p>I can get the result</p>

<pre><code>user_id   status_id   date_assigned
 1234       2          2015-07-01
 5678       5          2015-06-01
</code></pre>

<p>This is sort of right but user 5678 should not be there because although they had an inactive event within the date range, they were already inactive before the desired date range began and so did not <strong>become</strong> inactive within that range. </p>

<p>I need to add a bit to my query along the lines of 'only show me those users who had an inactive event <strong>and</strong> where the previous status_id for that user was 1, ie they were active at the time the inactive event happened.</p>

<p>Can anyone help me to get the syntax correct?<br>
See <a href="http://sqlfiddle.com/#!9/801c9/2" rel="nofollow">SQL fiddle</a> </p>