Re: removing weekends?

From: John Hoskins <john.hoskins_at_teldta.com>
Date: 1997/03/07
Message-ID: <332085F8.830@teldta.com>#1/1

From the answering your own question department: Thanks Chris for the feedback and correspondence, some of us came up with this solution:

SELECT 5*TRUNC((TRUNC(to_date('&date2')) - TRUNC(to_date('&date1'))+1)/7) +

     decode(TO_CHAR(to_date('&date1'),'D'),
            '1', MOD((TRUNC(to_date('&date2')) -
TRUNC(to_date('&date1'))+1),7)-1,
            '2', least(MOD((TRUNC(to_date('&date2')) -
TRUNC(to_date('&date1'))+1),7),5),
            '3', least(MOD((TRUNC(to_date('&date2')) -
TRUNC(to_date('&date1'))+1),7),4),
            '4', decode(MOD((TRUNC(to_date('&date2')) -
TRUNC(to_date('&date1'))+1),7),
              1,1, 2,2, 3,3, 4,3, 5,3, 6,4, 0),
           '5', decode(MOD((TRUNC(to_date('&date2')) -
TRUNC(to_date('&date1'))+1),7),
              1,1, 2,2, 3,2, 4,2, 5,3, 6,4, 0),
          '6', decode(MOD((TRUNC(to_date('&date2')) -
TRUNC(to_date('&date1'))+1),7),
              1,1, 2,1, 3,1, 4,2, 5,3, 6,4, 0),
         '7', greatest(MOD((TRUNC(to_date('&date2')) -
TRUNC(to_date('&date1'))+1)-2,7
     ),0)) daycount

from system.dual
/

Where you started with the total days and subtracted, this one starts with the 5 times the number of weeks, and adds to it the number days dependent on the day of the start date.

John Hoskins
TDS Computing Services

Chris Halioris wrote:
>
> John Hoskins wrote:
> >
> > Hi,
> > I want to subtract two dates, and get the number of 'working' days, in
> > other words not count the weekend days. Is their a way to do this in the
> > select clause? Or will I need to do it in more than one step.
> > Any help would be appreciated,
> > John Hoskins
> > john.hoskins_at_teldta.com
>
> You could do it in a the select clause but it would be rather hairy. I
> would suggest writing stored function to do it in PL/SQL, then use the
> function in your SQL statement. For example:
>
> CREATE OR REPLACE FUNCTION working_days_between
> (date1 in DATE, date2 IN DATE)
> RETURN NUMBER IS
> -- calculate and return value
> END IF;
>
> Then if you wanted to issue a SQL statement that found all orders in your
> system that took more than 5 working days to ship you could issue the
> following SQL statement. Keep in mind that if you put this function in a
> package you will also need to put a PRAGMA RESTRICT_REFERENCES in the
> package specification (I don't think you do this for a standalone
> function).
>
> SELECT order_id, order_desc
> FROM orders
> WHERE working_days_between(order_date, ship_date) > 5;
>
> Chris Halioris
> Tactics, Inc.
> hali_at_tacticsus.com
  Received on Fri Mar 07 1997 - 00:00:00 CST