title: MySQL query to select min datetime grouped by 30 day intervals
body: <p>Here's some dump data..</p>
  
  <pre><code>CREATE TABLE `customer` (
    `approve_datetime` datetime DEFAULT NULL,
    `created_date` date DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  INSERT INTO `customer` (`approve_datetime`, `created_date`)
  VALUES
      ('2015-08-20 04:43:00','2015-08-20'),
      (NULL,'2015-09-03'),
      ('2015-09-17 02:17:00','2015-09-17'),
      (NULL,'2015-09-29'),
      ('2015-09-29 12:44:00','2015-09-29'),
      ('2015-10-08 03:09:00','2015-10-08'),
      ('2016-01-20 08:59:00','2016-01-19'),
      ('2016-05-03 09:38:00','2016-05-02'),
      ('2016-07-15 11:06:00','2016-07-15'),
      (NULL,'2016-08-30'),
      ('2016-10-18 12:55:00','2016-10-18'),
      (NULL,'2017-01-08'),
      (NULL,'2017-02-02'),
      ('2017-02-13 02:58:00','2017-02-13');
  </code></pre>
  
  <p>Here is my current query which doesn't handle the 30 day groupings correctly. </p>
  
  <pre><code>SELECT a.*
  FROM customer a
  WHERE a.approve_datetime IN (
          SELECT MIN(b.approve_datetime) 
          FROM customer b 
          WHERE b.created_date BETWEEN a.created_date 
              AND DATE_ADD(a.created_date, INTERVAL 30 DAY)
      )
  </code></pre>
  
  <p>Which gives me the following.</p>
  
  <pre><code>+---------------------+--------------+
  | approve_datetime    | created_date |
  +---------------------+--------------+
  | 2015-08-20 04:43:00 | 2015-08-20   |
  | 2015-09-17 02:17:00 | 2015-09-17   |
  | 2015-09-29 12:44:00 | 2015-09-29   |
  | 2015-10-08 03:09:00 | 2015-10-08   |
  | 2016-01-20 08:59:00 | 2016-01-19   |
  | 2016-05-03 09:38:00 | 2016-05-02   |
  | 2016-07-15 11:06:00 | 2016-07-15   |
  | 2016-10-18 12:55:00 | 2016-10-18   |
  | 2017-02-13 02:58:00 | 2017-02-13   |
  +---------------------+--------------+
  </code></pre>
  
  <p>Can the query be altered to achieve the following results?</p>
  
  <pre><code>+---------------------+--------------+
  | approve_datetime    | created_date |
  +---------------------+--------------+
  | 2015-08-20 04:43:00 | 2015-08-20   |
  | 2015-09-29 12:44:00 | 2015-09-29   |
  | 2016-01-20 08:59:00 | 2016-01-19   |
  | 2016-05-03 09:38:00 | 2016-05-02   |
  | 2016-07-15 11:06:00 | 2016-07-15   |
  | 2016-10-18 12:55:00 | 2016-10-18   |
  | 2017-02-13 02:58:00 | 2017-02-13   |
  +---------------------+--------------+
  </code></pre>
  
  <p>Notice that records with created_date's 2015-09-17 and 2015-10-08 have been removed because they are within 30 days of the previous record which is the minimum date for that particular group. 2015-08-20 + 30 days starts off the first group with 2015-08-20 being the min date for that group.</p>
  
  <p>I hope what I'm trying to achieve makes sense.</p>