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> </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> </DIV>
<DIV> </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>> -----Original Message-----</FONT> <BR><FONT =
size=3D2>>=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>> </FONT><BR><FONT size=3D2>> We have open =
Time for every=20
order, ef:</FONT> <BR><FONT size=3D2>> </FONT><BR><FONT =
size=3D2>> Open=20
Time</FONT> <BR><FONT size=3D2>> ------------------</FONT> =
<BR><FONT=20
size=3D2>> 03/12/03 11:08:07</FONT> <BR><FONT size=3D2>> =
</FONT><BR><FONT=20
size=3D2>> How to calculate the working hours (8am - 5pm, no</FONT> =
<BR><FONT=20
size=3D2>> weekends) that a file remain open until now? =
(Sysdate</FONT>=20
<BR><FONT size=3D2>> - Open_Time) returns all the hours including=20
weekend</FONT> <BR><FONT size=3D2>> and <8am, >5pm =
hours. We only=20
like to know the</FONT> <BR><FONT size=3D2>> 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
<=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>(&&end_hour -=20
=
&&start_hour) &nbs=
p;  =
; =
&=
nbsp; &n=
bsp; &nb=
sp; =20
</FONT><BR><FONT size=3D2> * (trunc (decode (to_number (to_char =
(d2,=20
'D')), &&saturday, d2 - 1, &&sunday, d2 - 2,=20
d2)) </FONT><BR><FONT=20
size=3D2> - trunc (decode (to_number (to_char =
(d1,=20
'D')), &&saturday, d1 - 1, &&sunday, d1 - 2,=20
d1)) </FONT><BR><FONT =
size=3D2> =20
=
) =
&=
nbsp; &n=
bsp; &nb=
sp; &nbs=
p;  =
; =
=20
</FONT><BR><FONT size=3D2>- 2 * (&&end_hour -=20
=
&&start_hour) &nbs=
p;  =
; =
&=
nbsp; &n=
bsp; =20
</FONT><BR><FONT size=3D2> * (floor ((trunc (d2) - =
trunc (d1))=20
/=20
=
7)  =
; =
&=
nbsp; &n=
bsp; =20
</FONT><BR><FONT size=3D2> + =
decode=20
(sign (to_number (to_char (d2, 'D')) - to_number (to_char (d1,=20
=
'D'))), =
=20
</FONT><BR><FONT=20
=
size=3D2> &nbs=
p; =20
-1, 1,=20
=
0 =
&=
nbsp; &n=
bsp; &nb=
sp; &nbs=
p;  =
; =20
</FONT><BR><FONT=20
=
size=3D2> &nbs=
p; =20
=
) =
&=
nbsp; &n=
bsp; &nb=
sp; &nbs=
p;  =
; =20
</FONT><BR><FONT size=3D2> =20
=
) =
&=
nbsp; &n=
bsp; &nb=
sp; &nbs=
p;  =
; =
=20
</FONT><BR><FONT size=3D2>+ (decode (to_number (to_char (d2,=20
=
'D')), &=
nbsp; &n=
bsp; &nb=
sp; &nbs=
p; =20
</FONT><BR><FONT=20
size=3D2> =20
&&saturday,=20
=
&&end_seconds, &nb=
sp; &nbs=
p;  =
; =
&=
nbsp; =20
</FONT><BR><FONT=20
size=3D2> =20
&&sunday,=20
=
&&end_seconds, &nb=
sp; &nbs=
p;  =
; =
&=
nbsp; =20
</FONT><BR><FONT=20
size=3D2> =
greatest=20
(least (to_number (to_char (d2, 'SSSSS')) - &&start_seconds,=20
&&end_seconds), 0) </FONT><BR><FONT=20
size=3D2> =20
=
) =
&=
nbsp; &n=
bsp; &nb=
sp; &nbs=
p;  =
; =
=20
</FONT><BR><FONT size=3D2> - decode (to_number (to_char =
(d1,=20
=
'D')), &=
nbsp; &n=
bsp; &nb=
sp; &nbs=
p; =20
</FONT><BR><FONT=20
=
size=3D2> &nbs=
p; =20
&&saturday,=20
=
&&end_seconds, &nb=
sp; &nbs=
p;  =
; =
&=
nbsp; =20
</FONT><BR><FONT=20
=
size=3D2> &nbs=
p; =20
&&sunday,=20
=
&&end_seconds, &nb=
sp; &nbs=
p;  =
; =
&=
nbsp; =20
</FONT><BR><FONT=20
=
size=3D2> &nbs=
p; =20
greatest (least (to_number (to_char (d1, 'SSSSS')) - =
&&start_seconds,=20
&&end_seconds), 0)</FONT> <BR><FONT=20
=
size=3D2> &nbs=
p;=20
=
) =
&=
nbsp; &n=
bsp; &nb=
sp; &nbs=
p;  =
; =
=20
</FONT><BR><FONT size=3D2> ) /=20
=
3600 &nb=
sp; &nbs=
p;  =
; =
&=
nbsp; &n=
bsp; &nb=
sp; =20
</FONT><BR><FONT=20
=
size=3D2> &nbs=
p;  =
; =
&=
nbsp; &n=
bsp; &nb=
sp; &nbs=
p;  =
; =20
</FONT></P>
<P><FONT size=3D2>where</FONT> <BR><FONT size=3D2>&&saturday =
is to_date=20
(..., 'D') for saturday (will depend on your NLS_TERRITORY =
setting)</FONT>=20
<BR><FONT size=3D2>&&sunday is to_date (..., 'D') for sunday =
(will=20
depend on your NLS_TERRITORY setting)</FONT> <BR><FONT=20
size=3D2>&&start_hour is start of workday (in your case =
8:00)</FONT>=20
<BR><FONT size=3D2>&&end_hour is end of workday (in your case=20
17:00)</FONT> <BR><FONT size=3D2>&&start_seconds is number of =
seconds=20
from midnight to &&start_hour</FONT> <BR><FONT=20
size=3D2>&&end_seconds is number of seconds between =
&&start_hour=20
and &&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> =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> for i =
in=20
1..7</FONT> <BR><FONT size=3D2> loop</FONT> <BR><FONT=20
size=3D2> for j in 1..24</FONT> =
<BR><FONT=20
size=3D2> loop</FONT> <BR><FONT=20
size=3D2> for k in =
1..14</FONT>=20
<BR><FONT size=3D2> =
loop</FONT>=20
<BR><FONT=20
=
size=3D2> &nbs=
p; for=20
l in 1..24</FONT> <BR><FONT=20
=
size=3D2> &nbs=
p;=20
loop</FONT> <BR><FONT=20
=
size=3D2> &nbs=
p; =20
insert into t (d1, d2)</FONT> <BR><FONT=20
=
size=3D2> &nbs=
p; =20
values (start_date + i - 1 + (j - 1) / 24,</FONT> <BR><FONT=20
=
size=3D2> &nbs=
p;  =
;=20
start_date + i - 1 + (j - 1) / 24 + k - 1 + (l - 1) / 24</FONT> =
<BR><FONT=20
=
size=3D2> &nbs=
p; =20
) ;</FONT> <BR><FONT=20
=
size=3D2> &nbs=
p; end=20
loop ;</FONT> <BR><FONT=20
size=3D2> end loop =
;</FONT>=20
<BR><FONT size=3D2> end loop ;</FONT> =
<BR><FONT=20
size=3D2> end loop ;</FONT> <BR><FONT =
size=3D2> 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> to_number (to_char (to_date ('20030111', =
'YYYYMMDD'),=20
'D')) as saturday_day_number,</FONT> <BR><FONT size=3D2> =
to_number=20
(to_char (to_date ('20030112', 'YYYYMMDD'), 'D')) as =
sunday_day_number,</FONT>=20
<BR><FONT size=3D2> &&start_hour * 60 * 60 as=20
start_hour_in_seconds,</FONT> <BR><FONT size=3D2> =20
(&&end_hour - &&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> to_char (d1, =
'DY YYYY/MM/DD=20
HH24:MI:SS') as d1_fmt,</FONT> <BR><FONT size=3D2> to_char =
(d2, 'DY=20
YYYY/MM/DD HH24:MI:SS') as d2_fmt,</FONT> <BR><FONT =
size=3D2> =20
(&&end_hour - &&start_hour)</FONT> <BR><FONT=20
size=3D2> * (trunc (decode (to_number (to_char =
(d2,=20
'D')), &&saturday, d2 - 1, &&sunday, d2 - 2, =
d2))</FONT>=20
<BR><FONT size=3D2> - trunc =
(decode=20
(to_number (to_char (d1, 'D')), &&saturday, d1 - 1, =
&&sunday,=20
d1 - 2, d1))</FONT> <BR><FONT =
size=3D2> =20
)</FONT> <BR><FONT size=3D2> - 2 * (&&end_hour -=20
&&start_hour)</FONT> <BR><FONT=20
size=3D2> * (floor ((trunc (d2) - =
trunc=20
(d1)) / 7)</FONT> <BR><FONT=20
size=3D2> =
+ decode=20
(sign (to_number (to_char (d2, 'D')) - to_number (to_char (d1, =
'D'))),</FONT>=20
<BR><FONT=20
=
size=3D2> &nbs=
p; =20
-1, 1, 0</FONT> <BR><FONT=20
=
size=3D2> &nbs=
p; =20
)</FONT> <BR><FONT=20
size=3D2> =
)</FONT>=20
<BR><FONT size=3D2> + (decode (to_number (to_char (d2, =
'D')),</FONT>=20
<BR><FONT=20
=
size=3D2> &nbs=
p; =20
&&saturday, &&end_seconds,</FONT> <BR><FONT=20
=
size=3D2> &nbs=
p; =20
&&sunday, &&end_seconds,</FONT> <BR><FONT=20
=
size=3D2> &nbs=
p; =20
greatest (least (to_number (to_char (d2, 'SSSSS')) - =
&&start_seconds,=20
&&end_seconds), 0)</FONT> <BR><FONT=20
=
size=3D2> &nbs=
p; =20
)</FONT> <BR><FONT size=3D2> - decode =
(to_number=20
(to_char (d1, 'D')),</FONT> <BR><FONT=20
=
size=3D2> &nbs=
p; =20
&&saturday, &&end_seconds,</FONT> <BR><FONT=20
=
size=3D2> &nbs=
p; =20
&&sunday, &&end_seconds,</FONT> <BR><FONT=20
=
size=3D2> &nbs=
p; =20
greatest (least (to_number (to_char (d1, 'SSSSS')) - =
&&start_seconds,=20
&&end_seconds), 0)</FONT> <BR><FONT=20
Received on Wed Mar 19 2003 - 08:47:30 CST