Skip to Main Content
  • Questions

  • Calculating hours worked between two dates

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Chris.

Asked: December 25, 2003 - 9:05 pm UTC

Last updated: February 16, 2009 - 10:34 am UTC

Version: 9.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I am trying to calculate the hours a person may work between two dates.
For example we have an employee that starts work at 8.00 and ends work at 17:00. He works Monday to Friday.
He may start to do a job at 11:40 on Thursday and finishes at 14:00 the following Monday. I want to be able to calculate the hours he spent doing that particular job.

What's the best way of working it out?


and Tom said...

well, here is a SQL and a PLSQL based approach -- test them each out and see which is "best" for you.

I'm using table T as a "parameter table" here -- you could of course just use binds in the query instead of joining to T.

ops$tkyte@ORA9IR2> create table t ( stime date, etime date, clockin number, clockout number );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( to_date( '18-dec-2003 11:40', 'dd-mon-yyyy hh24:mi'),
2 to_date( '22-dec-2003 14:00', 'dd-mon-yyyy hh24:mi'),
3 8, 17 );

1 row created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @bigdate
ops$tkyte@ORA9IR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select r,
2 greatest( stime, trunc(stime)+clockin/24+r ),
3 least( trunc(stime)+r+clockout/24, etime ),
4 (least( trunc(stime)+r+clockout/24, etime )-greatest( stime, trunc(stime)+clockin/24+r ))*24 elap
5 from t,
6 (select rownum-1 r
7 from all_objects
8 where rownum <= (select trunc(etime)-trunc(stime)+1
9 from t) )
10 where to_char(stime+r,'Dy') not in ( 'Sat', 'Sun' )
11 /

