-
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
-
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.
-
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.
-
To simplify the process, include Sat and Sun in the holiday table.
-
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 ;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|