Date calculations

[rblaylock]

I am not a developer, I am a DBA but this can probably best be answered
by a developer.

I am attempting to set up a report that does a date calculation. For example,
a work order that needs to have 15 working days added to it for a required
completion date.

Is there a way to add, for example, 15 working days to a start date to obtain
a completion date? Does anyone have any code that would provide an example
for me?

TIA,

Ron Blaylock
rblaylock@mail.arc.nasa.gov

[tamilselvan]

I did it my previous project. I write the logic. It is better you develop your skill in writing PL/SQL procedure and function. Sooner or later it will help you. You do not need to depend upon others.

You need a hoilday table in which all hoildays are stored.

Set the starting date to STDATE
Set a counter to 15 (say Kount=15)
Do while kount > 0
STDATE = STDATE+1
if STDATE is not found in hoilday table (you need to write SQL)
KOUNT = KOUNT - 1
end if
End do while

The new STDATE will be the ending date for the work order.

[rblaylock]

Thanks for the info. That is what I figured but I'm having troubles getting the
date to convert in and out for my calculations. I also need to eliminate Saturday
and Sunday as well as holidays. I know sql fairly well but I am not using Oracle
so much anymore as I changed jobs and spend most of my time in Sybase
and on the mainframe.

I'll give your idea a shot and see if I can also calculate and remove weekends.

Best regards,

Ron B.

[tamilselvan]

To simplify the process, include Sat and Sun in the holiday table.

[Victoria]

This code will give you the 15th Working day from Today. Change 15 to something else If you please. To implement the holidays, just add the where clause
" where nextday not in ( select holiday from holiday_table ) "


Declare
today date := sysdate ;
nextday date ;
p_business_days number := 0 ;
Begin
nextday := today ;
while p_business_days <> 15
loop
select
1 + nextday - today -
((TRUNC(nextday,'D') - TRUNC(today,'D'))/7)*2
+ DECODE(to_char(nextday,'D'),7,-1,0)
+ DECODE(to_char(today,'D'),1,-1,0) Business_Days
into p_business_days
from dual ;
nextday := nextday + 1 ;
end loop;
dbms_output.put_line(nextday) ;
end ;