Skip to Main Content
  • Questions

  • Calulating hours between two date fields with excluding after working hours

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: January 15, 2013 - 11:52 pm UTC

Last updated: January 23, 2013 - 1:02 am UTC

Version: 10.0

Viewed 1000+ times

You Asked

I have a table with phone nos , complaint booked date,complaint cleared date.

Need to find the time taken to clear the complaint in Hours, excluding the after working hours and holidays.
The working hours is 8:00 A.M to 6:00 P.M. After working hours 6.00 P.M to nextday 8:00 A.M.

Assume 14/01/2013 as holiday.

Sample data
 CREATE TABLE "FAULTS" 
   ( "PHONE_NO" VARCHAR2(20 BYTE), 
 "COMP_BOOKD_DATE" DATE, 
 "COMP_CLEARED_DATE" DATE, 
       ) 


1. INSERT INTO FAULTS (PHONE_NO, COMP_BOOKD_DATE, COMP_CLEARED_DATE) VALUES ('xxx-yyyyyy', TO_DATE('06/01/2013  18:17:52', 'DD/MM/YYYY HH24:MI:SS'), TO_DATE('08/01/2013  16:31:21', 'DD/MM/YYYY HH24:MI:SS'))

2. INSERT INTO FAULTS (PHONE_NO, COMP_BOOKD_DATE, COMP_CLEARED_DATE) VALUES ('xxx-zzzzzz', TO_DATE('13/01/2013  12:30:52', 'DD/MM/YYYY HH24:MI:SS'), TO_DATE('15/01/2013  16:31:21', 'DD/MM/YYYY HH24:MI:SS'))


and Tom said...

NOTE: edited on 17-jan-2013 to correct a few bugs I discovered...


I'll give you the bits and pieces for this, you'll put them together.

Note: I didn't test all of the edge conditions - consider this rough psuedo code, I'm giving you an approach here. You'll have to read it, understand it, verify it does what you need, test it!!! (please)


my approach:

a) figure out the number of FULL business days between the two dates. We'll multiply that by 10 hours to get that number hours.

b) look at comp_bookd_date and figure out if it contributes any hours, if so, compute them

c) same for comp cleared date, if the day is not a weekend/holiday and is on a different day from comp_bookd_date (no ED on bookED - I cannot tell you how many times I typed that wrong :) ) - compute the hours it contributes.

Here is a stab at it:

ops$tkyte%ORA11GR2> create table holidays( dt date );

Table created.

ops$tkyte%ORA11GR2> insert into holidays values (to_date( '25-jan-2013', 'dd-mon-yyyy' ) );

1 row created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> drop table faults;

Table dropped.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE TABLE "FAULTS"
  2     (    "PHONE_NO" VARCHAR2(20 BYTE),
  3      "COMP_BOOKD_DATE" DATE,
  4      "COMP_CLEARED_DATE" DATE
  5         )
  6  /

Table created.

ops$tkyte%ORA11GR2> INSERT INTO FAULTS (PHONE_NO, COMP_BOOKD_DATE, COMP_CLEARED_DATE) VALUES
  2  ('xxx-yyyyyy', TO_DATE('06/01/2013  18:17:52', 'DD/MM/YYYY HH24:MI:SS'),
  3  TO_DATE('08/01/2013  16:31:21', 'DD/MM/YYYY HH24:MI:SS'));

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> INSERT INTO FAULTS (PHONE_NO, COMP_BOOKD_DATE, COMP_CLEARED_DATE) VALUES
  2  ('xxx-zzzzzz', TO_DATE('13/01/2013  12:30:52', 'DD/MM/YYYY HH24:MI:SS'),
  3  TO_DATE('15/01/2013  16:31:21', 'DD/MM/YYYY HH24:MI:SS'));

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO FAULTS (PHONE_NO, COMP_BOOKD_DATE, COMP_CLEARED_DATE) VALUES
  2  ('xxx-xxxx', TO_DATE('23/01/2013  12:30:52', 'DD/MM/YYYY HH24:MI:SS'),
  3  TO_DATE('30/01/2013  16:31:21', 'DD/MM/YYYY HH24:MI:SS'));

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> INSERT INTO FAULTS (PHONE_NO, COMP_BOOKD_DATE, COMP_CLEARED_DATE)
  2     VALUES ('xxx-Ayrayyyy', TO_DATE('07/01/2013  03:17:52', 'DD/MM/YYYY HH24:MI:SS'),
  3                          TO_DATE('07/01/2013  06:31:21', 'DD/MM/YYYY HH24:MI:SS'))
  4  /

