Re: How to calculate the working hours?

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Wed, 19 Mar 2003 09:47:30 -0500
Message-Id: <24715.322377@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_002C_01C2EDFC.8EAE8A20 Content-Type: text/plain;

        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

RE: How to calculate the working hours?What about holydays?

Igor Neyman, OCP DBA
ineyman_at_perceptron.com
 =20

• Original Message -----=20 From: Jacques Kilchoer=20 To: Multiple recipients of list ORACLE-L=20 Sent: Tuesday, March 18, 2003 9:53 PM Subject: RE: How to calculate the working hours?

  see answer below=20

> -----Original Message-----=20
> From: Andrea Oracle [mailto:andreaoracle_at_yahoo.com]=20

  >=20

> We have open Time for every order, ef:=20

  >=20

> Open Time=20
> ------------------=20
> 03/12/03 11:08:07=20

  >=20

> How to calculate the working hours (8am - 5pm, no=20
> weekends) that a file remain open until now? (Sysdate=20
> - Open_Time) returns all the hours including weekend=20
> and <8am, >5pm hours. We only like to know the=20
> WORKING hours.=20

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

  (&&end_hour - &&start_hour)                                            =
                             =20
    * (trunc (decode (to_number (to_char (d2, 'D')), &&saturday, d2 - 1, =
&&sunday, d2 - 2, d2))       =20
       - trunc (decode (to_number (to_char (d1, 'D')), &&saturday, d1 - =
1, &&sunday, d1 - 2, d1))     =20
      )                                                                  =
                             =20
  - 2 * (&&end_hour - &&start_hour)                                      =
                             =20
      * (floor ((trunc (d2) - trunc (d1)) / 7)                           =
                             =20
          + decode (sign (to_number (to_char (d2, 'D')) - to_number =
(to_char (d1, 'D'))),             =20
                    -1, 1, 0                                             =
                             =20
                   )                                                     =
                             =20
         )                                                               =
                             =20
  + (decode (to_number (to_char (d2, 'D')),                              =
                             =20
             &&saturday, &&end_seconds,                                  =
                             =20
             &&sunday, &&end_seconds,                                    =
                             =20
             greatest (least (to_number (to_char (d2, 'SSSSS')) - =
&&start_seconds, &&end_seconds), 0) =20
            )                                                            =
                             =20
     - decode (to_number (to_char (d1, 'D')),                            =
                             =20
               &&saturday, &&end_seconds,                                =
                             =20
               &&sunday, &&end_seconds,                                  =
                             =20
               greatest (least (to_number (to_char (d1, 'SSSSS')) - =
&&start_seconds, &&end_seconds), 0)=20
              )                                                          =
                             =20
    ) / 3600                                                             =
                             =20
                                                                         =
                             =20

  where=20
  &&saturday is to_date (..., 'D') for saturday (will depend on your = NLS_TERRITORY setting)=20
  &&sunday is to_date (..., 'D') for sunday (will depend on your = NLS_TERRITORY setting)=20

  &&start_hour is start of workday (in your case 8:00)=20
  &&end_hour is end of workday (in your case 17:00)=20
  &&start_seconds is number of seconds from midnight to &&start_hour=20
  &&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)=20

