----- Original Message -----
Sent: Tuesday, March 18, 2003 9:53
PM
Subject: RE: How to calculate the working
hours?
see answer below
> -----Original Message-----
>
From: Andrea Oracle [mailto:[EMAIL PROTECTED]]
>
> We have open Time for every
order, ef:
>
> Open
Time
> ------------------
> 03/12/03 11:08:07
>
> How to calculate the working hours (8am - 5pm, no
> weekends) that a file remain open until now? (Sysdate
> - Open_Time) returns all the hours including
weekend
> and <8am, >5pm hours. We only
like to know the
> WORKING hours.
I couldn't resist the challenge. The formula below is clunky
but it works.
Assume two dates, d1 and d2, with d1
<= d2. Find the number of working hours between d1 and d2.
Oracle SQL formula:
(&&end_hour -
&&start_hour)
* (trunc (decode (to_number (to_char (d2,
'D')), &&saturday, d2 - 1, &&sunday, d2 - 2,
d2))
- trunc (decode (to_number (to_char (d1,
'D')), &&saturday, d1 - 1, &&sunday, d1 - 2,
d1))
)
- 2 * (&&end_hour -
&&start_hour)
* (floor ((trunc (d2) - trunc (d1))
/
7)
+ decode
(sign (to_number (to_char (d2, 'D')) - to_number (to_char (d1,
'D'))),
-1, 1,
0
)
)
+ (decode (to_number (to_char (d2,
'D')),
&&saturday,
&&end_seconds,
&&sunday,
&&end_seconds,
greatest
(least (to_number (to_char (d2, 'SSSSS')) - &&start_seconds,
&&end_seconds), 0)
)
- decode (to_number (to_char (d1,
'D')),
&&saturday,
&&end_seconds,
&&sunday,
&&end_seconds,
greatest (least (to_number (to_char (d1, 'SSSSS')) - &&start_seconds,
&&end_seconds), 0)
)
) /
3600
where
&&saturday is to_date
(..., 'D') for saturday (will depend on your NLS_TERRITORY setting)
&&sunday is to_date (..., 'D') for sunday (will
depend on your NLS_TERRITORY setting)
&&start_hour is start of workday (in your case 8:00)
&&end_hour is end of workday (in your case
17:00)
&&start_seconds is number of seconds
from midnight to &&start_hour
&&end_seconds is number of seconds between &&start_hour
and &&end_hour
Proof of concept for your specifications (workday from 8:00 to
17:00)
-- populate table with sample data
drop table t ;
create table t (d1 date, d2
date) ;
declare
start_date constant date := to_date ('2003/03/01', 'YYYY/MM/DD') ;
begin
for i in
1..7
loop
for j in 1..24
loop
for k in 1..14
loop
for
l in 1..24
loop
insert into t (d1, d2)
values (start_date + i - 1 + (j - 1) / 24,
start_date + i - 1 + (j - 1) / 24 + k - 1 + (l - 1) / 24
) ;
end
loop ;
end loop ;
end loop ;
end loop ;
commit
;
end ;
/
--
-- calculate formula variables
undefine start_hour
undefine
end_hour
undefine start_seconds
undefine end_seconds
undefine saturday
undefine sunday
define start_hour =
"8"
define end_hour = "17"
column saturday_day_number noprint new_value saturday
column sunday_day_number noprint new_value sunday
column start_hour_in_seconds noprint new_value start_seconds
column end_hour_in_seconds noprint new_value
end_seconds
select
to_number (to_char (to_date ('20030111', 'YYYYMMDD'),
'D')) as saturday_day_number,
to_number
(to_char (to_date ('20030112', 'YYYYMMDD'), 'D')) as sunday_day_number,
&&start_hour * 60 * 60 as
start_hour_in_seconds,
(&&end_hour - &&start_hour) * 60 * 60 as
end_hour_in_seconds
from dual ;
clear columns
--
--
query test table to verify results.
-- change value in
where clause to verify for different begin dates
select
to_char (d1, 'DY YYYY/MM/DD
HH24:MI:SS') as d1_fmt,
to_char (d2, 'DY
YYYY/MM/DD HH24:MI:SS') as d2_fmt,
(&&end_hour - &&start_hour)
* (trunc (decode (to_number (to_char (d2,
'D')), &&saturday, d2 - 1, &&sunday, d2 - 2, d2))
- trunc (decode
(to_number (to_char (d1, 'D')), &&saturday, d1 - 1, &&sunday,
d1 - 2, d1))
)
- 2 * (&&end_hour -
&&start_hour)
* (floor ((trunc (d2) - trunc
(d1)) / 7)
+ decode
(sign (to_number (to_char (d2, 'D')) - to_number (to_char (d1, 'D'))),
-1, 1, 0
)
)
+ (decode (to_number (to_char (d2, 'D')),
&&saturday, &&end_seconds,
&&sunday, &&end_seconds,
greatest (least (to_number (to_char (d2, 'SSSSS')) - &&start_seconds,
&&end_seconds), 0)
)
- decode (to_number
(to_char (d1, 'D')),
&&saturday, &&end_seconds,
&&sunday, &&end_seconds,
greatest (least (to_number (to_char (d1, 'SSSSS')) - &&start_seconds,
&&end_seconds), 0)
)
) / 3600
as hours_open
from
t
where
d1 = to_date ('2003/03/01
00:00:00', 'YYYY/MM/DD HH24:MI:SS')
order by d1, d2
;