1 row created.



I added a new row that hits weekends and a holiday....



ops$tkyte%ORA11GR2> select phone_no, comp_bookd_date, comp_cleared_date,
  2         cnt2*10 + xx+yy tot_hours
  3    from (
  4  select phone_no, comp_bookd_date, comp_cleared_date,
  5         (select count(*)
  6            from dual
  7           where to_char(comp_bookd_date+level,'dy') not in ('sat','sun')
  8           start with trunc(comp_cleared_date) - trunc( comp_bookd_date ) > 1
  9         connect by level <= trunc(comp_cleared_date)-trunc(comp_bookd_date)-1 )
 10         - (select count(*)
 11              from holidays
 12             where dt between trunc(comp_bookd_date)+1 and trunc(comp_cleared_date)-1) cnt2,
 13         case when to_number( to_char( comp_bookd_date, 'hh24mi' ) ) >= 1800
 14              then 0
 15              when to_char( comp_bookd_date, 'dy' ) in ( 'sat', 'sun' ) or exists (select null from holidays where dt = trunc(comp_bookd_date))
 16              then 0
 17              when trunc(comp_bookd_date) = trunc(comp_cleared_date)
 18              then
 19                  case when to_char(comp_cleared_date,'hh24') < '08'
 20                  then 0
 21                  else to_date( to_char( least( 1800, to_number(to_char(comp_cleared_date, 'hh24mi')) ), 'fm0000' ), 'hh24mi' )
 22                       -to_date( to_char( greatest( 0800, to_number(to_char(comp_bookd_date,'hh24mi')) ), 'fm0000' ), 'hh24mi' )
 23                  end
 24              else
 25                  case when to_char(comp_bookd_date,'hh24') >= '18'
 26                  then 0
 27                  else to_date( '1800', 'hh24mi' )
 28                       -to_date( to_char( greatest( 0800, to_number(to_char(comp_bookd_date,'hh24mi')) ), 'fm0000' ), 'hh24mi' )
 29                  end
 30          end *24 xx,
 31         case when trunc(comp_bookd_date) = trunc(comp_cleared_date)
 32              then 0
 33              when to_char( comp_cleared_date, 'dy' ) in ( 'sat', 'sun' ) or exists (select null from holidays where dt = trunc(comp_cleared_date))
 34              then 0
 35              when to_number( to_char( comp_cleared_date, 'hh24mi' ) ) <= 800
 36              then 0
 37              when to_char( comp_cleared_date, 'hh24' ) >= '18'
 38              then 10
 39              else comp_cleared_date - (trunc(comp_cleared_date)+8/24)
 40          end *24 yy
 41    from faults
 42         )
 43  /

PHONE_NO             COMP_BOOKD_DATE          COMP_CLEARED_DATE         TOT_HOURS
-------------------- ------------------------ ------------------------ ----------
xxx-yyyyyy           sun 06-jan-2013 18:17:52 tue 08-jan-2013 16:31:21    18.5225
xxx-zzzzzz           sun 13-jan-2013 12:30:52 tue 15-jan-2013 16:31:21    18.5225
xxx-xxxx             wed 23-jan-2013 12:30:52 wed 30-jan-2013 16:31:21    44.0225
xxx-Ayrayyyy         mon 07-jan-2013 03:17:52 mon 07-jan-2013 06:31:21          0


Ok, so lines 5-9 compute the number of non-weekend days between the day after the bookd date but before the cleared date are not weekends.

Lines 10 through 12 compute the number of holidays in that same period of time.

We subtract the holidays out and you can then multiply that by ten to get the number of hours for that.



then, we look at comp_bookd_date.

If you inserted this after 6pm, we add no extra hours.
If you inserted this on a weekend or holiday, we add no extra hours.
If you booked this and cleared it on the same day - we need to compute the hours in between bookd and cleared after taking bookd up to at least 8am and setting cleared back to at least 6pm. If we cleared this before 8am - we return 0, else we do the math rounding bookd up to 8am and cleared back to 6pm
If you booked and cleared on different days - we just need to figure the hours between at least 8am or booked time, which ever is greatest and 6pm - unless we bookd this after 6pm!


