-- Thanks for the question regarding "Calculate previous working date", version 9.1.3

simple approach   June 4, 2008 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

create function return date 

- get previous date ( just date -1)
- if previous date is sat/sun, call function again with the date
- if prevous date is in the list of dates on the holiday table, call function again with the date.

As with any coding with recursive calls, be careful.

  June 5, 2008 - 2am Central time zone

Bookmark | Bottom | Top

Reviewer: Asheesh Dhupper from India

Hi Tom,
  A very good answer. But when I executed the qry on Oracle8i
(Oracle8i 8.1.7.4.0) it gave me the below error.
ORA-01436: CONNECT BY loop in user data
  
Is this because of version. Please guide.

To: Asheesh   June 5, 2008 - 6am Central time zone

Bookmark | Bottom | Top

Reviewer: Narendra from UK

select dt,
       (select max(dt-R)
          from (select rownum R from dual connect by level <= 5 and prior dbms_random.random is not 
null)
         where to_char((dt-R),'DY') not in ('SAT','SUN')
           and (dt-R) not in ( trunc(dt,'y'),
                               next_day(trunc(dt,'y')-1,'MON')+14,
                               next_day(add_months(trunc(dt,'y'),1)-1,'MON')+14,
                               next_day(last_day(add_months(trunc(dt,'y'),4))-7,'MON'),
                               add_months(trunc(dt,'y'),6)+3,
                               next_day(add_months(trunc(dt,'y'),8)-1,'MON'),
                               next_day(add_months(trunc(dt,'y'),9)-1,'MON')+7,
                               next_day(add_months(trunc(dt,'y'),10)-1,'THU')+21,
                               next_day(add_months(trunc(dt,'y'),10)-1,'THU')+22,
                               add_months(trunc(dt,'y'),11)+24 )
        ) prior_bd
  from (select to_date('01-jan-2008','dd-mon-yyyy')+level-1 dt from dual connect by level 
<= 366 and prior dbms_random.random is not null)

Did I miss something?   June 5, 2008 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: Greg from Toronto

Is it just me, or does that query "lie" a little?

Try plugging in Jan 1 2006:

SQL > l
  1  select dt,
  2         (select max(dt-R)
  3            from (select rownum R from dual connect by level <= 5)
  4           where to_char((dt-R),'DY') not in ('SAT','SUN')
  5             and (dt-R) not in ( trunc(dt,'y'),
  6                                 next_day(trunc(dt,'y')-1,'MON')+14,
  7                                 next_day(add_months(trunc(dt,'y'),1)-1,'MON')+14,
  8                                 next_day(last_day(add_months(trunc(dt,'y'),4))-7,'MON'),
  9                                 add_months(trunc(dt,'y'),6)+3,
 10                                 next_day(add_months(trunc(dt,'y'),8)-1,'MON'),
 11                                 next_day(add_months(trunc(dt,'y'),9)-1,'MON')+7,
 12                                 next_day(add_months(trunc(dt,'y'),10)-1,'THU')+21,
 13                                 next_day(add_months(trunc(dt,'y'),10)-1,'THU')+22,
 14                                 add_months(trunc(dt,'y'),11)+24 )
 15          ) prior_bd
 16*   from (select to_date('01-jan-2006','dd-mon-yyyy')+level-1 dt from dual connect by level <= 
366)
SQL > /

DT          PRIOR_BD
----------- -----------
01-JAN-2006 30-DEC-2005
02-JAN-2006 30-DEC-2005
03-JAN-2006 02-JAN-2006   <-- Jan 2 is a holiday, Jan 1 falls on SUN!!
04-JAN-2006 03-JAN-2006
05-JAN-2006 04-JAN-2006

If you want/need the full formulas for dates, I wrote a calendar in Excel a while back (Canadian 
holidays only, sorry):
http://dot.ditto.googlepages.com/Calendar.xls
Feel free to grab a copy and check the formulas for the dates (most involve some MOD logic to shift 
those that land on weekends to weekdays).

However, what I've learned (thanks to 9/11 ) is you can't calculate all the business days (there's 
always 1 exception) ... you almost need to use a table to either:

1) store the business dates
or
2) store the holidays

.. then work from there ...
(we use the formulas I use in that spreadsheet to populate our table ... )

but for those who want to in-line it, go ahead and check the formulas out ... (just go to the 
Calculations tab and select "Tools->Protection->Unprotect" .. there's no password, it's just to 
prevent accidental updats)

For example, the following "properly" calculates new years in Tom's query:

trunc(dt,'y')+greatest(0,2-mod(to_number(to_char(trunc(dt,'y'), 'd')),7)),

(it just needs an adjustment ..)
(there's probably an easier way ... I haven't played with these forumlas lately .. heh)

Some of the others might need tweaking as well .. (ie July 4?)

why introduce the overhead of lots of procedural code when you can inline the process.   June 5, 2008 - 3pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

KISS - Keep it simple, stupid. 

True!!   June 5, 2008 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: Greg from Toronto

Sorry if I caused any duress Tom!!  Not my intent, just trying to help (as were you - apparently) 
..

and I do totally 100% agree with you ...

Getting specs from people is hard, as a developer in my job, I know how tough it can be, and 
strange some of the "specs" we get!!

Anyway, if it helps, your original query is useful regardless, as it shows the basic premise, and 
sometimes that is HUGE.  (if you can get the basic premise of something, you can easily modify it 
as needed based on YOUR specific requirements)  ;)

So please .. don't hide any rocks, we need ya!! :)
I have learned so much from you and this site over the last 5+ years since I found you here it 
isn't funny!  I'd hate to think where I'd be today if it wasn't for you! (probably convincing 
people that it's better to use explicit cursors, avoid bind variables, and avoid UNIONS at all 
costs!!  Eeek!! )

It is hard when you aren't paid to do somebody else's work though .. ;)

believe me, some of us understand that!