RE: Vs: Function to calculate weekdays left in a month
Date: Mon, 4 Aug 2008 10:00:11 -0400
Message-ID: <0CD988AC0AC64902A2CBE112B7839D65@rsiz.com>
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
Received on Mon Aug 04 2008 - 09:00:11 CDT