We multiply that by 24 and you get the number of hours to add to the previous computation.


then we look at comp_cleared_date.

if you booked and cleared on the same day, we add no extra hours
if you cleared on a weekend or holiday, we add no extra hours
if you cleared early in the morning, before 8am, no extra hours
if you cleared after 6pm - just return 10
else - return the number of hours since 8am of that day...



Now you need to test it comprehensively - making sure it hits all of the boundary conditions :) I think it does - but I wrote this pretty quick - I might have missed a case...

Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

and please make sure that your nls_date_language is set appropriate when using 'dy' - format !

Sokrates, January 16, 2013 - 3:53 pm UTC

... or better: specify it explicitly

sokrates@11.2 > select value from nls_session_parameters where parameter = 'NLS_DATE_LANGUAGE';

VALUE
----------------------------------------------------------------------------------------------------
AMERICAN

sokrates@11.2 > select to_char(sysdate + 3, 'dy') from dual;

TO_CHAR(SYSD
------------
sat

sokrates@11.2 >
sokrates@11.2 > alter session set nls_date_language = estonian;

Session altered.

sokrates@11.2 > select to_char(sysdate + 3, 'dy') from dual;

TO_C
----
L

sokrates@11.2 > select to_char(sysdate + 3, 'dy', q'|nls_date_language='american'|') from dual;

TO_CHAR(SYSD
------------
sat

sokrates@11.2 > select value from nls_session_parameters where parameter = 'NLS_DATE_LANGUAGE';

VALUE
----------------------------------------------------------------------------------------------------
ESTONIAN

Tom Kyte
January 17, 2013 - 8:28 am UTC

true - I assumed people would understand to use the appropriate characters for sat/sun in their language... one should not assume...

Calulating hours between two date fields with excluding after working hours

A reader, January 17, 2013 - 5:49 am UTC

Thank you for the prompt service. With your logic, separated the time durations as three components and Using a Holidays table able to do the calculation

learning all the time

Greg, January 17, 2013 - 9:38 am UTC

So I read this question, and thought it was a good exercise, so I tried to answer it myself without reading what was already done :)

Needless to say, I got a different method than Tom presented, and just wanted to toss it out for discussion/criticism :)

What I think helps a lot, is in our company, rather than a table of holidays, we store a table of valid business days. By having that holiday/weekend logic essentially already done, it really simplifies that final query. :)

====

Our business day table:
CREATE TABLE business_day
  ( bus_date   NOT NULL   DATE )
/
-- it has other columns, but not necessary for this exercise 
-- it holds only valid business days, no holidays, no weekends. logic done up front, really :)

INSERT INTO business_day VALUES ( '04-jan-2013' );
INSERT INTO business_day VALUES ( '07-jan-2013' );
INSERT INTO business_day VALUES ( '08-jan-2013' );
INSERT INTO business_day VALUES ( '09-jan-2013' );
INSERT INTO business_day VALUES ( '10-jan-2013' );
INSERT INTO business_day VALUES ( '11-jan-2013' );
INSERT INTO business_day VALUES ( '14-jan-2013' );
INSERT INTO business_day VALUES ( '15-jan-2013' );
INSERT INTO business_day VALUES ( '16-jan-2013' );
INSERT INTO business_day VALUES ( '17-jan-2013' );
INSERT INTO business_day VALUES ( '18-jan-2013' );
INSERT INTO business_day VALUES ( '21-jan-2013' );

CREATE TABLE business_hours
   ( start_time   interval day to second,
     end_time     interval day to second
   )
/

INSERT INTO business_hours
   VALUES ( '0 8:00:00', '0 18:00:00' )
/

CREATE TABLE FAULTS
   (  PHONE_NO             VARCHAR2(20), 
      COMP_BOOKD_DATE      DATE, 
      COMP_CLEARED_DATE    DATE
    ) 
/

