Select overlaped hours

Question

I have table

CREATE TABLE card_tab
(card_no    NUMBER,
 emp_no     VARCHAR2(100),
 DATA       DATE,
 start_time DATE,
 end_time   DATE)

insert into card_tab (CARD_NO, EMP_NO, DATA, START_TIME, END_TIME)
values (1, '100', to_date('15-11-2019', 'dd-mm-yyyy'), to_date('15-11-2019 20:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('16-11-2019 03:00:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into card_tab (CARD_NO, EMP_NO, DATA, START_TIME, END_TIME)
values (2, '100', to_date('15-11-2019', 'dd-mm-yyyy'), to_date('15-11-2019 22:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('15-11-2019 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));

The card_no is only sequence. Emp_no was working 7 hours

SELECT t.*, (t.end_time - t.start_time) * 24 work_hours FROM card_tab t;
CARD_NO EMP_NO  DATA          START_TIME              END_TIME              WORK_HOURS
1       100     15.11.2019    15.11.2019 20:00:00     16.11.2019 03:00:00   7
2       100     15.11.2019    15.11.2019 22:00:00     15.11.2019 23:00:00   1

If hours are overlaped then sould be divided by 2 in this example it will be for

CARD_NO  WORK_HOURS
1        6,5
2        0,5

The sum of working hours is 7 so it's correct. There can be more than two overlaped records. I do a lot of loops but i think this can be do more easier. It's loo like island and gap problem but i don't know how to solve it.

tmrozek tmrozek · Accepted Answer · 2019-11-18T08:06:43

You can try this:

with data as
(
  select exact_hour
        , card_no
        , emp_no
        , ( select count(1) 
            from card_tab 
            where exact_hour >= start_time 
              and exact_hour < end_time) cnt
  from
  (
    select distinct start_time + (level - 1)/24 exact_hour
          , tab.card_no
          , emp_no
    from card_tab tab
    connect by nocycle level <= (end_time - start_time) * 24
  )
)
select card_no, sum(1 / cnt)
from data
group by card_no
;

Some explanation:

(level - 1)

it has been made that way because we only refer to start of each hour

exact_hour >= start_time and exact_hour < end_time

we can't use between keyword because in your data start of each period is equal to end of the previous one so for example period 22:00-23:00 would appear in two start hours

sum(1 / cnt)

we want to sum every part of hour so we need to divide each hour by number of different cards which were worked (?? - I don't know the exact business case)

I suppose everything except that three points is clear.

Select overlaped hours

1 Answers