Re: Calculate working days using stored procedure
sjsyau@my-dejanews.com writes:
>
> Please help me here.
>
> Given:
>
> Today's date + Working Days
> Today's date - Working Days
>
> I need to find out the result in date format. The working days means to
> exclude weekends and holidays.
>
> Ex,
> 06-27-1998 + 10
> should = 07-10-1998
>
> Any hints?
>
> SJ
> sjsyau@yahoo.com
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Hope this will help.
######################################################################
database yourdatabase
######################################################################
main
define
xWorkingDays integer,
startDate date,
resultDate date
let xWorkingDays = 13
let startDate = today
let resultDate = thisDatePlus(startDate, xWorkingDays )
end main
#####################################################################
function thisDatePlus ( startDate, xWorkingDays )
# returning resultDate
#####################################################################
#_define_var - define local variables
define
startDate date,
xWorkingDays integer
define
a, b, c, d, f integer, # working counters
s, e date,
a_prev integer,
scratch char(256)
define
resultDate date
let a = xWorkingDays
let b = a / 5
let c = a mod 5
let d = ( b * 7 )
# adjust start date if necessary
if ( isWeekEndDay(startDate) ) then
let startDate = fallToClosestWeekDay ( startDate, "monday" )
end if
if ( isHoliday(startDate) ) then
let startDate = nextClosestWorkingDay(startDate,1)
let scratch = startDate
end if
# add weekdays to start date
let resultDate = startDate + d
case
when ( c = 0 )
when ( ( weekDay(resultDate) + c ) <= 5 )
when ( ( weekDay(resultDate) + c ) > 5 )
let c = c + 2
end case
let resultDate = resultDate + c
# determine holidays in between dates
let a = holidaysBetween ( startDate, resultDate )
if ( a = 0 ) then
return resultDate
end if
# add extra working days to account for holidays found
let resultDate = nextClosestWorkingDay ( resultDate, a )
return resultDate
end function
# thisDatePlus()
#####################################################################
function nextWeekDay ( resultDate )
# returning next business day
#####################################################################
#_define_var - define local variables
define
resultDate date
case
when ( weekday(resultDate) = 6 )
let resultDate = resultDate + 2
when ( weekday(resultDate) = 0 )
let resultDate = resultDate + 2
end case
return resultDate
end function
# nextWorkDay()
#####################################################################
function holidaysBetween ( startDate, resultDate )
# returning numberOfHolidays
#####################################################################
#_define_var - define local variables
define
startDate date,
resultDate date
define
numberOfHolidays integer
select count(*)
into numberOfHolidays
from holidays
where holiday between startDate and resultDate
and weekday(holiday) not in ( 0, 6 )
return numberOfHolidays
end function
# holidaysBetween()
#####################################################################
function isWeekEndDay ( startDate )
# returning true or false
#####################################################################
#_define_var - define local variables
define
startDate date
if (weekday(startDate) >= 1 and weekday(startDate) <= 5) then
return false
else
return true
end if
end function
# isWeekEndDay()
#####################################################################
function isHoliday ( startDate )
# returning true or false
#####################################################################
#_define_var - define local variables
define
startDate date
define
trueOrFalse int
select count(*)
into trueOrFalse
from holidays
where holiday = startDate
return trueOrFalse
end function
# isHoliday ()
#####################################################################
function fallToClosestWeekDay ( startDate, mondayOrFriday )
# returning nextWeekday
#####################################################################
#_define_var - define local variables
define
startDate date,
mondayOrFriday char(10)
define
nextWeekDayDate date
case
when ( weekDay(startDate) = 6 )
and ( mondayOrFriday = "monday" )
let nextWeekDayDate = startDate + 2
when ( weekDay(startDate) = 0 )
and ( mondayOrFriday = "monday" )
let nextWeekDayDate = startDate + 1
when ( weekDay(startDate) = 6 )
and ( mondayOrFriday = "friday" )
let nextWeekDayDate = startDate - 1
when ( weekDay(startDate) = 0 )
and ( mondayOrFriday = "friday" )
let nextWeekDayDate = startDate - 2
otherwise
let nextWeekDayDate = startDate
end case
return nextWeekDayDate
end function
# fallToClosestWeekDay()
#####################################################################
function nextClosestWorkingDay(workDate, a)
# returning closest non-WE non-Holiday Date
#####################################################################
#_define_var - define local variables
define
workDate date,
a integer
define
i integer
for i = 1 to a
let workDate = workDate + 1
while ( true )
if ( isHoliday(workDate) ) then
let workDate = workDate + 1
continue while
end if
if ( isWeekEndDay(workDate ) ) then
let workDate = workDate + 1
continue while
end if
exit while
end while
end for
return workDate
end function
# nextClosestWorkingDay()
- Martin