INSERT INTO FAULTS (PHONE_NO, COMP_BOOKD_DATE, COMP_CLEARED_DATE) 
   VALUES ('xxx-yyyyyy', TO_DATE('06/01/2013  18:17:52', 'DD/MM/YYYY HH24:MI:SS'), 
                        TO_DATE('08/01/2013  16:31:21', 'DD/MM/YYYY HH24:MI:SS'))
/

INSERT INTO FAULTS (PHONE_NO, COMP_BOOKD_DATE, COMP_CLEARED_DATE) 
   VALUES ('xxx-zzzzzz', TO_DATE('13/01/2013  12:30:52', 'DD/MM/YYYY HH24:MI:SS'), 
                        TO_DATE('15/01/2013  16:31:21', 'DD/MM/YYYY HH24:MI:SS'))
/

INSERT INTO FAULTS (PHONE_NO, COMP_BOOKD_DATE, COMP_CLEARED_DATE) 
   VALUES ('xyz-zzzzzz', TO_DATE('15/01/2013  12:30:52', 'DD/MM/YYYY HH24:MI:SS'), 
                         TO_DATE('18/01/2013  16:31:21', 'DD/MM/YYYY HH24:MI:SS'))
/

COMMIT;

-- that's the setup.
Here's the query I end up with that in mind. Again, different from Tom's, as the basic difference is I'm using a list of valid days, he's using a list of holidays. :)

WITH baseline AS
      ( SELECT f.phone_no, f.comp_bookd_date, f.comp_cleared_date,
                bd.bus_date + bh.start_time   bus_date_start,
                bd.bus_date + bh.end_time     bus_date_end
           FROM faults f,
                business_day bd,
                business_hours bh
          WHERE bd.bus_date BETWEEN TRUNC ( f.comp_bookd_date ) 
                                AND TRUNC ( f.comp_cleared_date )
      ),
   actuals AS
      ( SELECT phone_no,
             GREATEST ( comp_bookd_date, bus_date_start ) act_start,
             LEAST ( comp_cleared_date, bus_date_end ) act_end
        FROM baseline
      )
SELECT phone_no,
       NUMTODSINTERVAL ( SUM(act_end - act_start), 'DAY' )  hours
  FROM actuals
 GROUP BY phone_no
/


That's a nice, simpler thought problem (not like some of the really complex ones you get in here sometimes :) ) lol

Tom Kyte
January 17, 2013 - 10:30 am UTC

close, but you did make me find a few bugs in my approach :) I've updated my query (it is more complex now!)

if you insert the four rows I did (and add a few business days - count jan-25 as a holiday) then your query returns:

ops$tkyte%ORA11GR2> WITH baseline AS
  2        ( SELECT f.phone_no, f.comp_bookd_date, f.comp_cleared_date,
  3                  bd.bus_date + bh.start_time   bus_date_start,
  4                  bd.bus_date + bh.end_time     bus_date_end
  5             FROM faults f,
  6                  business_day bd,
  7                  business_hours bh
  8            WHERE bd.bus_date BETWEEN TRUNC ( f.comp_bookd_date )
  9                                  AND TRUNC ( f.comp_cleared_date )
 10        ),
 11     actuals AS
 12        ( SELECT phone_no,
 13               GREATEST ( comp_bookd_date, bus_date_start ) act_start,
 14               LEAST ( comp_cleared_date, bus_date_end ) act_end
 15          FROM baseline
 16        )
 17  SELECT phone_no,
 18         NUMTODSINTERVAL ( SUM(act_end - act_start), 'DAY' )  hours
 19    FROM actuals
 20   GROUP BY phone_no
 21  /

PHONE_NO             HOURS
-------------------- ---------------------------------------------------------------------------
xxx-xxxx             +000000001 20:00:29.000000000
xxx-yyyyyy           +000000000 18:31:21.000000000
xxx-zzzzzz           +000000000 18:31:21.000000000
xxx-Ayrayyyy         -000000000 01:28:39.000000000



the negative bit is off - you have to check for bookd/cleared pairs that happen on the same day outside of the business day.


but thanks to you - I came up with a test case to show your query was not 100% which lead me to discover 4 bugs in my query :)

Learning most of the time O_o

Greg, January 17, 2013 - 9:40 am UTC

Sorry, forgot to post the final output *sigh*

