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

From: Bobak, Mark <>
Date: Mon, 4 Aug 2008 18:12:41 -0400
Message-ID: <>

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 J. Bobak
Senior Database Administrator, System & Product Technologies
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059  or +1.800.521.0600 x 4059

ProQuest...Start here.

-----Original Message-----
From: Rumpi Gravenstein []
Sent: Monday, August 04, 2008 6:08 PM
Cc: Bobak, Mark;; Teijo Lallukka;
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 <> 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: []
> On Behalf Of Bobak, Mark
> Sent: Monday, August 04, 2008 4:07 AM
> To:; Teijo Lallukka
> Cc:
> 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: [] On
> Behalf Of Jack van Zanen []
> Sent: Monday, August 04, 2008 1:54 AM
> To: Teijo Lallukka
> Cc:
> 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>
> --
-- Rumpi Gravenstein --
Received on Mon Aug 04 2008 - 17:12:41 CDT

Original text of this message