RE: Vs: Function to calculate weekdays left in a month
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: Rumpi Gravenstein <rgravens@xxxxxxxxx>, "mwf@xxxxxxxx" <mwf@xxxxxxxx>
- Date: Mon, 4 Aug 2008 18:12:41 -0400
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@xxxxxxxxxxxx
www.proquest.com
www.csa.com
ProQuest...Start here.
-----Original Message-----
From: Rumpi Gravenstein [mailto:rgravens@xxxxxxxxx]
Sent: Monday, August 04, 2008 6:08 PM
To: mwf@xxxxxxxx
Cc: Bobak, Mark; jack@xxxxxxxxxxxx; Teijo Lallukka; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxx> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Bobak, Mark
> Sent: Monday, August 04, 2008 4:07 AM
> To: jack@xxxxxxxxxxxx; Teijo Lallukka
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Jack van Zanen [jack@xxxxxxxxxxxx]
> Sent: Monday, August 04, 2008 1:54 AM
> To: Teijo Lallukka
> Cc: oracle-l@xxxxxxxxxxxxx
> 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>
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Rumpi Gravenstein
--
//www.freelists.org/webpage/oracle-l
Other related posts:
