Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
380 views
in Technique[技术] by (71.8m points)

oracle - Calculate time diffrence in SQL with shifts

i have two table the first table contains the record of a ticket with start date and end date

 start_date           | End_Date
 21-02-2017 07:52:32  | 22-02-2017 09:56:32
 21-02-2017 09:52:32  | 23-02-2017 17:52:32

the second table contains the details of the weekly shift:

 shift_day | Start_Time | End_Time
  MON        9:00          18:00
  TUE        10:00         19:00
  WED        9:00          18:00
  THU        10:00         19:00
  FRI        9:00          18:00

I am looking to get the time difference in the first table which will only include the time as per the second table.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Use a recursive sub-query factoring clause to generate each day within your time ranges and then correlate that with your shifts to restrict the time for each day to be within the shift hours and then aggregate to get the total:

Oracle 18 Setup:

CREATE TABLE times ( start_date, End_Date ) AS
SELECT DATE '2017-02-21' + INTERVAL '07:52:32' HOUR TO SECOND,
       DATE '2017-02-22' + INTERVAL '09:56:32' HOUR TO SECOND
FROM   DUAL
UNION ALL
SELECT DATE '2017-02-21' + INTERVAL '09:52:32' HOUR TO SECOND,
       DATE '2017-02-23' + INTERVAL '17:52:32' HOUR TO SECOND
FROM   DUAL;

CREATE TABLE weekly_shifts ( shift_day, Start_Time, End_Time ) AS
SELECT 'MON', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 'TUE', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 'WED', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 'THU', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 'FRI', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL;

Query 1:

WITH days ( id, start_date, day_start, day_end, end_date ) AS (
  SELECT ROWNUM,
         start_date,
         start_date,
         LEAST( TRUNC( start_date ) + INTERVAL '1' DAY, end_date ),
         end_date
  FROM   times
UNION ALL
  SELECT id,
         start_date,
         day_end,
         LEAST( day_end + INTERVAL '1' DAY, end_date ),
         end_date
  FROM   days
  WHERE  day_end < end_date
)
SELECT start_date,
       end_date,
       SUM( shift_end - shift_start ) AS days_worked_on_shift
FROM   (
  SELECT ID,
         start_date,
         end_date,
         GREATEST( day_start, TRUNC( day_start ) + start_time ) AS shift_start,
         LEAST( day_end, TRUNC( day_start ) + end_time ) AS shift_end
  FROM   days d
         INNER JOIN
         weekly_shifts w
         ON ( TO_CHAR( d.day_start, 'DY' ) = w.shift_day )
)
GROUP BY id, start_date, end_date;

Result:

START_DATE          END_DATE            DAYS_WORKED_ON_SHIFT
------------------- ------------------- --------------------
2017-02-21 07:52:32 2017-02-22 09:56:32 0.414259259259259259
2017-02-21 09:52:32 2017-02-23 17:52:32 1.078148148148148148

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...