RE: Query > past 10 days not the weekends
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 24 Sep 2004 15:25:28 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Anthony Molinaro
Sent: Friday, September 24, 2004 3:12 PM
To: bernas_glen@xxxxxxx; oracle-l@xxxxxxxxxxxxx
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 <=3D the 13th?
I think this'll do it...
=20
select *
from your_table
where last_change_date <=3D (=20
select sysdate-(count(*)+1)
from (
select rownum days
from your_table
where rownum <=3D sysdate - (sysdate-14)
)
where to_number(to_char(sysdate+days,'d')) not in (1,7)
) =20
- a
-----Original Message-----
From: bernas, glen [mailto:bernas_glen@xxxxxxx]=20
Sent: Friday, September 24, 2004 2:43 PM
To: oracle-l@xxxxxxxxxxxxx
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 =3D
back 2 weeks because it may contain a weekend.
The version of oracle is 9.2.0
____________________________________________
Glen Bernas
Database Administrator
EMC=3DB2 =3D09
where information lives
Phone:=3D20
Direct: (508) 249-2237
Ext: 42237
-----Original Message-----
From: Lex de Haan [mailto:lex.de.haan@xxxxxxxxxxxxxx]=3D20
Sent: Friday, September 24, 2004 2:39 PM
To: bernas_glen@xxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Query > past 10 days not the weekends
which version of Oracle? or do you have a table column containing a =3D
last change date? you don't provide enough information to answer your
question ...
Kind regards,
Lex.
-------------------------------
visit http://www.naturaljoin.nl <http://www.naturaljoin.nl>
-------------------------------
skype me <callto://lexdehaan>
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of bernas, glen
Sent: Friday, September 24, 2004 19:34
To: oracle-l@xxxxxxxxxxxxx
Subject: Query > past 10 days not the weekends
How can I create a query that determine a rows that has not been =3D
updated for the last 10 business days. That would be the last 2 weeks
not =3D including the weekend.
____________________________________________
Glen Bernas
Database Administrator
EMC=3DB2
where information lives
Phone:
Direct: (508) 249-2237
Ext: 42237
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
