Hi everybody,
In the database there's a table with checked in and out events. I want to calculate a cumulative total hours per week.
Currently I have this formula to calculate, but that give the results in decimals:
sum (((check_out - CHECK_IN)*24*60*60)) over (partition by employee_nr, to_char( check_in,'YYYY-IW') order by check_in) as Total_Per_Week
This is the requested result (the last column is what it should be):
| Employee Nr | Check In | Check Out | Hours In | New Cum. Total |
|---|
| 5911 | 01-04-2019 07:23:54 | 01-04-2019 16:09:20 | +00 08:45:26.000000 | 8:45 |
| 5911 | 02-04-2019 06:35:12 | 02-04-2019 15:53:26 | +00 09:18:14.000000 | 18:03 |
| 5911 | 04-04-2019 07:28:02 | 04-04-2019 15:49:40 | +00 08:21:38.000000 | 26:24 |
| 5911 | 05-04-2019 06:08:20 | 05-04-2019 16:05:10 | +00 09:56:50.000000 | 36:20 |
So the cumulative total goes over 24 hours, which it extra difficult.
Any ideas to achieve this?
Thanks in advance.
Regards,
Geert Joosten.