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@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Powell, Mark D
Sent: Friday, September 24, 2004 3:25 PM
To: oracle-l@xxxxxxxxxxxxx
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@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 <=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

 - a

-----Original Message-----
From: bernas, glen [mailto:bernas_glen@xxxxxxx]=3D20
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 =
=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@xxxxxxxxxxxxxx]=3D3D20
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 =
=3D3D
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 =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-l

Other related posts: