1

Topic: To calculate at waste o'clock, time of a dinner/smoke break for each worker

The guru of the given forum, the help is very necessary to me, I can not  how correctly to solve a problem.
YOUR HELP IS VERY VERY NECESSARY!!!!!!!!!!!
There is a table worker_time, containing id the worker (worker_id), time of its stopping for operation (dt_in) and time of an output from operation (dt_out) per every day
worker_id dt_in dt_out
1 1/10/2018 08:34:55 10.01.2018 11:34:55 AM
1 1/10/2018 12:26:55 10.01.2018 3:49:55 PM
1 1/10/2018 15:58:55 10.01.2018 5:20:55 PM
2 1/10/2018 09:00:55 10.01.2018 2:00:55 PM
2 1/10/2018 14:55:55 10.01.2018 6:05:55 PM
Dinner/smoke break time on each worker per every day is necessary to calculate at waste o'clock,
While on mind the such comes only while here.
SELECT worker_id as "Employee";
ROUND ((dt_out-dt_in) * 24,2) as ", in hours"
FROM worker_time;
Further the Brain does not cook, I not the programmer and even not not beginning. This problem from University

2

Re: To calculate at waste o'clock, time of a dinner/smoke break for each worker

Malenki_2000;

with q as (select 1 as id, to_date (' 1/10/2018 OF 8:34:55 AM ', ' DD.MM.YYYY HH24:MI:SS') as come_in, to_date (' 1/10/2018 OF 11:34:55 AM ', ' DD.MM.YYYY HH24:MI:SS') as come_out from dual union all
select 1, to_date (' 1/10/2018 OF 0:26:55 PM ', ' DD.MM.YYYY HH24:MI:SS'), to_date (' 1/10/2018 OF 3:49:55 PM ', ' DD.MM.YYYY HH24:MI:SS ') from dual union all
select 1, to_date (' 1/10/2018 OF 3:58:55 PM ', ' DD.MM.YYYY HH24:MI:SS'), to_date (' 1/10/2018 OF 5:20:55 PM ', ' DD.MM.YYYY HH24:MI:SS ') from dual union all
select 2, to_date (' 1/10/2018 OF 9:00:55 AM ', ' DD.MM.YYYY HH24:MI:SS'), to_date (' 1/10/2018 OF 2:00:55 PM ', ' DD.MM.YYYY HH24:MI:SS ') from dual union all
select 2, to_date (' 1/10/2018 OF 2:55:59 PM ', ' DD.MM.YYYY HH24:MI:SS'), to_date (' 1/10/2018 OF 6:05:55 PM ', ' DD.MM.YYYY HH24:MI:SS ') from dual)
select id
, trunc (sum (q.come_out-q.come_in) *24)
||': ' || lpad (trunc (((sum (q.come_out-q.come_in) *24)-trunc (sum (q.come_out-q.come_in) *24)) *60), 2, ' 0 ')
||': ' || lpad (trunc (((((sum (q.come_out-q.come_in) *24)-trunc (sum (q.come_out-q.come_in) *24)) *60) - trunc (((sum (q.come_out-q.come_in) *24)-trunc (sum (q.come_out-q.come_in) *24)) *60)) *60), 2, ' 0 ') as "is fulfilled"
from q group by id

3

Re: To calculate at waste o'clock, time of a dinner/smoke break for each worker

Dshedoo wrote:

Malenki_2000;

with q as (select 1 as id, to_date (' 1/10/2018 OF 8:34:55 AM ', ' DD.MM.YYYY HH24:MI:SS') as come_in, to_date (' 1/10/2018 OF 11:34:55 AM ', ' DD.MM.YYYY HH24:MI:SS') as come_out from dual union all
select 1, to_date (' 1/10/2018 OF 0:26:55 PM ', ' DD.MM.YYYY HH24:MI:SS'), to_date (' 1/10/2018 OF 3:49:55 PM ', ' DD.MM.YYYY HH24:MI:SS ') from dual union all
select 1, to_date (' 1/10/2018 OF 3:58:55 PM ', ' DD.MM.YYYY HH24:MI:SS'), to_date (' 1/10/2018 OF 5:20:55 PM ', ' DD.MM.YYYY HH24:MI:SS ') from dual union all
select 2, to_date (' 1/10/2018 OF 9:00:55 AM ', ' DD.MM.YYYY HH24:MI:SS'), to_date (' 1/10/2018 OF 2:00:55 PM ', ' DD.MM.YYYY HH24:MI:SS ') from dual union all
select 2, to_date (' 1/10/2018 OF 2:55:59 PM ', ' DD.MM.YYYY HH24:MI:SS'), to_date (' 1/10/2018 OF 6:05:55 PM ', ' DD.MM.YYYY HH24:MI:SS ') from dual)
select id
, trunc (sum (q.come_out-q.come_in) *24)
||': ' || lpad (trunc (((sum (q.come_out-q.come_in) *24)-trunc (sum (q.come_out-q.come_in) *24)) *60), 2, ' 0 ')
||': ' || lpad (trunc (((((sum (q.come_out-q.come_in) *24)-trunc (sum (q.come_out-q.come_in) *24)) *60) - trunc (((sum (q.come_out-q.come_in) *24)-trunc (sum (q.come_out-q.come_in) *24)) *60)) *60), 2, ' 0 ') as "is fulfilled"
from q group by id

