RE: Query > past 10 days not the weekends

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Fri, 24 Sep 2004 15:25:28 -0400
Message-ID: <564DE4477544D411AD2C00508BDF0B6A2133DF03@usahm018.exmi01.exch.eds.com>

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 <=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

-----Original Message-----

From: bernas, glen [mailto:bernas_glen_at_emc.com]=20 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 =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_at_naturaljoin.nl]=3D20 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 =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_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 =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

--
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

Received on Fri Sep 24 2004 - 14:21:36 CDT