on 01-12-2015 7:10 AM
Hi,
I want to know is there any custom function somebody had created to find the number of business days between two dates.
Business days = Days excluding Weekends, holidays are not involved.
Any help will be appreciated.
Thanks & Regards
Babin A B
Hi Babin ,
Please test this function !
---------------------------------------------------
$PBExportHeader$date-work-days.srf
$PBExportComments$Ime dneva
global type date-work-days from function_object
end type
forward prototypes
global function long date-work-days (ref date ad_begdate, ref date ad_enddate)
end prototypes
global function long date-work-days (ref date ad_begdate, ref date ad_enddate);// Function:DATE-WORK-DAYS
// Argument: 1.ad_BegDate = From date
// 2.ad_EndDate = To date
// Return:Number of work days (Long)
//
// Calling:
// ll_Work_Days=DATE-WORK-DAYS(BegDate,EndDate)
Long ll_Days,ll_Work,ll_Week
IF IsNull(ad_BegDate) OR IsNull(ad_EndDate) THEN
Return ll_Work
END IF
ll_Days=DaysAfter(ad_BegDate,ad_EndDate)+1
IF DayNumber(ad_BegDate)=1 THEN
ll_Days=ll_Days -1
ELSEIF DayNumber(ad_BegDate)=7 THEN
ll_Days=ll_Days -2
END IF
IF DayNumber(ad_EndDate)=7 THEN
ll_Days=ll_Days -1
ELSEIF DayNumber(ad_EndDate)=1 THEN
ll_Days=ll_Days -2
END IF
ll_Week=ll_Days/7
ll_Work=ll_Days -ll_Week*2
IF ll_Work<0 THEN
ll_Work=0
END IF
Return ll_Work
end function
----------------
Regards
Jure Gornjak
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
If you need to factor in Holidays (and these obviously vary by country), the solution we used was to create a database table.
CREATE table business_calendar (
country_code smallint not null,
calendar_date date not null,
is_holiday smallint not null default 0,
primary key (country_code, calendar_date) );
Then the query to answer your question would be:
SELECT sum(( 1 - is_holiday ) * (CASE when daynumber <= 5 then 1 else 0 end)) as business_days
from business_calendar
where country_code = :country
and calendar_date between :start_date and :end_date ;
If a date is a business work day (daynumber 1 thru 5) and NOT a holiday, then the expression evaluates to 1. Any other combination goes to 0 and is excluded from the sum().
-Paul-
Another way of doing it:
public function integer of_businessdays (date ad_from, date ad_to);
date ld1, ld2
integer li_dn1,li_dn2, li_daysback, li_daysforward, li_weeks, li_nowork, li_withwork
// back the beginning date to the nearest Sunday - This is to form the date to the exact week range
li_dn1 = dayNumber(ad_from)
li_daysback = li_dn1 - 1
ld1 = RelativeDate(ad_from, -li_daysback)
// Forward the end date to the nearest Saturday - This is to form the date to the exact week range
li_dn2 = dayNumber(ad_to)
li_daysforward = 7 - li_dn2
ld2 = RelativeDate(ad_to, li_daysforward)
// Subtract 1 to exclude sunday, if the starting date is not sunday.
If li_daysback <> 0 Then li_daysback = li_daysback - 1
// Subtract 1 to exclude saturday, if the end date is not saturday
If li_daysforward <> 0 Then li_daysforward = li_daysforward - 1
// Determine the number of weeks in the range.
li_weeks = (daysAfter(ld1,ld2) + 1) /7
// 2 non working days per week
li_nowork = li_weeks * 2
// Compute the number of days in the range, less the non working days, less the days you back off to Sunday
// Less the days you forward to Saturday, plus 1 because daysAfter starts counting after the start date.
li_withwork = daysAfter(ld1,ld2) - li_daysback - li_daysforward - li_nowork + 1
Return li_withwork
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
public function integer bizdaysbetween (date adfrom, date adto);
integer liDate, liTotal, liBiz, liXcept
date ldTest
date ldNotBiz []
ldNotBiz = { 2015-01-01 , 2015-01-26, 2015-04-03, 2015-04-06 } //hard coded
liTotal = daysAfter ( adFrom , adTo )
for liDate = 1 to liTotal
ldTest = relativeDate ( adFrom , liDate )
if dayNumber ( ldTest ) > 1 and dayNumber ( ldTest ) < 7 then
for liXcept = 1 to UpperBound ( ldNotBiz[] )
if ldTest = ldNotBiz [ liXcept ] then
liBiz --
EXIT
end if
next
liBiz++
end if
next
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Here's an SQL solution...
http://stackoverflow.com/questions/6425255/return-number-of-weekdays-between-2-dates-in-t-sql
hth,
Mark
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Note that Jure's solution assumes that the start date is <= end date. I would suggest adding logic after the isNull() checks to enforce this additional rule.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Like this
$PBExportHeader$date-work-days.srf
$PBExportComments$Ime dneva
global type date-work-days from function_object
end type
forward prototypes
global function long date-work-days (ref date ad_begdate, ref date ad_enddate)
end prototypes
global function long date-work-days (ref date ad_begdate, ref date ad_enddate);// Function:DATE-WORK-DAYS
// Argument: 1.ad_BegDate = From date
// 2.ad_EndDate = To date
// Return:Number of work days (Long)
//
// Calling:
// ll_Work_Days=DATE-WORK-DAYS(BegDate,EndDate)
Long ll_Days,ll_Work,ll_Week
IF IsNull(ad_BegDate) OR IsNull(ad_EndDate) OR ad_BegDate>ad_EndDate THEN
ll_Work=0
ELSE
ll_Days=DaysAfter(ad_BegDate,ad_EndDate)+1
IF DayNumber(ad_BegDate)=1 THEN
ll_Days=ll_Days -1
ELSEIF DayNumber(ad_BegDate)=7 THEN
ll_Days=ll_Days -2
END IF
IF DayNumber(ad_EndDate)=7 THEN
ll_Days=ll_Days -1
ELSEIF DayNumber(ad_EndDate)=1 THEN
ll_Days=ll_Days -2
END IF
ll_Week=ll_Days/7
ll_Work=ll_Days -ll_Week*2
IF ll_Work<0 THEN
ll_Work=0
END IF
END IF
Return ll_Work
end function
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.