| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query > past 10 days not the weekends
Someone mentioned that there is a date function that eliminates =
weekends.
Does that really exist??
Glen Bernas
Database Administrator
EMC=B2 =09
where information lives
Phone:=20
Direct: (508) 249-2237
Ext: 42237
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Powell, Mark D
Sent: Friday, September 24, 2004 3:25 PM
To: oracle-l_at_freelists.org
Subject: RE: Query > past 10 days not the weekends
But the problem with this logic and Mark's post is what about holidays? =
If
holidays also have to be accounted for as well as weekend days then you =
need
either a work calendar table to join to or a not in subquery on a list =
of
holidays. Being hard coding value lists is bad practice so I would go =
with
a holiday calendar table. Still if this is just an exercise you have
answers.
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Anthony Molinaro
Sent: Friday, September 24, 2004 3:12 PM
To: bernas_glen_at_emc.com; oracle-l_at_freelists.org
Subject: RE: Query > past 10 days not the weekends
Glen,
if today is Friday the 24 and 2 weeks (10 biz days ago) is mon the 13th,
you need to find records that have last_change_date <=3D3D the 13th?
I think this'll do it...
=3D20
select *
from your_table
where last_change_date <=3D3D (=3D20
select sysdate-(count(*)+1)
from (
select rownum days
from your_table
where rownum <=3D3D sysdate - (sysdate-14)
)
where to_number(to_char(sysdate+days,'d')) not in (1,7)
) =3D20
-----Original Message-----
From: bernas, glen [mailto:bernas_glen_at_emc.com]=3D20
Sent: Friday, September 24, 2004 2:43 PM
To: oracle-l_at_freelists.org
Subject: RE: Query > past 10 days not the weekends
Yes I do have the last change date.
I have problems determining 10 business days in a query. I can't go =
=3D3D
back 2 weeks because it may contain a weekend.
The version of oracle is 9.2.0
Glen Bernas
Database Administrator
EMC=3D3DB2 =3D3D09
where information lives
Phone:=3D3D20
Direct: (508) 249-2237
Ext: 42237
-----Original Message-----
From: Lex de Haan [mailto:lex.de.haan_at_naturaljoin.nl]=3D3D20
Sent: Friday, September 24, 2004 2:39 PM
To: bernas_glen_at_emc.com; oracle-l_at_freelists.org
Subject: RE: Query > past 10 days not the weekends
which version of Oracle? or do you have a table column containing a =
=3D3D
last change date? you don't provide enough information to answer your
question ...
Kind regards,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of bernas, glen
Sent: Friday, September 24, 2004 19:34
To: oracle-l_at_freelists.org
Subject: Query > past 10 days not the weekends
How can I create a query that determine a rows that has not been =3D3D updated for the last 10 business days. That would be the last 2 weeks not =3D3D including the weekend.
Glen Bernas
Database Administrator
EMC=3D3DB2
where information lives
Phone:
Direct: (508) 249-2237
Ext: 42237
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 24 2004 - 14:26:14 CDT
 |
 |