The following script reads a table containing user actions and calculates the time difference (in hours) between the first and last action per user and work day.
The field action_tm is of type TIMESTAMP.
SELECT TRUNC (action_tm) work_day, user_nm,
MIN (action_tm) start_time,
MAX (action_tm) end_time,
ROUND (((TO_CHAR (MAX (action_tm), 'SSSSS') + TO_CHAR (MAX (action_tm), 'FF') * .000001) -
(TO_CHAR (MIN (action_tm), 'SSSSS') + TO_CHAR (MIN (action_tm), 'FF') * .000001)) /60/60,2) work_hours
FROM user_actions a
GROUP BY TRUNC (action_tm), user_nm
ORDER BY TRUNC (action_tm);
The field action_tm is of type TIMESTAMP.
SELECT TRUNC (action_tm) work_day, user_nm,
MIN (action_tm) start_time,
MAX (action_tm) end_time,
ROUND (((TO_CHAR (MAX (action_tm), 'SSSSS') + TO_CHAR (MAX (action_tm), 'FF') * .000001) -
(TO_CHAR (MIN (action_tm), 'SSSSS') + TO_CHAR (MIN (action_tm), 'FF') * .000001)) /60/60,2) work_hours
FROM user_actions a
GROUP BY TRUNC (action_tm), user_nm
ORDER BY TRUNC (action_tm);
No comments:
Post a Comment