PHONE_NO             HOURS
-------------------- ------------------------------------
xyz-zzzzzz           +000000001 10:00:29.000000000
xxx-yyyyyy           +000000000 18:31:21.000000000
xxx-zzzzzz           +000000000 18:31:21.000000000

3 rows selected.

Calulating hours between two date fields with excluding after working hours

A reader, January 18, 2013 - 12:09 am UTC

Based on the Logic provided by Tom, modified and wrote a function.
If the fault is rectified in the Holiday or in Sunday the hours are counted.
If the complaint is booked in the holiday the duration is excluded.

The duration is calculated in three components.
d_I, d_II, and d_III.

d_I - Time duration in the Start date
d_II Time duration in the End date
d_III Time duration between Start and End date 

create or replace
FUNCTION   "FLT_DURATION_HOURS" (
                                   p_start_date DATE,
                                   p_end_date DATE
                                  )
    RETURN NUMBER
    IS
    d_I number; d_II number; cnt number;d_III number; v_return NUMBER;

BEGIN

-- If fault booked and cleared in Same Day

if trunc(p_start_date)= trunc(p_end_date) then
    v_return:=(p_end_date-p_start_date)*24 ;
  else
  
  -- Else
  
  -- d_I  Time duration in the Start date
  
    select  case  when p_start_date between trunc(p_start_date)+18/24 and trunc(p_start_date)+24/24  then 0 
    when p_start_date between trunc(p_start_date) and trunc(p_start_date)+8/24 then 10
    when   exists (select null from holidays where dt = trunc(p_start_date)) then 0
    else (trunc(p_start_date)+18/24 - p_start_date)*24  end 
    into d_I from dual;
    
    -- d_II Time duration in the End date
       
    select case   when p_end_date between trunc(p_end_date) and trunc(p_end_date)+8/24 then 0   else (p_end_date -(trunc(p_end_date)+8/24))*24 end 
    into d_II from dual;
    
    --d_III Time duration  between Start and End date
    
      select count(*) into cnt  from holidays   where dt between trunc(p_start_date)+1 and trunc(p_end_date)-1 ;
     
     d_III:=((((trunc(p_end_date))-(trunc(p_start_date)))-cnt-1)*10);
      
      v_return:=+d_I+d_II+d_III ;

end if;

v_return:=round(v_return,2);

return v_return;
END;

Please review it.
Tom Kyte
January 21, 2013 - 7:24 am UTC

f trunc(p_start_date)= trunc(p_end_date) then
v_return:=(p_end_date-p_start_date)*24 ;
else


i didn't go further than that, that doesn't work. what if start and end date are 3am and 5am, doesn't work...

Awesome!!

Greg, January 19, 2013 - 12:10 pm UTC

Hey Tom .. cool, thanks! I'll take another look at my query when I get a sec. But yeah, glad we can both help each other :)

Ah ok, simple fix

Greg, January 22, 2013 - 11:49 am UTC 

Yeah, I missed a side case, but easily remedied :)
(I assume when you tweaked your code, you didn't update your results? the xxx-xxxx case you still show as 44 hours, but it should be more like 40 + 5.5 + 8.5 = 54 ?

In any case, another layer of least/greatest on mine seems to solve the problem of using times outside the allowable "business hours" :)

<code>
WITH baseline AS
      ( SELECT f.phone_no, f.comp_bookd_date, f.comp_cleared_date,
                bd.bus_date + bh.start_time   bus_date_start,
                bd.bus_date + bh.end_time     bus_date_end
           FROM faults f,
                business_day bd,
                business_hours bh
          WHERE bd.bus_date BETWEEN TRUNC ( f.comp_bookd_date )
                                AND TRUNC ( f.comp_cleared_date )
      ),
   actuals AS
      ( SELECT phone_no,
             LEAST ( GREATEST ( comp_bookd_date, bus_date_start ), bus_date_end ) act_start,
             GREATEST ( LEAST ( comp_cleared_date, bus_date_end ), bus_date_start ) act_end
        FROM baseline
      )
SELECT phone_no,
       NUMTODSINTERVAL ( SUM(act_end - act_start), 'DAY' )  hours
  FROM actuals
 GROUP BY phone_no
/


Neat question - tricky but simple at the same time :)</code>
Tom Kyte
January 23, 2013 - 1:02 am UTC

