I have an SQL query written against view sys.dba_audit_session to retrieve initial logon and final logoff times for users across a range of days.

So the basic query is

 SELECT DISTINCT username, 
         action_name,                     
         TO_DATE(timestamp) as logged_on_date,
         FIRST_VALUE(timestamp) OVER (PARTITION BY username, TO_DATE(timestamp) ORDER BY timestamp
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS time                                               
    FROM sys.dba_audit_session
   WHERE ....
     AND returncode = 0
     AND TO_DATE(TO_CHAR(timestamp),'DD-MON-RR') >= '01-FEB-19'
     AND TO_DATE(TO_CHAR(timestamp),'DD-MON-RR') <  '07-FEB-19'
     AND ACTION_NAME = 'LOGON' 
                        
   UNION
                         
   SELECT DISTINCT username,                          
          action_name,                                                     
          TO_DATE(timestamp) as logged_on_date,
          LAST_VALUE(timestamp) OVER (PARTITION BY username, TO_DATE(timestamp)  ORDER BY timestamp
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS time                             
     FROM sys.dba_audit_session
    WHERE ....
      AND returncode = 0
      AND TO_DATE(TO_CHAR(timestamp),'DD-MON-RR') >= '01-FEB-19'
      AND TO_DATE(TO_CHAR(timestamp),'DD-MON-RR') <  '07-FEB-19'
      AND ACTION_NAME = 'LOGOFF' 
    ORDER BY username ASC, ACTION_NAME DESC;

I now want to PIVOT this so I get columns of USERNAME, LOGGED_ON_DATE, LOGON_TIME, LOGOFF_TIME

My only solution of

   PIVOT 
     (
       MAX(time) 
       FOR action_name in ('LOGON', 'LOGOFF') 
      )                         
     ;

only gives me the data for the last day, where am I going wrong ?

Also do I need two queries with a UNION or is there a better way of doing the query in a single pass?

SELECT username,                          
       trunc ( timestamp ) dt,
       min ( case when action_name = 'LOGON' then timestamp end ) first_logon,
       max ( case when action_name = 'LOGOFF' then timestamp end ) last_logoff
from   sys.dba_audit_session
where  timestamp >= date'2019-02-01'
and    action_name in  ( 'LOGOFF' , 'LOGON' )
group  by username,                          
       trunc ( timestamp )
order  by username,                          
       trunc ( timestamp );

USERNAME   DT                     FIRST_LOGON            LAST_LOGOFF            
CHRIS      01-FEB-2019 00:00:00   01-FEB-2019 10:53:28   <null>                 
CHRIS      04-FEB-2019 00:00:00   04-FEB-2019 08:34:21   04-FEB-2019 13:57:10   
CHRIS      05-FEB-2019 00:00:00   05-FEB-2019 07:45:23   05-FEB-2019 09:32:33   
CHRIS      06-FEB-2019 00:00:00   06-FEB-2019 09:17:24   06-FEB-2019 10:18:33   
CHRIS      07-FEB-2019 00:00:00   07-FEB-2019 08:39:18   07-FEB-2019 11:39:04   
SCOTT      04-FEB-2019 00:00:00   04-FEB-2019 13:57:11   <null>                 
SCOTT      07-FEB-2019 00:00:00   07-FEB-2019 10:54:03   07-FEB-2019 11:09:57   
U          06-FEB-2019 00:00:00   06-FEB-2019 10:14:50   06-FEB-2019 10:18:30