Thanks for  with fulfilled  and how to count from all this parsley break time?

4

Re: To calculate at waste o'clock, time of a dinner/smoke break for each worker

Malenki_2000 wrote:

and how to count from all this parsley break time

24 hours a minus

wrote:

  with fulfilled

smile))

5

Re: To calculate at waste o'clock, time of a dinner/smoke break for each worker

Malenki_2000;

with q as (select 1 as id, to_date (' 1/10/2018 OF 8:34:55 AM ', ' DD.MM.YYYY HH24:MI:SS') as come_in, to_date (' 1/10/2018 OF 11:34:55 AM ', ' DD.MM.YYYY HH24:MI:SS') as come_out from dual union all
select 1, to_date (' 1/10/2018 OF 0:26:55 PM ', ' DD.MM.YYYY HH24:MI:SS'), to_date (' 1/10/2018 OF 3:49:55 PM ', ' DD.MM.YYYY HH24:MI:SS ') from dual union all
select 1, to_date (' 1/10/2018 OF 3:58:55 PM ', ' DD.MM.YYYY HH24:MI:SS'), to_date (' 1/10/2018 OF 5:20:55 PM ', ' DD.MM.YYYY HH24:MI:SS ') from dual union all
select 2, to_date (' 1/10/2018 OF 9:00:55 AM ', ' DD.MM.YYYY HH24:MI:SS'), to_date (' 1/10/2018 OF 2:00:55 PM ', ' DD.MM.YYYY HH24:MI:SS ') from dual union all
select 2, to_date (' 1/10/2018 OF 2:55:59 PM ', ' DD.MM.YYYY HH24:MI:SS'), to_date (' 1/10/2018 OF 6:05:55 PM ', ' DD.MM.YYYY HH24:MI:SS ') from dual)
select id, (to_char (min (come_in), ' HH24:MI:SS') || ' - ' || to_char (max (come_out), ' HH24:MI:SS')) as "For the period"
, lpad (trunc (sum (q.come_out-q.come_in) *24), 2, ' 0 ')
||': ' || lpad (trunc (((sum (q.come_out-q.come_in) *24)-trunc (sum (q.come_out-q.come_in) *24)) *60), 2, ' 0 ')
||': ' || lpad (trunc (((((sum (q.come_out-q.come_in) *24)-trunc (sum (q.come_out-q.come_in) *24)) *60) - trunc (((sum (q.come_out-q.come_in) *24)-trunc (sum (q.come_out-q.come_in) *24)) *60)) *60), 2, ' 0 ') as "is fulfilled"
, lpad (trunc (((max (come_out) - min (come_in))-sum (q.come_out-q.come_in)) *24), 2, ' 0 ')
||': ' || lpad (trunc (((((max (come_out) - min (come_in))-sum (q.come_out-q.come_in)) *24)-trunc (((max (come_out) - min (come_in))-sum (q.come_out-q.come_in)) *24)) *60), 2, ' 0 ')
||': ' || lpad (trunc (((((((max (come_out) - min (come_in))-sum (q.come_out-q.come_in)) *24)-trunc (((max (come_out) - min (come_in))-sum (q.come_out-q.come_in)) *24)) *60) - trunc (((((max (come_out) - min (come_in))-sum (q.come_out-q.come_in)) *24)-trunc (((max (come_out) - min (come_in))-sum (q.come_out-q.come_in)) *24)) *60)) *60), 2, ' 0 ') as "is not fulfilled"
from q group by id

But it all the same has a little the general with a reality.
In a reality at you the table will look like:
The employee - Tip_dejstvija - action Date
- came - 8:00
Kohl - came - 9:00
- came - 9:01
Kohl - left - 9:02
But it already another story altogether...

6

Re: To calculate at waste o'clock, time of a dinner/smoke break for each worker

In a question of working hours it is a lot of nuances which are necessary for describing explicitly.
The part was considered here: catching "" on operation
And what such a dinner, a smoke break, a break? Whether to show them separately or jointly? Whether there is the forced minimum time of a dinner (did not leave anywhere, looked cats and ate directly from banks on a workplace)?
Certainly, the most simple variant:
. = sum (____ - _)
On all steams an input-exit, and
Break = (__ - __) - .
But in a reality many various special cases which in this circuit not to push