R GREATEST(STIME,TRUNC LEAST(TRUNC(STIME)+R ELAP
---------- -------------------- -------------------- ----------
0 18-dec-2003 11:40:00 18-dec-2003 17:00:00 5.33333333
1 19-dec-2003 08:00:00 19-dec-2003 17:00:00 9
4 22-dec-2003 08:00:00 22-dec-2003 14:00:00 6

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select sum(least( trunc(stime)+r+clockout/24, etime )-greatest( stime, trunc(stime)+clockin/24+r )) * 24
2 from t,
3 (select rownum-1 r
4 from all_objects
5 where rownum <= (select trunc(etime)-trunc(stime)+1
6 from t) )
7 where to_char(stime+r,'Dy') not in ( 'Sat', 'Sun' )
8 /

SUM(LEAST(TRUNC(STIME)+R+CLOCKOUT/24,ETIME)-GREATEST(STIME,TRUNC(STIME)+CLOCKIN/24+R))*24
-----------------------------------------------------------------------------------------
20.3333333

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace
2 function hours_worked( p_stime in date, p_etime in date, p_clockin number, p_clockout number, p_ignore_days in varchar2 )
3 return number
4 as
5 l_ignore_days long := '/'||upper(p_ignore_days)||'/';
6 l_tot number := 0;
7 begin
8 for i in 0 .. trunc(p_etime)-trunc(p_stime)
9 loop
10 if ( instr( l_ignore_days, '/'||to_char(p_stime+i,'DY')||'/' ) = 0 )
11 then
12 l_tot := l_tot + least( trunc(p_stime)+i+p_clockout/24, p_etime )-greatest( p_stime, trunc(p_stime)+p_clockin/24+i );
13 end if;
14 end loop;
15 return l_tot*24;
16 end;
17 /

Function created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select hours_worked( stime, etime, clockin, clockout, 'Sat/Sun' )
2 from t;

HOURS_WORKED(STIME,ETIME,CLOCKIN,CLOCKOUT,'SAT/SUN')
----------------------------------------------------
20.3333333


Rating

  (19 ratings)

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

Comments

Great solution

Chris, December 26, 2003 - 4:59 pm UTC

Hi Tom,
I love the SQL solution, thanks a lot.


Great!!

Sanjay Raj, December 27, 2003 - 6:43 am UTC

The SQL solution is great!!

Question

venkatesh, September 21, 2004 - 7:14 pm UTC

What if I have more rows in t. How do I do this using SQL approach.

Like
insert into t(stime,etime,clockin,clockout) values
( to_date( '18-dec-2003 11:40', 'dd-mon-yyyy hh24:mi'),
to_date( '22-dec-2003 14:00', 'dd-mon-yyyy hh24:mi'),
8, 17 );

insert into t(stime,etime,clockin,clockout) values
( to_date( '28-dec-2003 11:40', 'dd-mon-yyyy hh24:mi'),
to_date( '29-dec-2003 14:00', 'dd-mon-yyyy hh24:mi'),
8, 17 );

How do I get the same report.



Tom Kyte
September 21, 2004 - 7:31 pm UTC

er?

select hours_worked( stime, etime, clockin, clockout,
'Sat/Sun' ) from t;


will give you the "same" report (a report of the hours worked for each row in the table)

if you want a grand total

select SUM( .... ) from t;



Follow up

venkatesh, September 21, 2004 - 9:04 pm UTC

I am looking for this report and not the hours worked calue for multiple rows in t . I cannot think of a function which will return this..


R GREATEST(STIME,TRUNC LEAST(TRUNC(STIME)+R ELAP
---------- -------------------- -------------------- ----------
0 18-dec-2003 11:40:00 18-dec-2003 17:00:00 5.33333333
1 19-dec-2003 08:00:00 19-dec-2003 17:00:00 9
4 22-dec-2003 08:00:00 22-dec-2003 14:00:00 6
In the case of multiple rows in t , following subquery will fail..

(select rownum-1 r
from all_objects
where rownum <= (select trunc(etime)-trunc(stime)+1
from t ) )
where to_char(stime+r,'Dy') not in ( 'Sat', 'Sun' ) ;






Tom Kyte
September 22, 2004 - 7:25 am UTC

select max(trunc(.....) )

just apply logic and modify the query -- understand the component pieces and what they do.

or just use the function

A reader, September 22, 2004 - 5:31 am UTC


Variable number of arguments

A reader, October 28, 2004 - 8:57 pm UTC

Some Oracle built-in functions like greatest(), least(), decode(), etc take a variable number of arguments.

How can I do this for my own user-defined functions?

Thanks

Tom Kyte
October 28, 2004 - 10:10 pm UTC 

ops$tkyte@ORA9IR2> create or replace type var_args as table of varchar2(4000)
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure foo( x in var_args )
  2  as
  3  begin
  4          dbms_output.put_line( 'you gave me these many inputs: ' || x.count );
  5          null;
  6  end;
  7  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec foo( var_args( 1, 2, 3, 4 ) );
you gave me these many inputs: 4
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec foo( var_args( 1 ) );
you gave me these many inputs: 1
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec foo( var_args( 1, 2 ) );
you gave me these many inputs: 2
 
PL/SQL procedure successfully completed.

Question about subquery

Carlton Gregory, October 29, 2004 - 10:15 am UTC

I dont understand what this:

(select rownum-1 r
7 from all_objects
8 where rownum <= (select trunc(etime)-trunc(stime)+1
9 from t) )

gave you. Why select from all_objects?

Tom Kyte
October 29, 2004 - 10:46 am UTC

used all objects cause it generally has lots of rows. we needed lots of rows is all.

I will try again.

Venkatesh, April 28, 2005 - 7:28 pm UTC

Sorry if I was not clear earlier with my question. I decided to proceed with a different implementation but I still want to know this..

We have a table with these columns and rows.

Column attributes
start_date DATE;
end_date DATE;

rows
start_date end_date
01-apr-2005 10:00:00 03-apr-2005 21:00:09
03-apr-2005 21:00:09 07-apr-2005 20:00:00
07-apr-2005 20:00:00 07-apr-2005 21:00:00
08-apr-2005 08:00:00 08-apr-2005 22:00:00

Note the following...

1) end_date of prev record need not be equal to start_date of the present record. They can be same but not necessary.

For this table I need to a SQL query to split all the records into working hrs alone..
working hours are 8.00 - 20:00
with something like this..

start_date end_date Status
01-apr-2005 10:00:00 01-apr-2005 20:00 W
01-apr-2005 20:00:00 02-apr-2005 10:00:00 NW
02-apr-2005 10:00:00 02-apr-2005 20:00:00 W
02-apr-2005 20:00:00 03-apr-2005 10:00:00 NW
03-apr-2005 10:00:00 03-apr-2005 20:00:00 W
03-apr-2005 20:00:00 03-apr-2005 21:00:09 NW
03-apr-2005 21:00:09 04-apr-2005 10:00:00 NW
04-apr-2005 10:00:00 04-apr-2005 20:00:00 W
--
--



Thanks
venkatesh

Tom Kyte
April 28, 2005 - 7:37 pm UTC

no create table
no insert into's
no attempts by me :)

With instructions..

venkatesh, April 28, 2005 - 7:49 pm UTC


CREATE TABLE t1(
start_date DATE,
end_date DATE
);


insert into t1 (start_date,end_date)
VALUES
(to_date('01-apr-2005 10:00:00','dd-mon-yyyy hh24:mi:ss'), to_date('03-apr-2005 21:00:09','dd-mon-yyyy hh24:mi:ss'));


insert into t1 (start_date,end_date)
VALUES
(to_date('03-apr-2005 21:00:09','dd-mon-yyyy hh24:mi:ss'), to_date('07-apr-2005 20:00:00','dd-mon-yyyy hh24:mi:ss'));

insert into t1 (start_date,end_date)
VALUES
(to_date('07-apr-2005 20:00:00','dd-mon-yyyy hh24:mi:ss'), to_date('07-apr-2005 21:00:00','dd-mon-yyyy hh24:mi:ss'));

insert into t1 (start_date,end_date)
VALUES
(to_date('08-apr-2005 08:00:00','dd-mon-yyyy hh24:mi:ss'), to_date('08-apr-2005 22:00:00','dd-mon-yyyy hh24:mi:ss'));

commit;

We have a table with these columns and rows.

Column attributes
start_date DATE;
end_date DATE;

rows
start_date end_date
01-apr-2005 10:00:00 03-apr-2005 21:00:09
03-apr-2005 21:00:09 07-apr-2005 20:00:00
07-apr-2005 20:00:00 07-apr-2005 21:00:00
08-apr-2005 08:00:00 08-apr-2005 22:00:00

Note the following...

1) end_date of prev record need not be equal to start_date of the present
record. They can be same but not necessary.

For this table I need to a SQL query to split all the records into working hrs
alone..
working hours are 8.00 - 20:00
with something like this..

start_date end_date Status
01-apr-2005 10:00:00 01-apr-2005 20:00 W
01-apr-2005 20:00:00 02-apr-2005 10:00:00 NW
02-apr-2005 10:00:00 02-apr-2005 20:00:00 W
02-apr-2005 20:00:00 03-apr-2005 10:00:00 NW
03-apr-2005 10:00:00 03-apr-2005 20:00:00 W
03-apr-2005 20:00:00 03-apr-2005 21:00:09 NW
03-apr-2005 21:00:09 04-apr-2005 10:00:00 NW
04-apr-2005 10:00:00 04-apr-2005 20:00:00 W
--
--

Tom Kyte
April 28, 2005 - 8:19 pm UTC

so the working/notworking decision is made ont he start_date? look at row 2, you don't need two rows for that?

Sum up the days, hrs, minutes and secs

Keith Jamieson, April 29, 2005 - 9:04 am UTC

This gets the total days, hrs, minutes and seconds.
number of days = (end date - start date)
Hours = (end_date - start date)*24.
To find the number of hours less than 24, we simply mod them

so mod((end_date - start_date)*24,24) is the remaining hours.
We are not interested in the fractional part, so a
trunc( mod((end_date - start_date)*24,24)) will give us the number of whole hours left.

eg 49 hours = 2 days 1 hour so the above will give us 1 hour.






select start_date,end_date,
--(end_date - start_date) *24 total_hours,
trunc(end_date - start_date) days,
trunc(mod((end_date - start_date) *24,24)) hrs,
trunc(mod((end_date - start_date) * 24 * 60,60)) minutes,
trunc(mod((end_date - start_date) * 24 * 60 * 60,60)) seconds
from t1

and below produces a total sum

This time we need to find out how many whole minutes, hours, seconds, etc have been rounded up. So
to find the number of minutes to be added as the result of seconds rolling over to 60 and above, we get the total seconds (60) and divided by 60 which = 1 minute.
When we do the hours we have to rember to include the seconds to minutes as well, etc.


select min(start_date),
max(end_date),
sum(days) +
trunc((sum(hrs)+trunc(sum(minutes)/60) + trunc((sum(seconds)/60)))/24) days,
mod(sum(hrs)+trunc(sum(minutes)/60) + trunc((sum(seconds)/60)),24) hrs,
mod(sum(minutes) +trunc((sum(seconds)/60)),60) total_minutes,
mod(sum(seconds),60) total_seconds
from
(
select start_date,end_date,
--(end_date - start_date) *24 total_hours,
trunc(end_date - start_date) days,
trunc(mod((end_date - start_date) *24,24)) hrs,
trunc(mod((end_date - start_date) * 24 * 60,60)) minutes,
trunc(mod((end_date - start_date) * 24 * 60 * 60,60)) seconds
from t1
)

Clarify..

venkatesh, April 29, 2005 - 1:36 pm UTC

I quoting your response again here..



CREATE TABLE t1(
start_date DATE,
end_date DATE
);


insert into t1 (start_date,end_date)
VALUES
(to_date('01-apr-2005 10:00:00','dd-mon-yyyy hh24:mi:ss'),
to_date('03-apr-2005 21:00:09','dd-mon-yyyy hh24:mi:ss'));


insert into t1 (start_date,end_date)
VALUES
(to_date('03-apr-2005 21:00:09','dd-mon-yyyy hh24:mi:ss'),
to_date('07-apr-2005 20:00:00','dd-mon-yyyy hh24:mi:ss'));

insert into t1 (start_date,end_date)
VALUES
(to_date('07-apr-2005 20:00:00','dd-mon-yyyy hh24:mi:ss'),
to_date('07-apr-2005 21:00:00','dd-mon-yyyy hh24:mi:ss'));

insert into t1 (start_date,end_date)
VALUES
(to_date('08-apr-2005 08:00:00','dd-mon-yyyy hh24:mi:ss'),
to_date('08-apr-2005 22:00:00','dd-mon-yyyy hh24:mi:ss'));

commit;

We have a table with these columns and rows.

Column attributes
start_date DATE;
end_date DATE;

rows
start_date end_date
01-apr-2005 10:00:00 03-apr-2005 21:00:09
03-apr-2005 21:00:09 07-apr-2005 20:00:00
07-apr-2005 20:00:00 07-apr-2005 21:00:00
08-apr-2005 08:00:00 08-apr-2005 22:00:00

Note the following...

1) end_date of prev record need not be equal to start_date of the present
record. They can be same but not necessary.

