Calculate your working hours

The following query calculates your working hours by condensing the APEX_WORKSPACE_ACTIVITY_LOG.:

 

select distinct APEX_USER,
        TO_CHAR(MIN_DATE, 'Day DD.Mon.') START_DATE,
        TO_CHAR(MIN_DATE, 'HH24:MI') START_TIME,
        TO_CHAR(MAX_DATE, 'HH24:MI') STOP_TIME,
        EXTRACT(HOUR FROM WORK_TIME_PERIOD) || ':' ||
        LTRIM(TO_CHAR(ROUND(
          EXTRACT(MINUTE FROM WORK_TIME_PERIOD)
        + EXTRACT(SECOND FROM WORK_TIME_PERIOD) / 60, 0), '09'))
        WORKING_HOURS,
        EXTRACT(HOUR FROM BREAK_TIME_PERIOD) || ':' ||
        LTRIM(TO_CHAR(ROUND(
          EXTRACT(MINUTE FROM BREAK_TIME_PERIOD)
        + EXTRACT(SECOND FROM BREAK_TIME_PERIOD) / 60, 0), '09'))
        BREAK_HOURS,
        APPLICATION_NAME, PAGE_NAME, EVENT_CNT, GID
from (
    select distinct APEX_USER, MIN_DATE, MAX_DATE,
        ((MAX_DATE - MIN_DATE) DAY(9) TO SECOND) WORK_TIME_PERIOD,
        ((LEAD(MIN_DATE) OVER (PARTITION BY APEX_USER ORDER BY MIN_DATE ASC)
        - MAX_DATE) DAY(9) TO SECOND) BREAK_TIME_PERIOD,
        APPLICATION_NAME, PAGE_NAME, EVENT_CNT, GID
    from (
        select distinct APEX_USER, GID,
                MIN(VIEW_DATE) OVER (PARTITION BY APEX_USER, GID) MIN_DATE,
                MAX(VIEW_DATE) OVER (PARTITION BY APEX_USER, GID) MAX_DATE,
                COUNT(VIEW_DATE) OVER (PARTITION BY APEX_USER, GID) EVENT_CNT,
                FIRST_VALUE(APPLICATION_NAME) OVER(PARTITION BY APEX_USER, GID ORDER BY VIEW_DATE DESC) APPLICATION_NAME,
                FIRST_VALUE(PAGE_NAME) OVER(PARTITION BY APEX_USER, GID ORDER BY VIEW_DATE DESC) PAGE_NAME
        from (
            select APEX_USER, APPLICATION_NAME, PAGE_NAME, VIEW_DATE,
                SUM(GAP) OVER (PARTITION BY APEX_USER ORDER BY VIEW_DATE ASC) GID
            from (
                select APEX_USER, APPLICATION_NAME, PAGE_NAME, VIEW_DATE,
                    CASE WHEN VIEW_DATE - FIRST_VALUE(VIEW_DATE) OVER(PARTITION BY APEX_USER
                                            ORDER BY VIEW_DATE ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
                                            < 1 / 1440 * 20 THEN 0 ELSE 1 END AS GAP
                from APEX_WORKSPACE_ACTIVITY_LOG
             )
        )
    )
)
where APEX_USER = V('APP_USER')
and MIN_DATE > SYSDATE - 3
order by GID;