title: filtering of sql table with respect to time
body: <p>I have a following columns in a table</p>
  
  <pre><code>ISOCardNumber    Terminalno.    Eventdatetime
  456                 11        10/02/2017 7:12
  789                 13        10/02/2017 8:12
  456                 22        11/02/2017 11:13
  123                 11        10/02/2017 9:59
  789                 24        12/02/2017 9:12
  123                 22        10/02/17 11:59
  </code></pre>
  
  <p>the <code>isocardnumber</code> repeats for two times in the table and are first linked with terminalno. having odd numbers (which are limited to 11,13) and then it will be linked to even numbers (limited to 22, 24). the result need to be filtered with respect to the timing. 
  like i want to know how many <code>ISOcardnumber</code> where in and out with in <code>10/02/2017 07:12</code> to <code>11/02/2017 11:13</code></p>
  
  <p>I have tried the below query</p>
  
  <pre><code>SELECT ISOcardNumber, 
         MAX(CASE WHEN TerminalNumber IN (11) THEN TerminalNumber END) AS TerminalNumber_in, 
         MAX(CASE WHEN TerminalNumber IN (11) THEN EventDateTime END) AS Time_in, 
         MAX(CASE WHEN TerminalNumber IN (22, 24) THEN TerminalNumber END) 
                        AS TerminalNumber_out, 
         MAX(CASE WHEN terminalNumber IN (22, 24) THEN Eventdatetime END) AS time_out 
  FROM   dbo.vEvents
  WHERE  (TerminalNumber IN (11, 22, 24)) AND EventDateTime&gt;='10-2-2017' AND
         EventDateTime &lt;'12-2-2017' 
       AND (CAST(EventDateTime AS TIME) between '07:12' and '23:59')     
  GROUP BY ISOcardNumber
  ORDER BY Time_in DESC
  </code></pre>
  
  <p>but I couldn't filter for a long stretch of time like from <code>10/02/2017 07:12</code> to <code>11/02/2017 10:00</code></p>
  
  <p>to get the below result</p>
  
  <pre><code>TicketNumber    Machineno.   TimeIn             Machineno.   Timeout
  456                 11       10/02/2017 7:12    22           11/02/2017 11:13
  123                 11       10/02/2017 9:59    22           10/02/2017 11:59
  </code></pre>