title: I am trying to find unallocated time from specified time slot using MySQL query
body: <p>Let say reserved time slot is </p>
  
  <p>09:00 - 10:00</p>
  
  <p>Following are the request reservation:</p>
  
  <p>1) 08:00 - 09:15</p>
  
  <p>For this request,result should show 08:00 - 09:00</p>
  
  <p>2) 09:00 - 10:00</p>
  
  <p>For this request,result should show 00:00 - 00:00</p>
  
  <p>3) 09:45 - 10:45</p>
  
  <p>For this request,result should show 10:00 - 10:45</p>
  
  <p>4) 10:00 - 11:00</p>
  
  <p>For this request,result should show 10:00 - 11:00</p>
  
  <p>I am trying with following query but it does not give  proper result:</p>
  
  <pre><code>SELECT 
    CASE
  
      WHEN  time_from &gt; p_in_time  THEN
      p_in_time
      WHEN  time_to &lt;= p_in_time  THEN
      p_in_time
  
      ELSE '00:00:00' 
    END AS slot_in_time,
    CASE
  
      WHEN  time_from &gt;= p_out_time  THEN 
        time_from
      WHEN   .time_to &lt;= p_out_time  THEN
         p_out_time
      ELSE '00:00:00' 
    END AS slot_out_time 
  FROM
    tbl_reserved_time
  </code></pre>
  
  <p>Any help will be great help for me. </p>
  
  <hr>
  
  <p>@shadow  Thanks for your response:I try with following query:                             SELECT 
    CASE</p>
  
  <pre><code>WHEN  start_time &gt; @p_in_time  THEN
  @p_in_time
  WHEN  end_time &lt;= @p_in_time  THEN
  @p_in_time
  
  
  ELSE '00:00:00' 
  </code></pre>
  
  <p>END AS slot_in_time,
    CASE</p>
  
  <pre><code>WHEN  end_time &gt;= @p_out_time  THEN 
    start_time
  WHEN   end_time &lt;= @p_out_time  THEN
     @p_out_time
  ELSE '00:00:00' 
  </code></pre>
  
  <p>END AS slot_out_time 
  FROM
    tbl_reserved_time  </p>
  
  <p>It works only for 1 condition (08:00-09:15)</p>