title: How to use sql/mysql to handle null values and certain cases?
body: <p>suppose we have the table below to track the shipping department's machine. It typically takes 5 mins for the machine to scan certain numbers of packages. However, it might have some problems during the scan process. Therefore, we would like to track each of the scan event durations? (Please refer to the below table)</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>date</th>
  <th>eventTime</th>
  <th>event</th>
  <th>actions</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2020-02-01</td>
  <td>2020-02-01 10:05:25</td>
  <td>scan_package</td>
  <td>start</td>
  </tr>
  <tr>
  <td>2020-02-01</td>
  <td>2020-02-01 10:10:25</td>
  <td>scan_package</td>
  <td>end</td>
  </tr>
  <tr>
  <td>2020-02-01</td>
  <td>2020-02-01 10:25:25</td>
  <td>scan_package</td>
  <td>start</td>
  </tr>
  <tr>
  <td>2020-02-01</td>
  <td>2020-02-01 10:30:25</td>
  <td>error</td>
  <td></td>
  </tr>
  <tr>
  <td>2020-02-01</td>
  <td>2020-02-01 10:35:25</td>
  <td>scan_package</td>
  <td>start</td>
  </tr>
  <tr>
  <td>2020-02-01</td>
  <td>2020-02-01 10:40:25</td>
  <td>scan_package</td>
  <td>end</td>
  </tr>
  <tr>
  <td>2020-02-01</td>
  <td>2020-02-01 10:42:25</td>
  <td>scan_package</td>
  <td>start</td>
  </tr>
  <tr>
  <td>2020-02-01</td>
  <td>2020-02-01 10:47:25</td>
  <td>scan_package</td>
  <td>end</td>
  </tr>
  <tr>
  <td>2020-02-01</td>
  <td>2020-02-01 10:51:25</td>
  <td>scan_package</td>
  <td>start</td>
  </tr>
  <tr>
  <td>2020-02-01</td>
  <td></td>
  <td>scan_package</td>
  <td>start</td>
  </tr>
  <tr>
  <td>2020-02-02</td>
  <td>2020-02-02 08:45:25</td>
  <td>scan_package</td>
  <td>start</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The scan duration is equivalent:  (event = scan_package &amp; action = end) - (event = scan_package &amp; action = start)</p>
  <p>However, there are some corner cases that need to handle.</p>
  <ol>
  <li>for row3 (eventTime:2020-02-01_10:25:25) from the table, there is no corresponding end scan event, due to within 5 mins after the scan started, there is no scan ended. Therefore, we will not have the scan duration for this row, <strong>but we still need to record the event to the final table.</strong> (not sure how to record in this case)</li>
  </ol>
  <p>Basically, in order to consider a valid scan duration, the start and end should be within 6 mins.</p>
  <ol start="2">
  <li><p>will not consider the scan event is valid if there is no eventTime like (last second row from the table), we will just ignore the scan event.</p>
  </li>
  <li><p>assume there are no situations like (actions = start, start, start, then end ...)
  which means there will not be multiple start actions happening at the same time or in sequence.</p>
  </li>
  <li><p>assume the column eventTime is timestamp type</p>
  </li>
  </ol>
  <p>Sample desired final output table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>date</th>
  <th>eventTime1</th>
  <th>event1</th>
  <th>eventTime2</th>
  <th>event2</th>
  <th>scan_duration_in_mins</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2020-02-01</td>
  <td>2020-02-01 10:05:25</td>
  <td>scan_start</td>
  <td>2020-02-01 10:10:25</td>
  <td>scan_end</td>
  <td>5</td>
  </tr>
  <tr>
  <td>2020-02-01</td>
  <td>2020-02-01 10:10:25</td>
  <td>scan_start</td>
  <td>null</td>
  <td>null</td>
  <td>null</td>
  </tr>
  <tr>
  <td>2020-02-01</td>
  <td>2020-02-01 10:35:25</td>
  <td>scan_start</td>
  <td>2020-02-01 10:40:25</td>
  <td>scan_end</td>
  <td>5</td>
  </tr>
  <tr>
  <td>2020-02-01</td>
  <td>2020-02-01 10:42:25</td>
  <td>scan_start</td>
  <td>2020-02-01 10:47:25</td>
  <td>scan_end</td>
  <td>5</td>
  </tr>
  <tr>
  <td>2020-02-01</td>
  <td>2020-02-01 10:51:25</td>
  <td>scan_start</td>
  <td>null</td>
  <td>null</td>
  <td>null</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Below is some of my code,</p>
  <pre><code>CREATE TABLE scan (
      date DATE,
      eventTime datetime,
      event VARCHAR(100),
      actions VARCHAR(100)
  );
  
  INSERT INTO scan VALUES ( '2020-02-01' , '2020-02-01 10:05:25', 'scan_package' , 'start');
  INSERT INTO scan VALUES ( '2020-02-01' , '2020-02-01 10:10:25', 'scan_package' , 'end');
  INSERT INTO scan VALUES ( '2020-02-01' , '2020-02-01 10:25:25', 'scan_package' , 'start');
  INSERT INTO scan VALUES ( '2020-02-01' , '2020-02-01 10:30:25', 'error' ,          null );
  INSERT INTO scan VALUES ( '2020-02-01' , '2020-02-01 10:35:25', 'scan_package' , 'start');
  INSERT INTO scan VALUES ( '2020-02-01' , '2020-02-01 10:40:25', 'scan_package' , 'end');
  INSERT INTO scan VALUES ( '2020-02-01' , '2020-02-01 10:42:25', 'scan_package' , 'start');
  INSERT INTO scan VALUES ( '2020-02-01' , '2020-02-01 10:47:25', 'scan_package' , 'end');
  INSERT INTO scan VALUES ( '2020-02-01' , '2020-02-01 10:51:25', 'scan_package' , 'start');
  INSERT INTO scan VALUES ( '2020-02-01' ,  null ,                'scan_package' , 'start');
  INSERT INTO scan VALUES ( '2020-02-02' , '2020-02-02 08:45:25', 'scan_package' , 'start');
  
  SELECT 
      t1.date as date,
      t1.eventTime as scan_start_time,
      t1.event as scan_start,
      t2.eventTime as scan_end_time,
      t2.event as scan_end
  FROM  scan as t1 
  LEFT JOIN scan as t2 ON t2.eventTime BETWEEN t1.eventTime + INTERVAL 4 MINUTE
                                         AND t1.eventTime + INTERVAL 6 MINUTE
                    AND t1.actions = 'start'
                    AND t2.actions &lt;&gt; 'start';
                   
  </code></pre>
  <p>However, the code above did not get the desired output.
  Can someone please provide me with some suggestions or SQL code?
  Thanks a lot</p>