For this table I need to a SQL query to split all the records into working hrs
alone..
working hours are 8.00 - 20:00
with something like this..

start_date end_date Status
01-apr-2005 10:00:00 01-apr-2005 20:00 W
01-apr-2005 20:00:00 02-apr-2005 10:00:00 NW
02-apr-2005 10:00:00 02-apr-2005 20:00:00 W
02-apr-2005 20:00:00 03-apr-2005 10:00:00 NW
03-apr-2005 10:00:00 03-apr-2005 20:00:00 W
03-apr-2005 20:00:00 03-apr-2005 21:00:09 NW
03-apr-2005 21:00:09 04-apr-2005 10:00:00 NW
04-apr-2005 10:00:00 04-apr-2005 20:00:00 W
--
--


Followup:

so the working/notworking decision is made ont he start_date? look at row 2,
you don't need two rows for that?

-------------
My response:
I am not sure if I understood you here.. But let me explain this again..

Table t1 has 2 columns
start_date and end_date . The end_date of row(n) can be equal to the start_date of row (n+1) but it need not be all the time. The start_date and end_date of any row in t1 can span multiple days. The working hours are between 8.00 to 20.00.
The aim is to split the records in t1 into working and non-working hours through a SQL query..

I hope I am clear here.


Tom Kyte
April 29, 2005 - 2:00 pm UTC

