(re-sending because my e-mail from yesterday never made it to the list)
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 ;