• populate table with sample data=20 drop table t ;=20 create table t (d1 date, d2 date) ;=20 declare=20 start_date constant date :=3D to_date ('2003/03/01', 'YYYY/MM/DD') = ;=20 begin=20 for i in 1..7=20 loop=20 for j in 1..24=20 loop=20 for k in 1..14=20 loop=20 for l in 1..24=20 loop=20 insert into t (d1, d2)=20 values (start_date + i - 1 + (j - 1) / 24,=20 start_date + i - 1 + (j - 1) / 24 + k - 1 + (l = - 1) / 24=20 ) ;=20 end loop ;=20 end loop ;=20 end loop ;=20 end loop ;=20 commit ;=20 end ;=20 /=20 --=20
• calculate formula variables=20 undefine start_hour=20 undefine end_hour=20 undefine start_seconds=20 undefine end_seconds=20 undefine saturday=20 undefine sunday=20 define start_hour =3D "8"=20 define end_hour =3D "17"=20 column saturday_day_number noprint new_value saturday=20 column sunday_day_number noprint new_value sunday=20 column start_hour_in_seconds noprint new_value start_seconds=20 column end_hour_in_seconds noprint new_value end_seconds=20 select=20 to_number (to_char (to_date ('20030111', 'YYYYMMDD'), 'D')) as = saturday_day_number,=20 to_number (to_char (to_date ('20030112', 'YYYYMMDD'), 'D')) as = sunday_day_number,=20 &&start_hour * 60 * 60 as start_hour_in_seconds,=20 (&&end_hour - &&start_hour) * 60 * 60 as end_hour_in_seconds=20 from dual ;=20 clear columns=20 --=20
• query test table to verify results.=20
• change value in where clause to verify for different begin dates=20 select=20 to_char (d1, 'DY YYYY/MM/DD HH24:MI:SS') as d1_fmt,=20 to_char (d2, 'DY YYYY/MM/DD HH24:MI:SS') as d2_fmt,=20 (&&end_hour - &&start_hour)=20
  • (trunc (decode (to_number (to_char (d2, 'D')), &&saturday, d2 - = 1, &&sunday, d2 - 2, d2))=20
    • trunc (decode (to_number (to_char (d1, 'D')), &&saturday, d1 = - 1, &&sunday, d1 - 2, d1))=20 )=20
      • 2 * (&&end_hour - &&start_hour)=20
        • (floor ((trunc (d2) - trunc (d1)) / 7)=20 + decode (sign (to_number (to_char (d2, 'D')) - to_number = (to_char (d1, 'D'))),=20 -1, 1, 0=20 )=20 )=20 + (decode (to_number (to_char (d2, 'D')),=20 &&saturday, &&end_seconds,=20 &&sunday, &&end_seconds,=20 greatest (least (to_number (to_char (d2, 'SSSSS')) - = &&start_seconds, &&end_seconds), 0)=20 )=20
          • decode (to_number (to_char (d1, 'D')),=20 &&saturday, &&end_seconds,=20 &&sunday, &&end_seconds,=20 greatest (least (to_number (to_char (d1, 'SSSSS')) - = &&start_seconds, &&end_seconds), 0)=20 )=20 ) / 3600=20 as hours_open=20 from=20 t=20 where=20 d1 =3D to_date ('2003/03/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS')=20 order by d1, d2 ;=20

------=_NextPart_000_002C_01C2EDFC.8EAE8A20 Content-Type: text/html;

        charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>RE: How to calculate the working hours?</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =

charset=3Diso-8859-1">

<META content=3D"MSHTML 5.50.4731.2200" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2>What about holydays?</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV>Igor Neyman, OCP DBA<BR><A=20

href=3D"mailto:ineyman_at_perceptron.com">ineyman_at_perceptron.com</A><BR>&nbs= p; </DIV>

<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<BLOCKQUOTE dir=3Dltr=20

style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; = BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">   <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>   <DIV=20
  style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: = black"><B>From:</B>=20
  <A title=3DJacques.Kilchoer_at_quest.com=20   href=3D"mailto:Jacques.Kilchoer_at_quest.com">Jacques Kilchoer</A> </DIV>   <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A = title=3DORACLE-L_at_fatcity.com=20
  href=3D"mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list = ORACLE-L</A>=20
  </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Tuesday, March 18, 2003 = 9:53=20
  PM</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> RE: How to calculate = the working=20
  hours?</DIV>

  <DIV><BR></DIV>
  <P><FONT size=3D2>see answer below</FONT> </P>
  <P><FONT size=3D2>&gt; -----Original Message-----</FONT> <BR><FONT =

size=3D2>&gt;=20
  From: Andrea Oracle [<A=20
  =
href=3D"mailto:andreaoracle_at_yahoo.com">mailto:andreaoracle_at_yahoo.com</A>]= </FONT>=20
  <BR><FONT size=3D2>&gt; </FONT><BR><FONT size=3D2>&gt; We have open = Time for every=20
  order, ef:</FONT> <BR><FONT size=3D2>&gt; </FONT><BR><FONT = size=3D2>&gt; Open=20

  Time</FONT> <BR><FONT size=3D2>&gt; ------------------</FONT> =

<BR><FONT=20

  size=3D2>&gt; 03/12/03 11:08:07</FONT> <BR><FONT size=3D2>&gt; =
