Calculating Average Processing Time in Hours Excluding Weekends

Introduction

The main purpose of this tip is to address a business question of calculating average processing time in hours for the raised issues during the year 2014 by excluding weekends. There are various ways to do it, but in this work Oracle PL/SQL cursors are used. Through this post, industry experience is being shared.

Background

The following are the details of the business question.

Calculate the average processing time of issues in working hours assuming the following:

1. Given information is the issue number, submission date with time and resolution date with time.
2. Working hours start from 8:00 AM and ends by 4:00 PM.
3. Fridays and Saturdays are considered as weekends, so should be excluded in the calculation.
4. If the issue is created after 4:00 PM on any day then it is treated as created on the next day 8:00 AM.
5. If the issue is solved after 4:00 PM on any day then is treated as solved on the next day by 8:00 AM.
6. If the issue is created before 8:00 AM on any day then it is treated as created on the same day 8:00 AM.
7. If the issue is solved before 8:00 AM on any day then it is treated as solved on the same day 8:00 AM.
8. If the issue is created above 30’th minute of an hour then it is rounded to next hour. Example: 10:55 AM is rounded to 11:00 AM.
9. If the issue is created below 30’th minute of an hour then it is rounded to previous hour. Example: 10:12 AM is rounded to 10:00 AM.
10. If the issue is solved above 30’th minute of an hour then it is rounded to next hour. Example: 10:55 AM is rounded to 11:00 AM.
11. If the issue is solved below 30’th minute of an hour then it is rounded to previous hour. Example: 10:12 AM is rounded to 10:00 AM.

Using the Code

Creating the table:

create table x_tt
(
  id number,
  submitted_date date,
  last_updated date,
  sub_day_number number,
  sub_month_number number,
  sub_year_number number,
  sub_hour_number number,
  sub_minute_number number,
  lu_day_number number,
  lu_month_number number,
  lu_year_number number,
  lu_hour_number number,
  lu_minute_number number,
  Number_of_days number,
  Number_of_hours number
)

Load the data into the columns ID, SUBMITTED_DATE, LAST_UPDATED.

Segregate the day, month, year, hour and minute values from the given dates and update the above columns by below query.

update x_tt set
sub_day_number=to_char(submitted_date,'dd'),
sub_month_number=to_char(submitted_date,'mm'),
sub_year_number=to_char(submitted_date,'yyyy'),
sub_hour_number=to_char(submitted_date,'hh24'),
sub_minute_number=to_char(submitted_date,'mi'),
lu_day_number=to_char(last_updated,'dd'),
lu_month_number=to_char(last_updated,'mm'),
lu_year_number=to_char(last_updated,'yyyy'),
lu_hour_number=to_char(last_updated,'hh24'),
lu_minute_number=to_char(last_updated,'mi')

If the issue is created after 4:00 PM on any day, then it is treated as created on the next day 8:00 AM, updating the table with the below statement.

update x_tt set
sub_day_number=to_char(submitted_date+1,'dd'),
sub_month_number=to_char(submitted_date+1,'mm'),
sub_year_number=to_char(submitted_date+1,'yyyy'),
sub_hour_number=8,
sub_minute_number=0
where
sub_hour_number>=16

If the issue is solved after 4:00 PM on any day, then is treated as solved on the next day by 8:00 AM, updating the table with the below statement.

update x_tt
set
lu_day_number=to_char(last_updated+1,'dd'),
lu_month_number=to_char(last_updated+1,'mm'),
lu_year_number=to_char(last_updated+1,'yyyy'),
lu_hour_number=8,
lu_minute_number=0
where
lu_hour_number>=16
and
lu_minute_number<>0

If the issue is created before 8:00 AM on any day, then it is treated as created on the same day 8:00 AM, updating the table with the below statement.

update x_tt
set
sub_hour_number=8,
sub_minute_number=0
where
sub_hour_number <8

If the issue is solved before 8:00 AM on any day, then it is treated as solved on the same day 8:00 AM, updating the table with the below statement.

