title: Query Advice needed
body: <p>I have a table where I manage user login on a machine. Based on the activity type I determine if it is a logon or logoff. I have been asked to generate a report on user login/logout. It works for all the shifts, but doesn't work for Midnight shift. </p>
  
  <p>Sample Table:</p>
  
  <pre><code>User_id Activity_code timestamp
  1       login         2016-03-28 11:10:00
  1       logout        2016-03-28 19:45:00
  2       login         2017-02-15 23:00:00
  2       logout        2017-02-16 07:00:00
  </code></pre>
  
  <p>If I group by timestamp I get the report properly for user id1, but not for user 2.</p>
  
  <pre><code>desc pmevents_bkp
  
   Name           Null?    Type
   -------------  -------  --------------
   EVENTTYPE      NOT NULL  NUMBER(4)
   USERID                   CHAR(32)
   JOBNUMBER                NUMBER(11)
   TIMESTAMP      NOT NULL  DATE
   SEQUENCENUMBER           NUMBER(8)
  
   SQL &gt;select * from pmevents_bkp;
  
   EVENTTYPE USERID                            JOBNUMBER TIMESTAMP SEQUENCENUMBER
  ---------- -------------------------------- ---------- --------- --------------
         101 JBOND 007                             12345 11-MAR-17          12345
         101 JBOND 007                             12345 12-MAR-17          12345
         101 JBOND 008                              3245 11-MAR-17           3245
         100 JBOND 007                             12345 11-MAR-17           3245
  
   SQL &gt;select to_char(timestamp, 'MM/DD/YYYY'),
      2  userid,
      3  MIN(timestamp) KEEP (DENSE_RANK FIRST ORDER BY eventtype) "Login",
      4  MAX(timestamp) KEEP (DENSE_RANK LAST ORDER BY eventtype) "Logout"
      5     FROM pmevents_bkp
      6     where  timestamp BETWEEN TO_DATE('01-mar-17','DD-MON-YY') AND TO_DATE('30-mar-17','DD-MON-YY')
      7  and (eventtype = '100'
      8  or eventtype = '101')
      9  group by to_char(timestamp, 'MM/DD/YYYY'),userid;
  
  TO_CHAR(TI USERID                           Login     Logout
  ---------- -------------------------------- --------- ---------
  03/11/2017 JBOND 007                        11-MAR-17 11-MAR-17
  03/11/2017 JBOND 008                        11-MAR-17 11-MAR-17
  03/12/2017 JBOND 007                        12-MAR-17 12-MAR-17
  </code></pre>