</FONT><BR><FONT=20
  size=3D2>&gt; How to calculate the working hours (8am - 5pm, no</FONT> =

<BR><FONT=20
  size=3D2>&gt; weekends) that a file remain open until now?&nbsp; = (Sysdate</FONT>=20
  <BR><FONT size=3D2>&gt; - Open_Time) returns all the hours including=20   weekend</FONT> <BR><FONT size=3D2>&gt; and &lt;8am, &gt;5pm = hours.&nbsp; We only=20
  like to know the</FONT> <BR><FONT size=3D2>&gt; WORKING hours.</FONT> = </P><BR>
  <P><FONT size=3D2>I couldn't resist the challenge. The formula below = is clunky=20
  but it works.</FONT> <BR><FONT size=3D2>Assume two dates, d1 and d2, = with d1=20
  &lt;=3D d2. Find the number of working hours between d1 and d2.</FONT> = <BR><FONT=20
  size=3D2>Oracle SQL formula:</FONT> </P>   <P><FONT size=3D2>(&amp;&amp;end_hour -=20   =

&&start_hour)        &nbs=
p;           &nbsp=
;            =
            &=
nbsp;           &n=
bsp;           &nb=

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

)            =
            &=
nbsp;           &n=
bsp;           &nb=
sp;           &nbs=
p;           &nbsp=
;            =

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   </FONT><BR><FONT size=3D2>- 2 * (&amp;&amp;end_hour -=20   =

&&start_hour)        &nbs=
p;           &nbsp=
;            =
            &=
nbsp;           &n=

bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   </FONT><BR><FONT size=3D2>&nbsp;&nbsp;&nbsp; * (floor ((trunc (d2) - = trunc (d1))=20
  /=20
  =

7)           &nbsp=
;            =
            &=
nbsp;           &n=

bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   </FONT><BR><FONT size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; + = decode=20
  (sign (to_number (to_char (d2, 'D')) - to_number (to_char (d1,=20   =
'D'))),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= &nbsp;&nbsp;=20
  </FONT><BR><FONT=20
  =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   -1, 1,=20
  =

0            =
            &=
nbsp;           &n=
bsp;           &nb=
sp;           &nbs=
p;           &nbsp=

;&nbsp;=20
  </FONT><BR><FONT=20
  =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
  =

)            =
            &=
nbsp;           &n=
bsp;           &nb=
sp;           &nbs=
p;           &nbsp=

;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   </FONT><BR><FONT size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   =

)            =
            &=
