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

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