update x_tt set
lu_hour_number=8,
lu_minute_number=0
where
lu_hour_number<8

If the issue is created above 30’th minute of an hour, then it is rounded to next hour. Example: 10:55 AM is rounded to 11:00 AM, updating the table with the below statement.

update x_tt set
sub_minute_number=0,
sub_hour_number = sub_hour_number+1
where
sub_minute_number>30
and
sub_hour_number<>23

If the issue is created below 30’th minute of an hour, then it is rounded to previous hour. Example: 10:12 AM is rounded to 10:00 AM, updating the table with the below statement.

update x_tt
set
sub_minute_number=0
where sub_minute_number<=30 and sub_minute_number>0

If the issue is solved above 30’th minute of an hour, then it is rounded to next hour. Example: 10:55 AM is rounded to 11:00 AM.

update x_tt
set
lu_minute_number=0,
lu_hour_number=lu_hour_number+1
where
lu_minute_number<>0
and
lu_minute_number>=30

If the issue is solved below 30’th minute of an hour, then it is rounded to previous hour. Example: 10:12 AM is rounded to 10:00 AM.

update x_tt
set
lu_minute_number=0
where
lu_minute_number<>0
and
lu_minute_number<30

Now excluding the weekends and calculating the actual number of working days by PL/SQL cursor.

DECLARE
   NOD x_tt.id%type;
   var_submitted_date x_tt.submitted_date%type;
   var_last_updated x_tt.last_updated%type;

   CURSOR cur1
   IS
      SELECT * FROM  x_tt;
BEGIN

   FOR cur1_rec
   IN cur1
   LOOP
   -- If the dates of next to submitted_date and last_updated are not equal
   if (to_char(trunc(cur1_rec.submitted_date+1),'mm/dd/yyyy')<>
   to_char(trunc(cur1_rec.last_updated),'mm/dd/yyyy')) then

    var_submitted_date:=trunc(cur1_rec.submitted_date+1);
    var_last_updated:=trunc(cur1_rec.last_updated);
    NOD:=0;

    WHILE var_submitted_date < var_last_updated

    LOOP
        -- Excluding the weekends and counting in the variable NOD
        if (trim(to_char(var_submitted_date, 'DAY')) not in ('FRIDAY','SATURDAY')) then
           NOD:=NOD+1;
        end if;
        -- Incrementing the counter
        var_submitted_date:=var_submitted_date + 1;
    END LOOP;

   update x_tt set Number_of_days=NOD where id=cur1_rec.id;
   else -- If the dates of next to submitted date and last_updated are equal
   update x_tt15_backup set number_of_days=0 where id=cur1_rec.id;
   end if;

   END LOOP;
END;

Adding the difference between creation and resolution hours on the same day:

update x_tt set
number_of_hours=lu_hour_number-sub_hour_number
where
to_date(sub_month_number||'/'||sub_day_number||'/'||sub_year_number,'mm/dd/yyyy')=
to_date(lu_month_number||'/'||lu_day_number||'/'||lu_year_number,'mm/dd/yyyy')

Adding the submitted date residue hours and closure date residue hours on different days:

update x_tt set
number_of_hours=16-sub_hour_number+lu_hour_number-8
where
to_date(sub_month_number||'/'||sub_day_number||'/'||sub_year_number,'mm/dd/yyyy')<>
to_date(lu_month_number||'/'||lu_day_number||'/'||lu_year_number,'mm/dd/yyyy')

Getting the average processing time by the query finally.

select avg(number_of_days*8+number_of_hours) from x_tt;

Conclusion

Though this article did not use the latest features of Oracle, it basically aims to show how such calculations are useful in the industry to address the business questions. This work has given to the author a greater insight/experience as a job assignment for calculating KPI for SLA evaluation. The author is working under PMO office, Saudi Telecom Company. He was a Business Analyst before. His manager name is Khalid Al Afifi and director name is Majid Algarni.