I fixed the original answer and the review where I realized I goofed - but didn't look at any of the interior reviews...

Alan Stewart, January 24, 2013 - 12:54 pm UTC

Here is my solution. I save holiday and workday information in tables; this greatly simplifies matters. I think the logic is sound, but perhaps someone has a test case that will break this. 

drop table holiday;
create table holiday
(   holiday_date date primary key
);

insert into holiday (holiday_date) values (to_date ('20130114', 'yyyymmdd'));

drop table workday;
create table workday
(   work_date          date primary key,
    work_open_datetime date,
    work_shut_datetime date
);

insert into workday (work_date, work_open_datetime, work_shut_datetime)
select
    d    as work_date,
    case when to_char (d, 'd') between 2 and 6 -- mon thru fri
         then d + to_dsinterval('0 08:00:00')  -- 8am
    end  as work_open_datetime,
    case when to_char (d, 'd') between 2 and 6 -- mon thru fri
         then d + to_dsinterval('0 18:00:00')  -- 6pm
    end  as work_shut_datetime
from
(   select to_date ('20130101', 'yyyymmdd') + seq_num d
    from
    (   select level - 1 seq_num
        from dual
        connect by level <= 365.25*10 -- ten years
    )
);

update workday
set    work_open_datetime = null,
       work_shut_datetime = null
where  work_date in (select holiday_date from holiday);

create index workday_hours
on workday (work_open_datetime, work_shut_datetime);

select phone_no, comp_bookd_date, comp_cleared_date,
(   select nvl (24 * sum (case
        when work_open_datetime >= comp_bookd_date
        and  work_shut_datetime >= comp_cleared_date
        then comp_cleared_date - work_open_datetime

        when work_open_datetime >= comp_bookd_date
        and  work_shut_datetime <= comp_cleared_date
        then work_shut_datetime - work_open_datetime

        when work_open_datetime <= comp_bookd_date
        and  work_shut_datetime >= comp_cleared_date
        then comp_cleared_date - comp_bookd_date

        when work_open_datetime <= comp_bookd_date
        and  work_shut_datetime <= comp_cleared_date
        then work_shut_datetime - comp_bookd_date

        end), 0)
    from  workday
    where work_shut_datetime > comp_bookd_date
    and   work_open_datetime < comp_cleared_date
) work_hours_elapsed
from faults;

Another possible approach.

Denis Cerkvin, January 30, 2013 - 2:05 am UTC 

/* It is very tempting to ask business people to revisit the rules.
   In fact, this is what I believe needs to happen. 
   They may not realize it, but what they are asking for does not correlate to the expectations 
   of the customer - we all expect our services to be provided on 24x7 basis :-)

   So, this is truly a winning re-defined solution. We will use it for validation later on.
*/

  1* select phone_no, (COMP_CLEARED_DATE - COMP_BOOKD_DATE)*24 from faults
02:27:00 SQL> /

PHONE_NO      (COMP_CLEARED_DATE-COMP_BOOKD_DATE)*24
-------------------- --------------------------------------
xxx-yyyyyy      46.2247222
xxx-zzzzzz      52.0080556
xxx-holiday      140
xxx-holiday2        6

4 rows selected.

Elapsed: 00:00:00.00
02:27:01 SQL> 


/* Now, assuming our business guys are stubborn :-), we can go an extra mile and give them
   a report with more useful information.
*/

02:32:33 SQL> @/tmp/1

PHONE_NO      COMP_BOOKD_DATE COMP_CLEARED_DATE  HOUR_TYPE  HRS
-------------------- ------------------ ------------------ ------------- ----------
xxx-holiday      14-JAN-13 04:00:00 20-JAN-13 00:00:00 AFTER HOURS   71
       14-JAN-13 04:00:00 20-JAN-13 00:00:00 BUSINESS HOUR  50
       14-JAN-13 04:00:00 20-JAN-13 00:00:00 FUN DAY   19


xxx-holiday2      14-JAN-13 04:00:00 14-JAN-13 10:00:00 FUN DAY    6


xxx-yyyyyy      06-JAN-13 18:17:52 08-JAN-13 16:31:21 AFTER HOURS   29
       06-JAN-13 18:17:52 08-JAN-13 16:31:21 BUSINESS HOUR  18


