I have a date mydate and i want to subtract X working days off

i was thinking of something like

loop while x > 0
if to_char(mydate,'d') < 6 then
x = x -1
endif
mydate :mydate - 1
endloop

any ideas

g wrote:
If X tends to be large (>2weeks),
then save a few passes thru the loop by

--- computing the whole weeks
whole_weeks := ( 7* (X/5) ) --- must use integer arithmetic
mydate := mydate - whole_weeks
x := mod(x,5)
--- then put your loop here:


HTH,
ed

What if you have some holiday in between?

Ed Prochak wrote:

akkha1234 (AT) gmail (DOT) com wrote:
I'm thinking he handles that afterwards.

select count(*) from holidays where holiday_date between mydatestart
and mydate ;

Yes there are boundary issues, but faster than looping for every day.
(again this is all optimization for workdays X being relatively large
most of the time this operation is called.

As always in performance tuning, the first key is: know your data.

Ed

g wrote:
Just a quick note: TO_CHAR does not produce a number and
essentially none of the code, above, is valid. It will
never run in Oracle. What product do you have?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

g wrote:
: I have a date mydate and i want to subtract X working days off

: i was thinking of something like

: loop while x > 0
: if to_char(mydate,'d') < 6 then
: x = x -1
: endif
: mydate :mydate - 1
: endloop

two ideas come to mind

-1-

Keep a table that lists the dates of the holidays and weekends dates for
the year (call them off_days)

first_guess = mydate - x/5*7 # account for weekends

non_working = (select count(off_days) between guess and mydate)
new_guess = mydate - non_working
iterate until the new_guess doesn't change


-2-

fill a table with every work day of the year, and for each day save the
number of days worked up to that day (the_date, worked_days)

given mydate,
lookup the number of days worked in the year up to that date
select worked_days from year where the_date= mydate
subtract x to find days we want to have worked (call it wanted)
select the date that has that many days worked
select the_date from year where worked_days = wanted

$0.10

DA Morgan wrote:
It will, as PL/SQL engine will implicitly apply TO_NUMBER() to the
result of TO_CHAR() in attempt to match types of the values being
compared. Not a good idea to rely on implicit type conversion though
(at least until 10g where it seems to be more efficient than explicit
conversion sometimes.)

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

Vladimir M. Zakharychev wrote:
I took it as pseudocode as it will not work as written. Just look at
the assignment statements.

"Ed Prochak" <edprochak (AT) gmail (DOT) com> wrote

it works a treat
it always worth a second look to see if anyone else has a better idea