Re: # of weekdays before sysdate?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: Sat, 11 Mar 2000 22:17:25 GMT
Message-ID: <8aegli$svk$1@nnrp1.deja.com>

In article <MPG.13343341fd46683498974b_at_nntp.ce.mediaone.net>, dkoleary_at_mediaone.net (Doug O'Leary) wrote:
> Hey;
>
> Is there a way to identify the number of weekdays since the beginning
of
> the year that's somewhat easier (and, hopefully, more accurate) than
I'm
> coming up with?
>
> What I've got so far is:
>
> -- Gives the day's number in the year.
> select to_char(sysdate,'DDD') from dual
>
> -- The hack at the # of weekends in the year so far
> -- Multiply the number of weeks * 2 on the theory that
> -- each week has a weekend, ergo...
> select to_char(sysdate,'WW') * 2 from dual;
>
> --Combining the two:
> select (to_char(sysdate,'DDD') - (to_char(sysdate,'WW') * 2)) from
dual;
>
> That sql results in 49 (as of Saturday, 3/11/00). However, according
to
> my calendar, there's been 50 weekdays since the beginning of the
year.
> If all else fails, I'll add a fudge factor; however, I'd like a
cleaner
> method of figuring that out...
>
> This bit of ugliness is going to be part of a script that identifies
> contractors' (particularly mine!) billing percentages. I'd like it
to be
> accurate.
>
> Thanks for any tips/suggestions.
>
> Doug
>
> PS: In case anyone's concerned, I get billed out as a UNIX admin,
not an
> Oracle dba...
>

select count(*)
from ( select trunc(sysdate,'year')+rnum-1

           from ( select rownum rnum
                    from all_objects
                   where rownum <= to_char(sysdate,'ddd') )
          where to_char( trunc(sysdate,'year')+rnum-1, 'd' )
                        not in ( '1', '7' )
       )

That

o create a set of rnums from 1 .. number of days in the year so far (counts on the fact that all_objects has more then 366 rows).

o then, from that set, keeps rows such that the first_day_of_the_year+rnum-1 is not a saturday or sunday.

o then counts those rows....

> --
> ==============
> Douglas K. O'Leary
> Senior System Admin
> dkoleary_at_mediaone.net
> ==============
>

Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Mar 11 2000 - 16:17:25 CST