01-apr-2005 20:00:00 02-apr-2005 10:00:00 NW

that row, it spans a "W" and "NW" period.

don't you need two rows for that? you have 2 hours of W in there.

Sorry my mistake.

venkatesh, April 29, 2005 - 2:05 pm UTC

Sorry my mistake.. please assume Working hours are between 10:00 to 20:00 and not 08:00 to 20:00

Tom Kyte
April 29, 2005 - 6:33 pm UTC 

ops$tkyte@ORA10GR1> with
  2  range
  3  as
  4  (
  5  select trunc(min(start_date)) min_start, trunc(max(start_date)) max_start
  6    from t1
  7  ),
  8  nums
  9  as
 10  (select trunc(min_start)+ceil(level/3)-1 + decode(mod(level,3),0,-4/24,1,10/24,20/24) dt1,
 11          trunc(min_start)+ceil(level/3)-1 + decode(mod(level,3),0,10/24,1,20/24,1+10/24) dt2,
 12                  decode(mod(level,3),0,'NW',1,'W','NW') what,
 13                  level l
 14     from range
 15   connect by level <= (max_start-min_start+1)*3
 16  )
 17  select greatest(dt1, min(start_date)), least( dt2, max(end_date)),  what
 18    from nums, t1
 19   where t1.start_date < dt2 and t1.end_date >= dt1
 20   group by dt1, dt2, what
 21   order by 1
 22  /
 