xxx-zzzzzz      13-JAN-13 12:30:52 15-JAN-13 16:31:21 AFTER HOURS   15
       13-JAN-13 12:30:52 15-JAN-13 16:31:21 BUSINESS HOUR  14
       13-JAN-13 12:30:52 15-JAN-13 16:31:21 FUN DAY   24



9 rows selected.

Elapsed: 00:00:00.02
02:34:19 SQL> 



-- This is my data:
02:34:56 SQL> select * from holidays;

HOL_DAY
------------------
14-JAN-13 00:00:00

1 row selected.

Elapsed: 00:00:00.00
02:35:01 SQL> select * from faults;

PHONE_NO      COMP_BOOKD_DATE COMP_CLEARED_DATE
-------------------- ------------------ ------------------
xxx-yyyyyy      06-JAN-13 18:17:52 08-JAN-13 16:31:21
xxx-zzzzzz      13-JAN-13 12:30:52 15-JAN-13 16:31:21
xxx-holiday      14-JAN-13 04:00:00 20-JAN-13 00:00:00
xxx-holiday2      14-JAN-13 04:00:00 14-JAN-13 10:00:00

4 rows selected.

Elapsed: 00:00:00.00
02:35:06 SQL> 




-- And this is my query.

02:35:06 SQL> get /tmp/1
  1  break on phone_no skip 2
  2  WITH next_resolution_hour (PHONE_NO, COMP_BOOKD_DATE, COMP_CLEARED_DATE, CUR_DATE, HOUR_TYPE) AS
  3  (
  4   select PHONE_NO, COMP_BOOKD_DATE, COMP_CLEARED_DATE, COMP_CLEARED_DATE as CUR_DATE,
  5    case -- case starts
  6 -- Current hour falls into Holiday
  7 when trunc(COMP_CLEARED_DATE) in (select h1.hol_day from holidays h1)
  8  then 'FUN DAY'
  9 -- Current hour falls out of business hours
 10 when (COMP_CLEARED_DATE NOT between trunc(COMP_CLEARED_DATE)+1/24*8 and trunc(COMP_CLEARED_DATE)+1/24*17)
 11  then 'AFTER HOURS'
 12 -- This is good legitimate boring working hour
 13 else 'BUSINESS HOUR'
 14    end AS HOUR_TYPE -- case ends
 15   from faults f
 16   where COMP_CLEARED_DATE > COMP_BOOKD_DATE
 17  --and phone_no = 'xxx-holiday' -- DEBUG
 18   UNION ALL -- Recursion rulez!
 19   select f.PHONE_NO, f.COMP_BOOKD_DATE, f.COMP_CLEARED_DATE, n.CUR_DATE-1/24 as CUR_DATE,
 20    case -- case starts
 21 -- Current hour falls into Holiday
 22 when trunc(n.CUR_DATE-1/24) in (select h2.hol_day from holidays h2)
 23  then 'FUN DAY'
 24 -- Current hour falls out of business hours
 25 when (n.CUR_DATE-1/24 NOT between trunc(n.CUR_DATE-1/24)+1/24*8 and trunc(n.CUR_DATE-1/24)+1/24*17)
 26  then 'AFTER HOURS'
 27 -- This is good legitimate boring working hour
 28 else 'BUSINESS HOUR'
 29    end AS HOUR_TYPE -- case ends
 30   from next_resolution_hour n, faults f
 31   where
 32   n.PHONE_NO = f.PHONE_NO and
 33   n.CUR_DATE-1/24 > n.COMP_BOOKD_DATE
 34  --and f.phone_no = 'xxx-holiday' -- DEBUG
 35  ) -- WITH
 36  --SEARCH DEPTH FIRST BY PHONE_NO, COMP_BOOKD_DATE, COMP_CLEARED_DATE, CUR_DATE, HOUR_TYPE SET order1 -- DEBUG
 37  CYCLE CUR_DATE SET is_cycle TO 'Y' DEFAULT 'N'
 38  --SELECT nrh.PHONE_NO, nrh.COMP_BOOKD_DATE, nrh.COMP_CLEARED_DATE, nrh.CUR_DATE, nrh.HOUR_TYPE, is_cycle -- DEBUG
 39  SELECT nrh.PHONE_NO, nrh.COMP_BOOKD_DATE, nrh.COMP_CLEARED_DATE, nrh.HOUR_TYPE, count(nrh.HOUR_TYPE) hrs -- NODEBUG
 40  FROM next_resolution_hour nrh
 41  group by nrh.PHONE_NO, nrh.COMP_BOOKD_DATE, nrh.COMP_CLEARED_DATE, nrh.HOUR_TYPE -- NODEBUG
 42  order by nrh.PHONE_NO, nrh.COMP_BOOKD_DATE, nrh.COMP_CLEARED_DATE, nrh.HOUR_TYPE -- NODEBUG
 43* --order by order1 -- DEBUG
