I have a Oracle database that saves every time a user makes an operation and I want to know how many hours each of them are working. My logic to find this number is the following: For every operation done in a 10 minute timespan, that time is considered as "working time".
So, if someone makes an operation at 10:05 and another at 10:25, I will be considering that he worked from 10:00-10:10 and from 10:20-10:30, 20 minutes total.
I want to have a query that calculates the total of minutes, following this logic, dividing for each user.
I tried following this blog post, and was able to achieve the expected result, with only one user, however. Once I tried to include multiple users, the query would run infinitely. Below is the query that worked, with only one user.
How can I be able to replicate this logic, with multiple users?
WITH database AS(
SELECT
operation_date,
user
FROM
database
where
user = 'user1'
)
,start_date as (
select
min (operation_date ) AS start_date,
10 / 1440 AS time_interval
from database
)
, intervals as (
select
start_date + ( ( level - 1 ) * time_interval ) start_date,
start_date + ( ( level ) * time_interval ) end_date
from start_date
connect by level <= 144
)
,timesum as(
select
user,
to_char(start_date,'dd/mm/yyyy hh24:mi'),
count ( operation_date ) as oper_num
from intervals
left join database
on start_date <= operation_date
and operation_date < end_date
group by user, start_date
order by start_date
)
select
user,
(count(oper_num)*10)/60 as hours
from timesum
where
oper_num > 0
group by user
Since my comment seemed to help, I'm writing it up as an answer.
Since you only want the totals and since the periods seem to be 10 minute intervals relative to midnight (as opposed to being relative to the user's first action), all you need to do is round the times to the 10 minute floor and then count the distinct values. You can do something along the lines of:
SELECT
"USER",
COUNT(DISTINCT operation_date - MOD(TO_CHAR(operation_date, 'MI'), 10)/(24*60) - TO_CHAR(operation_date, 'SS')/(24*60*60)) * 10/60 t
FROM "DATABASE"
GROUP BY "USER"
ORDER BY "USER"
You can see it working in this Fiddle.
- MOD(TO_CHAR(operation_date, 'MI'), 10)/(24*60) will get the 10 minute floor for minutes (e.g. 10:23:01 -> 10:20:01) and - TO_CHAR(operation_date, 'SS')/(24*60*60) will remove the seconds (e.g. 10:20:01 -> 10:20:00). Then by counting the distinct values for each user, you know how many 10 minute time periods they were working for and can calculate the total time.
(In the comments, my original version of this did not account for seconds, but it's cleaned up here. I also had an error in my day<->minutes arithmetic for rounding the minutes.)
This could probably be cleaned up more to use interval instead of the 24*60 and 24*60*60 arithmetic and to explicitly use to_number instead of relying on automatic conversion. But some of that will be preference and some of that will depend on the actual context where/how you're using this.
I kept the column/table names from the question. As others have noted in the comments and in the other answer, actually using these names is not a good idea since it will just cause confusion and unnecessary complexity.