GREATEST(DT1,MIN(STA LEAST(DT2,MAX(END_DA WHAT
-------------------- -------------------- ------------------------------
01-apr-2005 10:00:00 01-apr-2005 20:00:00 W
01-apr-2005 20:00:00 02-apr-2005 10:00:00 NW
02-apr-2005 10:00:00 02-apr-2005 20:00:00 W
02-apr-2005 20:00:00 03-apr-2005 10:00:00 NW
03-apr-2005 10:00:00 03-apr-2005 20:00:00 W
03-apr-2005 20:00:00 04-apr-2005 10:00:00 NW
04-apr-2005 10:00:00 04-apr-2005 20:00:00 W
04-apr-2005 20:00:00 05-apr-2005 10:00:00 NW
05-apr-2005 10:00:00 05-apr-2005 20:00:00 W
05-apr-2005 20:00:00 06-apr-2005 10:00:00 NW
06-apr-2005 10:00:00 06-apr-2005 20:00:00 W
06-apr-2005 20:00:00 07-apr-2005 10:00:00 NW
07-apr-2005 10:00:00 07-apr-2005 20:00:00 W
07-apr-2005 20:00:00 08-apr-2005 10:00:00 NW
08-apr-2005 10:00:00 08-apr-2005 20:00:00 W
08-apr-2005 20:00:00 08-apr-2005 22:00:00 NW
 
16 rows selected.


Not exactly what you had, but I don't understand why:


03-apr-2005 20:00:00          03-apr-2005 21:00:09       NW
03-apr-2005 21:00:09          04-apr-2005 10:00:00       NW 

two rows, but it covers the range

RE: calculating W/NW hours

Vinayak, April 29, 2005 - 7:53 pm UTC 

To Venkatesh:

do you want something like this:

SQL> select * from t1 ;

START_DT             END_DT
-------------------- --------------------
01-APR-2005 10:00:00 03-APR-2005 21:00:09
03-APR-2005 21:00:09 07-APR-2005 20:00:00
07-APR-2005 20:00:00 07-APR-2005 21:00:00
08-APR-2005 08:00:00 08-APR-2005 22:00:00

SQL> get b
  1  select greatest(t1.start_dt,z.work_st),least(t1.end_dt,z.work_end),z.status
  2  from t1,
  3  (select decode(status,1,start_dt+r+10/24,start_dt+r+20/24) work_st,
  4         decode(status,1,start_dt+r+20/24,start_dt+r+1+10/24) work_end,
  5         decode(status,1,'W','NW') status from
  6  (select start_dt,rownum-1 r from (select * from dual connect by dummy=dummy) ,
  7  (select trunc(min(start_dt)) start_dt from t1)
  8  where rownum<(select 1+max(end_dt)-min(start_dt) from t1)), (select 1 status from dual union select 2 from dual)
  9  ) z
 10  where (z.work_st>=t1.start_dt AND z.work_end<=t1.end_dt)
 11   OR   (z.work_st>=t1.start_dt and z.work_st<t1.end_dt)
 12   OR   (z.work_end>t1.start_dt and z.work_end<t1.end_dt)
 13* order by z.work_st
SQL> @b

GREATEST(T1.START_DT LEAST(T1.END_DT,Z.WO ST
-------------------- -------------------- --
01-APR-2005 10:00:00 01-APR-2005 20:00:00 W
01-APR-2005 20:00:00 02-APR-2005 10:00:00 NW
02-APR-2005 10:00:00 02-APR-2005 20:00:00 W
02-APR-2005 20:00:00 03-APR-2005 10:00:00 NW
03-APR-2005 10:00:00 03-APR-2005 20:00:00 W
03-APR-2005 20:00:00 03-APR-2005 21:00:09 NW
03-APR-2005 21:00:09 04-APR-2005 10:00:00 NW
04-APR-2005 10:00:00 04-APR-2005 20:00:00 W
04-APR-2005 20:00:00 05-APR-2005 10:00:00 NW
05-APR-2005 10:00:00 05-APR-2005 20:00:00 W
05-APR-2005 20:00:00 06-APR-2005 10:00:00 NW
06-APR-2005 10:00:00 06-APR-2005 20:00:00 W
06-APR-2005 20:00:00 07-APR-2005 10:00:00 NW
07-APR-2005 10:00:00 07-APR-2005 20:00:00 W
07-APR-2005 20:00:00 07-APR-2005 21:00:00 NW
08-APR-2005 08:00:00 08-APR-2005 10:00:00 NW
08-APR-2005 10:00:00 08-APR-2005 20:00:00 W
08-APR-2005 20:00:00 08-APR-2005 22:00:00 NW

18 rows selected.

Case not handled in function?

Greg, June 28, 2005 - 2:23 pm UTC

Hi Tom,

I have been playing with the hours_worked function and am using it to compute the number of business hours contained within an interval. I'm not sure if you intended to handle this case, but I find that when p_stime is greater than p_clockout the return value is incorrect. For example, this returns 4.5 when I am expecting 8.5:

-- !!!!!!Case 18: Time spans 2 bus day. Start after bus hours day 1 finish after bus hours day 2.
-- Should return 8 hour 30 mins (8.5).
select hours_worked(to_date('June 28, 2005, 9:00 P.M.', 'Month dd, YYYY, HH:MI A.M.'), to_date('June 29, 2005, 9:00 P.M.', 'Month dd, YYYY, HH:MI A.M.'), 8.5, 17, 'Sat/Sun' )
from dual;


I think this slight modificatin takes care of it:

FUNCTION hours_worked( p_stime in date, p_etime in date, p_clockin number,
p_clockout number, p_ignore_days in varchar2 )
RETURN number
AS
l_ignore_days long := '/'||upper(p_ignore_days)||'/';
sub_tot number := 0;
l_tot number := 0;
BEGIN
for i in 0 .. trunc(p_etime)-trunc(p_stime)
loop
if ( instr( l_ignore_days, '/'||to_char(p_stime+i,'DY')||'/' ) = 0 )
then
sub_tot := least( trunc(p_stime)+i+p_clockout/24, p_etime)-greatest( p_stime, trunc(p_stime)+p_clockin/24+i );
if sub_tot > 0 then
l_tot := l_tot + sub_tot;
end if;
end if;
end loop;
return l_tot*24;
END;



Thanks for all your help!!!
Greg


Calculation working hours

ARC, February 12, 2009 - 8:49 am UTC

Hi Tom,
The SQL is most useful in calculating working hours. I am using the query for my requirement, it is not helping as the table as multiple rows.
ACTIVITY_ID END_TIME START_TIME
1-1SUOH5 12/23/2008 12/5/2008
1-1STXT3 12/8/2008 12/5/2008
1-1STH4J 12/6/2008 12/5/2008
1-1SUASN 12/19/2008 12/5/2008
1-1SUCEH 12/9/2008 12/5/2008
1-1SU5PX 12/6/2008 12/5/2008
1-1STK6S 12/5/2008 12/5/2008
1-1SUQ3W 12/8/2008 12/5/2008
1-1SUHAT 12/6/2008 12/5/2008
1-1SUQ3M 12/9/2008 12/5/2008

I am trying to achive the working hours for all the activites in a single query.
SQL query for one activity:
select activity_id, reqst_start_date,resp_date_time, r,
greatest( reqst_start_date, trunc(reqst_start_date)+8/24+r ),
least( trunc(reqst_start_date)+r+17/24, resp_date_time ),
(least( trunc(reqst_start_date)+r+17/24, resp_date_time )-greatest( reqst_start_date, trunc(reqst_start_date)+8/24+r ))*24 elap
from (select activity_id, resp_date_time, reqst_start_date from cu.cu_iw_activity where activity_type = 'Research' and activity_id = '1-1SUOH5') t,
(
select rownum-1 r from all_objects
where rownum <= (select trunc(resp_date_time) - trunc(reqst_start_date)+1 from cu.cu_iw_activity where activity_type = 'Research' and activity_id = '1-1SUOH5')
) t2
where to_char(reqst_start_date+r,'Dy') not in ( 'Sat', 'Sun' );

Please help me to re-write the query.

Thnaks in advance.
ARC
Tom Kyte
February 12, 2009 - 3:54 pm UTC

no create
no inserts
literally no look - I didn't even read the question to see if it could be done.

Calculation working hours

ARC, February 13, 2009 - 4:47 am UTC

Hi Tom,
With refrence to your follow-up I am giving create and insert statements.

Query:
select activity_id, start_time,end_time, r,
greatest( start_time, trunc(start_time)+8/24+r ),
least( trunc(start_time)+r+17/24, end_time ),
(least( trunc(start_time)+r+17/24, end_time )-greatest( satrt_time,
trunc(start_time)+8/24+r ))*24 elap
from (select activity_id, end_time, start_time from t) t1,
(
select rownum-1 r from all_objects
where rownum <= (select trunc(end_time) - trunc(start_time)+1 from t )
) t2
where to_char(start_time+r,'Dy') not in ( 'Sat', 'Sun' );

create table t (activity_id varchar2(20), end_time date, start_time date, );

Insert into t
(ACTIVITY_ID, END_TIME, START_TIME)
Values
('1-1MDLSP', TO_DATE('10/13/2008 16:01:09', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/13/2008 14:23:23', 'MM/DD/YYYY HH24:MI:SS'));
Insert into t
(ACTIVITY_ID, END_TIME, START_TIME)
Values
('1-1ME7II', TO_DATE('10/13/2008 16:35:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/13/2008 14:24:17', 'MM/DD/YYYY HH24:MI:SS'));
Insert into t
(ACTIVITY_ID, END_TIME, START_TIME)
Values
('1-1MF7GA', TO_DATE('10/13/2008 16:01:12', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/13/2008 15:03:51', 'MM/DD/YYYY HH24:MI:SS'));
Insert into t
(ACTIVITY_ID, END_TIME, START_TIME)
Values
('1-1MFOXN', TO_DATE('10/13/2008 16:01:11', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/13/2008 14:47:19', 'MM/DD/YYYY HH24:MI:SS'));
Insert into t
(ACTIVITY_ID, END_TIME, START_TIME)
Values
('1-1MEZ3F', TO_DATE('10/13/2008 16:01:12', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/13/2008 15:12:41', 'MM/DD/YYYY HH24:MI:SS'));
Insert into t
(ACTIVITY_ID, END_TIME, START_TIME)
Values
('1-1MF2WH', TO_DATE('10/13/2008 16:01:09', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/13/2008 14:18:31', 'MM/DD/YYYY HH24:MI:SS'));
Insert into t
(ACTIVITY_ID, END_TIME, START_TIME)
Values
('1-1KQ29Y', TO_DATE('10/01/2008 10:58:21', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/29/2008 14:35:19', 'MM/DD/YYYY HH24:MI:SS'));
Insert into t
(ACTIVITY_ID, END_TIME, START_TIME)
Values
('1-1LCAKL', TO_DATE('10/06/2008 11:40:40', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/03/2008 13:55:30', 'MM/DD/YYYY HH24:MI:SS'));
Insert into t
(ACTIVITY_ID, END_TIME, START_TIME)
Values
('1-1M8GU0', TO_DATE('10/13/2008 16:01:36', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/11/2008 11:45:14', 'MM/DD/YYYY HH24:MI:SS'));
Insert into t
(ACTIVITY_ID, END_TIME, START_TIME)
Values
('1-1M8QCF', TO_DATE('10/13/2008 15:18:55', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/11/2008 09:33:28', 'MM/DD/YYYY HH24:MI:SS'));
Tom Kyte
February 16, 2009 - 10:34 am UTC

I see no specification here at all. Nothing, nada. I don't even know what output you expect.

You have dates that span many days, do you really have people working that long.


do you just want the number of hours between the two dates minus hours on saturdays and sundays?

be very precise in what you need. As if you were giving a specification to a developer to generate code from.

Because - you are.

Adding Holidays to the mix!

João, March 05, 2014 - 10:41 am UTC

Hi Tom,

I was analysing your function and it fits into my needs just perfectly.

I'm just in a small dilemma... the "holidays" i have 2 types of holidays, the national holidays, that works for everything, and the region holidays.

Your function works great, because i will put the start hour and the end hour, plus the days.

Normally excluding the weekends its standard for me, but i'm out of ideas of how to exclude the holidays.

Thank you for your attention.

Online Solution

mansur.arslan@gmail.com, April 21, 2014 - 8:31 am UTC

Hi, Thank you for your very good answer I started from there and devised an alternative way w/o using intermediate tables. It also solves the problem of having multiple rows in the data table "t"
I hope this would be useful for people too.

create table t ( NAME varchar(50), stime date, etime date, clockin number,clockout number );
insert into t values ( 'JOHN', to_date( '18/12/2003 11:40','dd/mm/yyyy hh24:mi'), to_date( '22/12/2003 14:00', 'dd/mm/yyyy hh24:mi'),8, 17 );
insert into t values ( 'JOHN', to_date( '19/12/2003 13:40','dd/mm/yyyy hh24:mi'), to_date( '21/12/2003 15:00', 'dd/mm/yyyy hh24:mi'),8, 17 );
insert into t values ( 'TOM', to_date( '19/12/2003 13:40','dd/mm/yyyy hh24:mi'), to_date( '21/12/2003 15:00', 'dd/mm/yyyy hh24:mi'),8, 17 );


with oo as (SELECT LEVEL-1 rn FROM dual CONNECT BY LEVEL <= 365) --JUST GENERATES A DUMMY TABLE WITH INTEGER RECORDS FROM 1 To 365 
select
t.NAME ,sum(least( trunc(stime)+17/24+rn, etime )-greatest( stime, trunc(stime)+8/24+rn))*24 as WorkHours 
from oo
inner join t on oo.rn < (trunc(etime)-trunc(stime)+1) and to_char(stime+rn,'Dy') not in ( 'Cmt', 'Paz' ) 
group by t.NAME


Online Solution

mansur.arslan@gmail.com, April 21, 2014 - 8:46 am UTC

Hi, Again. In my above code I forgot to translate the day filters to English :) So they should have been
to_char(stime+r,'Dy') not in ( 'Sat', 'Sun' )

as in the original answer.

And also As an aswer to the additional question about National/Regional holidays:

You can add specific filters in the join clause such as 
and to_char(trunc(stime)+rn,'DD.MM')  != '04.07'  
and to_char(trunc(stime)+rn,'DD.MM')  != '01.01'

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.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library