02:36:01  44  
02:36:07 SQL> 

/* All I am doing here is using a recursion to get through each of the hours and allowing the 
   CASE logic to take care of the rest.

   I did not want to think too much about corner cases etc., so here it goes by itself.
   As an extra bonus, I am getting detailed report on each type of hours used, not only business.
   If you enable the debug lines (and disable the non-debug ones :-)) - you could see the raw "hours list".
*/


=================================================
Sincerely,
Denis Cerkvin

The Bible for command line people.
http://www.read-and-think.org/kjv.html
=================================================

My version (specific to business hours given in this case)

Venkat, January 30, 2013 - 11:06 am UTC

select phone_no,
to_char(booked,'dy yyyy-mm-dd hh24:mi:ss') booked,
to_char(cleared,'dy yyyy-mm-dd hh24:mi:ss') cleared,
(
(tcleared - tbooked -- days difference
- ((next_day(tcleared,'sun') - next_day(tbooked,'mon') + 8)*2/7 -- weekend days
- case dbooked when '1' then 1 else 0 end
- case dcleared when '7' then 1 else 2 end)
- (select count(*) from holidays where dt between tbooked and tcleared) -- hols
) * 10
- case when dbooked in ('1','7') or dt = tbooked then 0
else least(greatest(booked-tbooked-1/3,0)*24,10) end -- hrs to bod
+ case when dcleared in ('1','7') or dt = tcleared then 0
else least(greatest(cleared-tcleared-1/3,0)*24,10) end -- hrs to eod
) as tot_hours
from
(select phone_no, comp_bookd_date booked, comp_cleared_date cleared,
trunc(comp_bookd_date) tbooked, trunc(comp_cleared_date) tcleared,
to_char(comp_bookd_date,'d') dbooked,
to_char(comp_cleared_date,'d') dcleared,
holidays.dt
from faults
left outer join holidays
on holidays.dt in (trunc(faults.comp_bookd_date), trunc(faults.comp_cleared_date))
)
;

My previous post

Venkat, January 30, 2013 - 11:14 am UTC

will not work with both the dates being holidays-
Revised code-

select phone_no,
to_char(booked,'dy yyyy-mm-dd hh24:mi:ss') booked,
to_char(cleared,'dy yyyy-mm-dd hh24:mi:ss') cleared,
(
(tcleared - tbooked -- days difference
- ((next_day(tcleared,'sun') - next_day(tbooked,'mon') + 8)*2/7 -- weekend days
- case dbooked when '1' then 1 else 0 end
- case dcleared when '7' then 1 else 2 end)
- (select count(*) from holidays where dt between tbooked and tcleared) -- hols
) * 10
- case when dbooked in ('1','7') or dt1 = tbooked then 0
else least(greatest(booked-tbooked-1/3,0)*24,10) end -- hrs to bod
+ case when dcleared in ('1','7') or dt2 = tcleared then 0
else least(greatest(cleared-tcleared-1/3,0)*24,10) end -- hrs to eod
) as tot_hours
from
(select phone_no, comp_bookd_date booked, comp_cleared_date cleared,
trunc(comp_bookd_date) tbooked, trunc(comp_cleared_date) tcleared,
to_char(comp_bookd_date,'d') dbooked,
to_char(comp_cleared_date,'d') dcleared,
h1.dt dt1, h2.dt dt2
from faults
left outer join holidays h1
on h1.dt = trunc(faults.comp_bookd_date)
left outer join holidays h2
on h2.dt = trunc(faults.comp_cleared_date)
)
;
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.