Re: Vs: Function to calculate weekdays left in a month

Thanks for all the replies.

There is definately more than one way to go about this. I have built myself a little function around the case statement which I believe Mark came up with. works a treat.

Brgds

Jack

On 05/08/2008, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:
>
> I too thought of holidays. I thought then youd be going down the route
> of a non-working calendar table (non-working date, reason varchar2...)
> then I started considering a dw time dimension! I over-engineer
> sometimes. Annoyed I didn't think of case though
>
> On 04/08/2008, Bobak, Mark <Mark.Bobak_at_proquest.com> wrote:
> > Hey now, scope creep!! It wasn't in the original request!! :-)
> >
> > Seriously, though, I thought about that when I was writing my
> query....but
> > he didn't mention holidays, so I didn't consider a solution that
> accounted
> > for them....
> >
> > I suppose you could do something with a lookup table of valid holidays,
> and
> > hook it into what I've done without too much work....I think....
> >
> > -Mark
> >
> >
> > --
> > Mark J. Bobak
> > Senior Database Administrator, System & Product Technologies
> > ProQuest
> > 789 E. Eisenhower, Parkway, P.O. Box 1346
> > Ann Arbor MI 48106-1346
> > +1.734.997.4059 or +1.800.521.0600 x 4059
> > mark.bobak_at_proquest.com
> > www.proquest.com
> > www.csa.com
> >
> > ProQuest...Start here.
> >
> >
> > -----Original Message-----
> > From: Rumpi Gravenstein [mailto:rgravens_at_gmail.com]
> > Sent: Monday, August 04, 2008 6:08 PM
> > To: mwf_at_rsiz.com
> > Cc: Bobak, Mark; jack_at_vanzanen.com; Teijo Lallukka;
> oracle-l_at_freelists.org
> > Subject: Re: Vs: Function to calculate weekdays left in a month
> >
> > what about holidays? to support a "true" calc you wouldn't you have
> > to make allowances for them?
> >
> > On 8/4/08, Mark W. Farnham <mwf_at_rsiz.com> wrote:
> >> Mark's functional implementation meets what I think is the highest
> >> standard
> >> of elegance: Given the code without comment you would know exactly what
> it
> >> does just by knowing the language it is written in.
> >>
> >> IF it turns out this function is to be called a bazillion times, then it
> >> might also be worth measuring whether the construction of a calendar
> table
> >> pairing each day with the corresponding number of days left performs
> >> better
> >> in your actual application. Using Mark's function but cycling through
> the
> >> calendar instead of referencing sysdate would be a fine way to populate
> >> the
> >> calendar table's workdays_left_this_month column (or populating a new
> >> column
> >> added to an existing calendar table).
> >>
> >> Loading the likely to be reference range of the table into some layer of
> >> cache might also be a useful exercise in executing the performance test.
> >>
> >> The context of use of the function is also relevant to whether the
> >> function
> >> will perform better as being calculated each time or as a database
> lookup:
> >> If the function tends to be called low in the database together with
> other
> >> table references all of which can stay "deep" to return the answer
> stream
> >> setwise, that will tend to favor the reference table version
> increasingly
> >> as
> >> the size of the set increases. Please notice that I have not run the
> >> proposed test, so I am not claiming an answer to which would be faster
> in
> >> any case let alone the actual case, and it may indeed not be worth
> >> figuring
> >> it out.
> >>
> >> Regards,
> >>
> >> mwf
> >>
> >> -----Original Message-----
> >> From: oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org]
> >> On Behalf Of Bobak, Mark
> >> Sent: Monday, August 04, 2008 4:07 AM
> >> To: jack_at_vanzanen.com; Teijo Lallukka
> >> Cc: oracle-l_at_freelists.org
> >> Subject: RE: Vs: Function to calculate weekdays left in a month
> >>
> >> Try this:
> >> select sum((case trim(to_char(sysdate+level-1,'Day'))
> >> when 'Saturday'
> >> then 0
> >> when 'Sunday'
> >> then 0
> >> else 1 end)) weekdays_left_this_month
> >> from dual
> >> connect by level <= trunc(last_day(sysdate))-trunc(sysdate)+1
> >>
> >> As written, it includes the current day in the count.
> >>
> >> Hope that helps,
> >>
> >> -Mark
> >> ________________________________________
> >> From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On
> >> Behalf Of Jack van Zanen [jack_at_vanzanen.com]
> >> Sent: Monday, August 04, 2008 1:54 AM
> >> To: Teijo Lallukka
> >> Cc: oracle-l_at_freelists.org
> >> Subject: Re: Vs: Function to calculate weekdays left in a month
> >>
> >> If only it were that simple.
> >>
> >> That gets me all the days and I am interested in the weekdays only.
> >>
> >>
> >> <snip>
> >>
> >>
> >>
> >> --
> >> http://www.freelists.org/webpage/oracle-l
> >>
> >>
> >>
> >
> >
> > --
> > Rumpi Gravenstein
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>

-- 
J.A. van Zanen

--
http://www.freelists.org/webpage/oracle-l