nbsp;           &n=
bsp;           &nb=
sp;           &nbs=
p;           &nbsp=
;            =

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   </FONT><BR><FONT size=3D2>+ (decode (to_number (to_char (d2,=20   =

'D')),           &=
nbsp;           &n=
bsp;           &nb=
sp;           &nbs=

p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   </FONT><BR><FONT=20
  size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   &amp;&amp;saturday,=20
  =

&&end_seconds,        &nb=
sp;           &nbs=
p;           &nbsp=
;            =
            &=

nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
  </FONT><BR><FONT=20
  size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   &amp;&amp;sunday,=20
  =

&&end_seconds,        &nb=
sp;           &nbs=
p;           &nbsp=
;            =
            &=

nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   </FONT><BR><FONT=20
  size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = greatest=20
  (least (to_number (to_char (d2, 'SSSSS')) - &amp;&amp;start_seconds,=20   &amp;&amp;end_seconds), 0)&nbsp; </FONT><BR><FONT=20   size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   =

)            =
            &=
nbsp;           &n=
bsp;           &nb=
sp;           &nbs=
p;           &nbsp=
;            =

&nbsp;&nbsp;&nbsp;&nbsp;=20
  </FONT><BR><FONT size=3D2>&nbsp;&nbsp; - decode (to_number (to_char = (d1,=20
  =

'D')),           &=
nbsp;           &n=
bsp;           &nb=
sp;           &nbs=

p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   </FONT><BR><FONT=20
  =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;=20
  &amp;&amp;saturday,=20
  =

&&end_seconds,        &nb=
sp;           &nbs=
p;           &nbsp=
;            =
            &=

nbsp;&nbsp;&nbsp;&nbsp;=20
  </FONT><BR><FONT=20
  =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;=20
  &amp;&amp;sunday,=20
  =

&&end_seconds,        &nb=
sp;           &nbs=
p;           &nbsp=
;            =
            &=

nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
  </FONT><BR><FONT=20
  =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;=20
  greatest (least (to_number (to_char (d1, 'SSSSS')) - = &amp;&amp;start_seconds,=20
  &amp;&amp;end_seconds), 0)</FONT> <BR><FONT=20   =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;=20
  =

)            =
            &=
nbsp;           &n=
bsp;           &nb=
sp;           &nbs=
p;           &nbsp=
;            =

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

3600           &nb=
sp;           &nbs=
p;           &nbsp=
;            =
            &=
nbsp;           &n=
bsp;           &nb=

sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
  </FONT><BR><FONT=20
  =

size=3D2>          &nbs=
p;           &nbsp=
;            =
            &=
nbsp;           &n=
bsp;           &nb=
sp;           &nbs=
p;           &nbsp=

;&nbsp;&nbsp;&nbsp;&nbsp;=20
  </FONT></P>
  <P><FONT size=3D2>where</FONT> <BR><FONT size=3D2>&amp;&amp;saturday = is to_date=20
  (..., 'D') for saturday (will depend on your NLS_TERRITORY = setting)</FONT>=20
  <BR><FONT size=3D2>&amp;&amp;sunday is to_date (..., 'D') for sunday = (will=20
  depend on your NLS_TERRITORY setting)</FONT> <BR><FONT=20   size=3D2>&amp;&amp;start_hour is start of workday (in your case = 8:00)</FONT>=20
  <BR><FONT size=3D2>&amp;&amp;end_hour is end of workday (in your case=20   17:00)</FONT> <BR><FONT size=3D2>&amp;&amp;start_seconds is number of = seconds=20
  from midnight to &amp;&amp;start_hour</FONT> <BR><FONT=20   size=3D2>&amp;&amp;end_seconds is number of seconds between = &amp;&amp;start_hour=20
  and &amp;&amp;end_hour</FONT> </P>
  <P><FONT size=3D2>Proof of concept for your specifications (workday = from 8:00 to=20
  17:00)</FONT> </P>
  <P><FONT size=3D2>-- populate table with sample data</FONT> <BR><FONT=20   size=3D2>drop table t ;</FONT> <BR><FONT size=3D2>create table t (d1 = date, d2=20
  date) ;</FONT> <BR><FONT size=3D2>declare</FONT> <BR><FONT = size=3D2>&nbsp;&nbsp;=20
  start_date constant date :=3D to_date ('2003/03/01', 'YYYY/MM/DD') = ;</FONT>=20
  <BR><FONT size=3D2>begin</FONT> <BR><FONT size=3D2>&nbsp;&nbsp; for i = in=20
  1..7</FONT> <BR><FONT size=3D2>&nbsp;&nbsp; loop</FONT> <BR><FONT=20   size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for j in 1..24</FONT> = <BR><FONT=20
  size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; loop</FONT> <BR><FONT=20   size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for k in = 1..14</FONT>=20
  <BR><FONT size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = loop</FONT>=20
  <BR><FONT=20
  =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p; for=20
  l in 1..24</FONT> <BR><FONT=20
  =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;=20
  loop</FONT> <BR><FONT=20
  =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp;=20
  insert into t (d1, d2)</FONT> <BR><FONT=20   =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp;&nbsp;=20
  values (start_date + i - 1 + (j - 1) / 24,</FONT> <BR><FONT=20   =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp= ;=20
  start_date + i - 1 + (j - 1) / 24 + k - 1 + (l - 1) / 24</FONT> = <BR><FONT=20
  =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   ) ;</FONT> <BR><FONT=20
  =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p; end=20
  loop ;</FONT> <BR><FONT=20
  size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end loop = ;</FONT>=20
  <BR><FONT size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end loop ;</FONT> = <BR><FONT=20
  size=3D2>&nbsp;&nbsp; end loop ;</FONT> <BR><FONT = size=3D2>&nbsp;&nbsp; commit=20
  ;</FONT> <BR><FONT size=3D2>end ;</FONT> <BR><FONT size=3D2>/</FONT> = <BR><FONT=20
  size=3D2>--</FONT> <BR><FONT size=3D2>-- calculate formula = variables</FONT>=20
  <BR><FONT size=3D2>undefine start_hour</FONT> <BR><FONT = size=3D2>undefine=20
  end_hour</FONT> <BR><FONT size=3D2>undefine start_seconds</FONT> = <BR><FONT=20
  size=3D2>undefine end_seconds</FONT> <BR><FONT size=3D2>undefine = saturday</FONT>=20
  <BR><FONT size=3D2>undefine sunday</FONT> <BR><FONT size=3D2>define = start_hour =3D=20
  "8"</FONT> <BR><FONT size=3D2>define end_hour =3D "17"</FONT> = <BR><FONT=20
  size=3D2>column saturday_day_number noprint new_value saturday</FONT> = <BR><FONT=20
  size=3D2>column sunday_day_number noprint new_value sunday</FONT> = <BR><FONT=20
  size=3D2>column start_hour_in_seconds noprint new_value = start_seconds</FONT>=20
  <BR><FONT size=3D2>column end_hour_in_seconds noprint new_value=20   end_seconds</FONT> <BR><FONT size=3D2>select</FONT> <BR><FONT=20   size=3D2>&nbsp;&nbsp; to_number (to_char (to_date ('20030111', = 'YYYYMMDD'),=20
  'D')) as saturday_day_number,</FONT> <BR><FONT size=3D2>&nbsp;&nbsp; = to_number=20
  (to_char (to_date ('20030112', 'YYYYMMDD'), 'D')) as = sunday_day_number,</FONT>=20
  <BR><FONT size=3D2>&nbsp;&nbsp; &amp;&amp;start_hour * 60 * 60 as=20   start_hour_in_seconds,</FONT> <BR><FONT size=3D2>&nbsp;&nbsp;=20   (&amp;&amp;end_hour - &amp;&amp;start_hour) * 60 * 60 as=20   end_hour_in_seconds</FONT> <BR><FONT size=3D2>from dual ;</FONT> = <BR><FONT=20
  size=3D2>clear columns</FONT> <BR><FONT size=3D2>--</FONT> <BR><FONT = size=3D2>--=20
  query test table to verify results.</FONT> <BR><FONT size=3D2>-- = change value in=20
  where clause to verify for different begin dates</FONT> <BR><FONT=20   size=3D2>select</FONT> <BR><FONT size=3D2>&nbsp;&nbsp; to_char (d1, = 'DY YYYY/MM/DD=20
  HH24:MI:SS') as d1_fmt,</FONT> <BR><FONT size=3D2>&nbsp;&nbsp; to_char = (d2, 'DY=20
  YYYY/MM/DD HH24:MI:SS') as d2_fmt,</FONT> <BR><FONT = size=3D2>&nbsp;&nbsp;=20
  (&amp;&amp;end_hour - &amp;&amp;start_hour)</FONT> <BR><FONT=20   size=3D2>&nbsp;&nbsp;&nbsp;&nbsp; * (trunc (decode (to_number (to_char = (d2,=20
  'D')), &amp;&amp;saturday, d2 - 1, &amp;&amp;sunday, d2 - 2, = d2))</FONT>=20
  <BR><FONT size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - trunc = (decode=20
  (to_number (to_char (d1, 'D')), &amp;&amp;saturday, d1 - 1, = &amp;&amp;sunday,=20
  d1 - 2, d1))</FONT> <BR><FONT =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   )</FONT> <BR><FONT size=3D2>&nbsp;&nbsp; - 2 * (&amp;&amp;end_hour -=20   &amp;&amp;start_hour)</FONT> <BR><FONT=20   size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; * (floor ((trunc (d2) - = trunc=20
  (d1)) / 7)</FONT> <BR><FONT=20
  size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = + decode=20
  (sign (to_number (to_char (d2, 'D')) - to_number (to_char (d1, = 'D'))),</FONT>=20
  <BR><FONT=20
  =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   -1, 1, 0</FONT> <BR><FONT=20
  =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20   )</FONT> <BR><FONT=20
  size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = )</FONT>=20

  <BR><FONT size=3D2>&nbsp;&nbsp; + (decode (to_number (to_char (d2, =
'D')),</FONT>=20
  <BR><FONT=20

  =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;=20
  &amp;&amp;saturday, &amp;&amp;end_seconds,</FONT> <BR><FONT=20   =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;=20
  &amp;&amp;sunday, &amp;&amp;end_seconds,</FONT> <BR><FONT=20   =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;=20
  greatest (least (to_number (to_char (d2, 'SSSSS')) - = &amp;&amp;start_seconds,=20
  &amp;&amp;end_seconds), 0)</FONT> <BR><FONT=20   =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;=20
  )</FONT> <BR><FONT size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - decode = (to_number=20
  (to_char (d1, 'D')),</FONT> <BR><FONT=20   =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp;&nbsp;=20
  &amp;&amp;saturday, &amp;&amp;end_seconds,</FONT> <BR><FONT=20   =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp;&nbsp;=20
  &amp;&amp;sunday, &amp;&amp;end_seconds,</FONT> <BR><FONT=20   =
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp;&nbsp;=20
  greatest (least (to_number (to_char (d1, 'SSSSS')) - = &amp;&amp;start_seconds,=20
  &amp;&amp;end_seconds), 0)</FONT> <BR><FONT=20 Received on Wed Mar 19 2003 - 08:47:30 CST