RE: How to calculate the working hours?

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 19 Mar 2003 10:14:49 -0800
Message-Id: <24715.322407@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C2EE43.6E435080
Content-Type: text/plain

(re-sending because my e-mail from yesterday never made it to the list)

see answer below

> -----Original Message-----
> From: Andrea Oracle [mailto:andreaoracle_at_yahoo.com]
>
> 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 ;

------_=_NextPart_001_01C2EE43.6E435080
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3DUS-ASCII">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2654.45">

<TITLE>RE: How to calculate the working hours?</TITLE>
</HEAD>
<BODY>

(re-sending because my e-mail from yesterday never = made it to the list)

see answer below

> -----Original Message----- > From: Andrea Oracle [<A = HREF=3D"mailto:andreaoracle_at_yahoo.com">mailto:andreaoracle_at_yahoo.com</A>= ]

<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; We have open Time for every order, ef:</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Open Time</FONT>
<BR><FONT SIZE=3D2>&gt; ------------------</FONT>
<BR><FONT SIZE=3D2>&gt; 03/12/03 11:08:07</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; 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

<BR><FONT SIZE=3D2>&gt; WORKING hours.</FONT>
</P>
<BR>

I couldn't resist the challenge. The formula below is = clunky but it works.
 Assume two dates, d1 and d2, with d1 <=3D d2. = Find the number of working hours between d1 and d2. Oracle SQL formula:

(&&end_hour - =

&amp;&amp;start_hour)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT>

* (trunc (decode (to_number (to_char (d2, = 'D')), &&saturday, d2 - 1, &&sunday, d2 - 2, = d2))

<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp; - trunc (decode (to_number =
(to_char (d1, 'D')), &amp;&amp;saturday, d1 - 1, &amp;&amp;sunday, d1 - =
2, d1))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; =
)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =

- 2 * (&&end_hour - =

&amp;&amp;start_hour)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT>

* (floor ((trunc (d2) - trunc = (d1)) / =

7)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT>

+ decode = (sign (to_number (to_char (d2, 'D')) - to_number (to_char (d1, = 'D'))),          &nbsp= ;   
           &nb= sp;       -1, 1, =

0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp; </FONT>

&nb= sp;      =

)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=

;           &nbsp= ;         + (decode (to_number (to_char (d2, =

'D')),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=

=

&amp;&amp;saturday, =
&amp;&amp;end_seconds,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT>

=

&amp;&amp;sunday, =
&amp;&amp;end_seconds,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT>

= greatest (least (to_number (to_char (d2, 'SSSSS')) - = &&start_seconds, &&end_seconds), 0)            =

)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT>

- decode (to_number (to_char (d1, =

'D')),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT>

&nb= sp;  &&saturday, =

&amp;&amp;end_seconds,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT>

&nb= sp;  &&sunday, =

&amp;&amp;end_seconds,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT>

&nb= sp;  greatest (least (to_number (to_char (d1, 'SSSSS')) - = &&start_seconds, &&end_seconds), 0)           &nb= sp; =

)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp; </FONT>

) / =

3600&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT>

<FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT>

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)

<P><FONT SIZE=3D2>-- populate table with sample data</FONT>
<BR><FONT SIZE=3D2>drop table t ;</FONT>
<BR><FONT SIZE=3D2>create table t (d1 date, d2 date) ;</FONT>
<BR><FONT SIZE=3D2>declare</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp; start_date constant date :=3D to_date =

('2003/03/01', 'YYYY/MM/DD') ;

<BR><FONT SIZE=3D2>begin</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp; for i in 1..7</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp; loop</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for j in 1..24</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; loop</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for =

k in 1..14

<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
loop</FONT>
<BR><FONT =

SIZE=3D2>          &nb= sp; for l in 1..24
           &nb= sp; loop
           &nb= sp;    insert into t (d1, d2)           &nb= sp;     values (start_date + i - 1 + (j - 1) / = 24,
           &nb= sp;           &nb= sp; start_date + i - 1 + (j - 1) / 24 + k - 1 + (l - 1) / 24           &nb= sp;            ) = ;
           &nb= sp; end loop ;
          end = loop ;

<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end loop ;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp; end loop ;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp; commit ;</FONT>
<BR><FONT SIZE=3D2>end ;</FONT>
<BR><FONT SIZE=3D2>/</FONT>
<BR><FONT SIZE=3D2>--</FONT>
<BR><FONT SIZE=3D2>-- calculate formula variables</FONT>
<BR><FONT SIZE=3D2>undefine start_hour</FONT>
<BR><FONT SIZE=3D2>undefine end_hour</FONT>
<BR><FONT SIZE=3D2>undefine start_seconds</FONT>
<BR><FONT SIZE=3D2>undefine end_seconds</FONT>
<BR><FONT SIZE=3D2>undefine saturday</FONT>
<BR><FONT SIZE=3D2>undefine sunday</FONT>
<BR><FONT SIZE=3D2>define start_hour =3D &quot;8&quot;</FONT>
<BR><FONT SIZE=3D2>define end_hour =3D &quot;17&quot;</FONT>
<BR><FONT SIZE=3D2>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

<BR><FONT SIZE=3D2>from dual ;</FONT>
<BR><FONT SIZE=3D2>clear columns</FONT>
<BR><FONT SIZE=3D2>--</FONT>
<BR><FONT SIZE=3D2>-- query test table to verify results.</FONT>
<BR><FONT SIZE=3D2>-- 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'))),
           &nb= sp;          -1, 1, = 0
           &nb= sp;         )           = )
    + (decode (to_number (to_char (d2, = 'D')),
           &nb= sp;   &&saturday, &&end_seconds,           &nb= sp;   &&sunday, &&end_seconds,           &nb= sp;   greatest (least (to_number (to_char (d2, 'SSSSS')) - = &&start_seconds, &&end_seconds), 0)           &nb= sp;  )
       - decode (to_number = (to_char (d1, 'D')),
           &nb= sp;     &&saturday, = &&end_seconds,
           &nb= sp;     &&sunday, = &&end_seconds,
           &nb= sp;     greatest (least (to_number (to_char (d1, = 'SSSSS')) - &&start_seconds, &&end_seconds), 0) <FONT = Received on Wed Mar 19 2003 - 12:14:49 CST