Who's Online Now
0 registered members (), 6 guests, and 16 spiders.
Key: Admin, Global Mod, Mod
Newest Members
NickyMcQ, dartdeena, jebaneesa, Sanju, Abbie R
1238 Registered Users
Recent Posts
Spinal Point Salary Based on input value
by delboy. 30/03/17 05:45 AM
Top Posters(30 Days)
CT 4
delboy 2
Popular Topics(Views)
385,226 PAYE RTI
46,438 HR_PF.K RUP4
Previous Thread
Next Thread
Print Thread
Rate This Thread
#2312 - 03/11/06 03:28 PM Formula to calculate absence duration  
Joined: Jun 2006
Posts: 39
rakram2 Offline
enthusiast
rakram2  Offline
enthusiast

Joined: Jun 2006
Posts: 39
Hello

I am using the BG_ABSENCE_DUATION formula (copied from TEMPLATE_ABSENCE_DURATION). However this doesn't exclude weekends when the absence duration is calculated. How would I modify the formula to exclude weekends??

#2313 - 03/11/06 09:05 PM Re: Formula to calculate absence duration [Re: rakram2]  
Joined: Mar 2005
Posts: 2,061
CT Offline
The Grand Daddy
CT  Offline
The Grand Daddy
****

Joined: Mar 2005
Posts: 2,061
People's Republic of Portsea I...
I believe there is a function GET_WORKING_DAYS, registered for use with formulas, that does exactly this.


L&K
CT

Remember: A dog is for life, not just for Christmas... unless you're in Korea
#2314 - 08/11/06 12:00 PM Re: Formula to calculate absence duration [Re: CT]  
Joined: Mar 2005
Posts: 1,279
bcooper Offline
bcooper  Offline


*****

Joined: Mar 2005
Posts: 1,279
Earth, Europe, England, here
Ah, but what is a working day?
Surely this is legislation specific. Not everybody works on a Friday, and not everybody has Sunday as a non-working day (for example)

#2315 - 08/11/06 02:33 PM Re: Formula to calculate absence duration [Re: bcooper]  
Joined: Mar 2005
Posts: 2,061
CT Offline
The Grand Daddy
CT  Offline
The Grand Daddy
****

Joined: Mar 2005
Posts: 2,061
People's Republic of Portsea I...
Firstly I lied, the function is actually called COUNT_WORKING_DAYS, and you can find it in the package HR_CAL_ABS_DUR_PKG .

Secondly, I'm not sure if what you say is right about non-working days in other countries. The basis of this function is the resolution of the function TO_CHAR(<date>,'D') the result of this is a number between 1 and 7, but I *think* that depending on certain (registry?) settings, the value for Sunday is 1 or 7 or even 6! Essentially the function only includes a date in the range as a working day if the number is between 2 and 6 inclusive. I'd like to know if anyone out there gets a value when performing that function for a Sunday, that is not 1.

However, what I have had trouble with in the past is public holidays. I seem to remember having to write a variant of the GET_TABLE_VALUE function that interrogated the user defined table 'BANK_HOLIDAYS', and didn't get upset if it found nothing for the given date/country.

The other thing I guess one might need to think about is the effect of shift patterns.

Food for thought, perhaps.

Last edited by CT; 08/11/06 02:38 PM.

L&K
CT

Remember: A dog is for life, not just for Christmas... unless you're in Korea
#2316 - 12/11/06 11:17 AM Re: Formula to calculate absence duration [Re: CT]  
Joined: Jun 2006
Posts: 39
rakram2 Offline
enthusiast
rakram2  Offline
enthusiast

Joined: Jun 2006
Posts: 39
Thanks for your response.

I have amended the BG_ABSENCE_DURATION formula as follows, adding the COUNT_WORKING_DAYS function but it still doesn't exclude weekends??


/*
FORMULA NAME: BG_ABSENCE_DURATION
FORMULA TYPE: Quickpaint
DESCRIPTION: Calculates the Employee's Absence
Duration in days or hours. The profile
'HR: Absence Duration Auto Overwrite'
determines if an existing duration value
can change automatically or not.
--
INPUTS:
- days_or_hours: the units of the absence
- date_start: the absence start date
- date_end: the absence end date
- time_start: the absence start time
- time_end: the absence end time
--
DBI Required:
- asg_start_time : the assignment start time
- asg_end_time: the assignment end time
- asg_pos_start_time: the positon start time
- asg_pos_end_time: the position end time
--
Change History
01 Sep 99 jmoyano Created
10 Oct 01 dcasemor end_day was being set to
asg_start_time. Also allowed
hours to be defaulted if no
UOM is set and hours have been
entered.
*/
/* Main Body of Formula */
INPUTS ARE days_or_hours(text),
date_start (date),
date_end (date),
time_start (text),
time_end (text)
/* default values */
DEFAULT FOR days_or_hours IS 'D'
DEFAULT FOR time_start IS '09:00'
DEFAULT FOR time_end IS '17:00'
DEFAULT FOR date_start IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR date_end IS '4712/12/31 00:00:00' (DATE)
/* database items */
DEFAULT FOR asg_start_time IS '09:00'
DEFAULT FOR asg_end_time IS '17:00'
DEFAULT FOR asg_pos_start_time IS '09:00'
DEFAULT FOR asg_pos_end_time IS '17:00'
/* local variables */
error_or_warning = ' '
invalid_msg = ' '
duration = '0'
number_of_days = 0
number_of_days_count = 0
working_days_count = '0'
first_day_hours = 0
last_day_hours = 0
/* Defaults Section */
/* default values for working day, these are only used if no
working conditions can be found */
begin_day = '09:00'
end_day = '17:00'

IF ((date_start WAS DEFAULTED) or (date_end WAS DEFAULTED)) then
duration = '0'
else
(
number_of_days_count = days_between(date_end,date_start)

working_days_count = COUNT_WORKING_DAYS(to_char(date_start,'DD-MON-YYYY'), to_char(number_of_days_count))

number_of_days = to_number(working_days_count)

/* absence in hours */
IF days_or_hours = 'H'
OR (days_or_hours WAS DEFAULTED
AND time_start WAS NOT DEFAULTED
AND time_end WAS NOT DEFAULTED) THEN
(
/* look for the assignment values*/
If ((asg_start_time WAS NOT DEFAULTED) and
(asg_end_time WAS NOT DEFAULTED)) then
(
begin_day = asg_start_time
end_day = asg_end_time
)
else
(
/* look for the position values */
if ((asg_pos_start_time WAS NOT DEFAULTED) and
(asg_pos_end_time WAS NOT DEFAULTED)) then
(
begin_day = asg_pos_start_time
end_day = asg_pos_end_time
)
)
/* compute hours per day */
hours_per_day = ((to_num(substr(end_day,1,2))*60 +
to_num(substr(end_day,4,2))) -
(to_num(substr(begin_day,1,2))*60 +
to_num(substr(begin_day,4,2)))) / 60
/* absence takes place during the same day */
IF number_of_days = 0 THEN
duration = to_char(((to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2))) -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2)))) / 60)
/* more than one day */
ELSE
(
/* Changes for bug3093970 starts here */
first_day_hours =((to_num(substr(end_day,1,2))*60 +
to_num(substr(end_day,4,2))) -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2))) ) / 60

last_day_hours = ((to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2))) -
(to_num(substr(begin_day,1,2))*60 +
to_num(substr(begin_day,4,2))))/60


if first_day_hours <=0
OR first_day_hours > hours_per_day
OR last_day_hours <= 0
OR last_day_hours > hours_per_day THEN
(
/* Leave timings are out off standard timings*/
/* So use 24 hours rule */
first_day_hours = (24*60 -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2))))/60

last_day_hours = (to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2)))/60
)
duration = to_char(first_day_hours+last_day_hours)
duration = to_char(to_num(duration) +
(DAYS_BETWEEN(date_end,date_start) - 1)* hours_per_day)
)
/* Changes for bug3093970 ends here */
)
/* absence in days */
ELSE
(
duration = to_char(DAYS_BETWEEN(date_end,date_start) + 1)
)
/* use of error messages:
if to_num(duration) = 0 then
(
duration = 'FAILED'
invalid_msg = 'HR_ABSENCE_CANNOT_BE_ZERO'
)
*/
)
return duration, invalid_msg

#2317 - 14/11/06 01:59 PM Re: Formula to calculate absence duration [Re: rakram2]  
Joined: Oct 2005
Posts: 37
simonbarrow Offline
enthusiast
simonbarrow  Offline
enthusiast

Joined: Oct 2005
Posts: 37
Manchester
Hi there,

I think you might actually be looking for the GET_WORKING_DAYS function, into which you pass the relevant start and end dates.

A good tip is to set two local variables as follows:

start_date = greatest(absence_start, period_start)
end_date = least(absence_end, period_end)

If you then pass these into the GET_WORKING_DAYS function, you will always get the number of working days in the absence, relevant to the current processing period.

Cheers

Simon


Simon Barrow
#2318 - 14/11/06 02:40 PM Re: Formula to calculate absence duration [Re: simonbarrow]  
Joined: Jun 2006
Posts: 39
rakram2 Offline
enthusiast
rakram2  Offline
enthusiast

Joined: Jun 2006
Posts: 39
Thanks

I have replaced COUNT_WORKING DAYS with GET_WORKING_DAYS as follows, but weekends are still not excluded in the absence duration calculation.

I now have:

/*
FORMULA NAME: BG_ABSENCE_DURATION
FORMULA TYPE: Quickpaint
DESCRIPTION: Calculates the Employee's Absence
Duration in days or hours. The profile
'HR: Absence Duration Auto Overwrite'
determines if an existing duration value
can change automatically or not.
--
INPUTS:
- days_or_hours: the units of the absence
- date_start: the absence start date
- date_end: the absence end date
- time_start: the absence start time
- time_end: the absence end time
--
DBI Required:
- asg_start_time : the assignment start time
- asg_end_time: the assignment end time
- asg_pos_start_time: the positon start time
- asg_pos_end_time: the position end time
--
Change History
01 Sep 99 jmoyano Created
10 Oct 01 dcasemor end_day was being set to
asg_start_time. Also allowed
hours to be defaulted if no
UOM is set and hours have been
entered.
*/
/* Main Body of Formula */
INPUTS ARE days_or_hours(text),
date_start (date),
date_end (date),
time_start (text),
time_end (text)
/* default values */
DEFAULT FOR days_or_hours IS 'D'
DEFAULT FOR time_start IS '09:00'
DEFAULT FOR time_end IS '17:00'
DEFAULT FOR date_start IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR date_end IS '4712/12/31 00:00:00' (DATE)
/* database items */
DEFAULT FOR asg_start_time IS '09:00'
DEFAULT FOR asg_end_time IS '17:00'
DEFAULT FOR asg_pos_start_time IS '09:00'
DEFAULT FOR asg_pos_end_time IS '17:00'
/* local variables */
error_or_warning = ' '
invalid_msg = ' '
duration = '0'
number_of_days = 0
get_working_days_count = 0
first_day_hours = 0
last_day_hours = 0
/* Defaults Section */
/* default values for working day, these are only used if no
working conditions can be found */
begin_day = '09:00'
end_day = '17:00'

IF ((date_start WAS DEFAULTED) or (date_end WAS DEFAULTED)) then
duration = '0'
else
(

get_working_days_count = GET_WORKING_DAYS(date_start, date_end)

number_of_days = get_working_days_count

/* absence in hours */
IF days_or_hours = 'H'
OR (days_or_hours WAS DEFAULTED
AND time_start WAS NOT DEFAULTED
AND time_end WAS NOT DEFAULTED) THEN
(
/* look for the assignment values*/
If ((asg_start_time WAS NOT DEFAULTED) and
(asg_end_time WAS NOT DEFAULTED)) then
(
begin_day = asg_start_time
end_day = asg_end_time
)
else
(
/* look for the position values */
if ((asg_pos_start_time WAS NOT DEFAULTED) and
(asg_pos_end_time WAS NOT DEFAULTED)) then
(
begin_day = asg_pos_start_time
end_day = asg_pos_end_time
)
)
/* compute hours per day */
hours_per_day = ((to_num(substr(end_day,1,2))*60 +
to_num(substr(end_day,4,2))) -
(to_num(substr(begin_day,1,2))*60 +
to_num(substr(begin_day,4,2)))) / 60
/* absence takes place during the same day */
IF number_of_days = 0 THEN
duration = to_char(((to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2))) -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2)))) / 60)
/* more than one day */
ELSE
(
/* Changes for bug3093970 starts here */
first_day_hours =((to_num(substr(end_day,1,2))*60 +
to_num(substr(end_day,4,2))) -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2))) ) / 60

last_day_hours = ((to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2))) -
(to_num(substr(begin_day,1,2))*60 +
to_num(substr(begin_day,4,2))))/60


if first_day_hours <=0
OR first_day_hours > hours_per_day
OR last_day_hours <= 0
OR last_day_hours > hours_per_day THEN
(
/* Leave timings are out off standard timings*/
/* So use 24 hours rule */
first_day_hours = (24*60 -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2))))/60

last_day_hours = (to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2)))/60
)
duration = to_char(first_day_hours+last_day_hours)
duration = to_char(to_num(duration) +
(DAYS_BETWEEN(date_end,date_start) - 1)* hours_per_day)
)
/* Changes for bug3093970 ends here */
)
/* absence in days */
ELSE
(
duration = to_char(DAYS_BETWEEN(date_end,date_start) + 1)
)
/* use of error messages:
if to_num(duration) = 0 then
(
duration = 'FAILED'
invalid_msg = 'HR_ABSENCE_CANNOT_BE_ZERO'
)
*/
)
return duration, invalid_msg

#2319 - 14/11/06 03:14 PM Re: Formula to calculate absence duration [Re: rakram2]  
Joined: Oct 2005
Posts: 37
simonbarrow Offline
enthusiast
simonbarrow  Offline
enthusiast

Joined: Oct 2005
Posts: 37
Manchester
Hello there

The above code doesn't actually return the result of the get_working_days function, it simply populates get_working_days_count with the outcome, and then carries on using the seeded calcs.

A good start would be to change the final criteria clause:

/* absence in days */
ELSE
(
duration = to_char(DAYS_BETWEEN(date_end,date_start) + 1)
)
/* use of error messages:
if to_num(duration) = 0 then
(
duration = 'FAILED'
invalid_msg = 'HR_ABSENCE_CANNOT_BE_ZERO'
)
*/
)


to the following:


/* absence in days */
ELSE
(
duration = to_char(GET_WORKING_DAYS(date_start,date_end) + 1)
)
/* use of error messages:
if to_num(duration) = 0 then
(
duration = 'FAILED'
invalid_msg = 'HR_ABSENCE_CANNOT_BE_ZERO'
)
*/
)


Note that the order of the input values has changed to what is required in the formula function definition.

You will also need to go through the rest of the criteria and get rid of any that will never be satisfied, and change the rest to use the function..

Absences can be pretty tricky, and often using the formulae created by the wizards can produce a whole lot of code which may not be useful to your particular scenario. If your absence requirement is fairly simple, then I would consider writing the formula yourself- again, this can be a lot less confusing than understanding and editing an exising one..

Cheers

Simon


Simon Barrow
#2320 - 15/11/06 03:17 PM Re: Formula to calculate absence duration [Re: simonbarrow]  
Joined: Jun 2006
Posts: 39
rakram2 Offline
enthusiast
rakram2  Offline
enthusiast

Joined: Jun 2006
Posts: 39
Hello Again

You will not be surprised when I tell you it still doesn't work! We are using hours rather then days so I now have:

/*
FORMULA NAME: BG_ABSENCE_DURATION
FORMULA TYPE: Quickpaint
DESCRIPTION: Calculates the Employee's Absence
Duration in days or hours. The profile
'HR: Absence Duration Auto Overwrite'
determines if an existing duration value
can change automatically or not.
--
INPUTS:
- days_or_hours: the units of the absence
- date_start: the absence start date
- date_end: the absence end date
- time_start: the absence start time
- time_end: the absence end time
--
DBI Required:
- asg_start_time : the assignment start time
- asg_end_time: the assignment end time
- asg_pos_start_time: the positon start time
- asg_pos_end_time: the position end time
--
Change History
01 Sep 99 jmoyano Created
10 Oct 01 dcasemor end_day was being set to
asg_start_time. Also allowed
hours to be defaulted if no
UOM is set and hours have been
entered.
*/
/* Main Body of Formula */
INPUTS ARE days_or_hours(text),
date_start (date),
date_end (date),
time_start (text),
time_end (text)
/* default values */
DEFAULT FOR days_or_hours IS 'D'
DEFAULT FOR time_start IS '09:00'
DEFAULT FOR time_end IS '17:00'
DEFAULT FOR date_start IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR date_end IS '4712/12/31 00:00:00' (DATE)
/* database items */
DEFAULT FOR asg_start_time IS '09:00'
DEFAULT FOR asg_end_time IS '17:00'
DEFAULT FOR asg_pos_start_time IS '09:00'
DEFAULT FOR asg_pos_end_time IS '17:00'
/* local variables */
error_or_warning = ' '
invalid_msg = ' '
duration = '0'
number_of_days = 0
get_working_days_count = 0
first_day_hours = 0
last_day_hours = 0
/* Defaults Section */
/* default values for working day, these are only used if no
working conditions can be found */
begin_day = '09:00'
end_day = '17:00'

IF ((date_start WAS DEFAULTED) or (date_end WAS DEFAULTED)) then
duration = '0'
else
(

get_working_days_count = GET_WORKING_DAYS(date_start, date_end)

number_of_days = get_working_days_count

/* absence in hours */
IF days_or_hours = 'H'
OR (days_or_hours WAS DEFAULTED
AND time_start WAS NOT DEFAULTED
AND time_end WAS NOT DEFAULTED) THEN
(
/* look for the assignment values*/
If ((asg_start_time WAS NOT DEFAULTED) and
(asg_end_time WAS NOT DEFAULTED)) then
(
begin_day = asg_start_time
end_day = asg_end_time
)
else
(
/* look for the position values */
if ((asg_pos_start_time WAS NOT DEFAULTED) and
(asg_pos_end_time WAS NOT DEFAULTED)) then
(
begin_day = asg_pos_start_time
end_day = asg_pos_end_time
)
)
/* compute hours per day */
hours_per_day = ((to_num(substr(end_day,1,2))*60 +
to_num(substr(end_day,4,2))) -
(to_num(substr(begin_day,1,2))*60 +
to_num(substr(begin_day,4,2)))) / 60
/* absence takes place during the same day */
IF number_of_days = 0 THEN
duration = to_char(((to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2))) -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2)))) / 60)
/* more than one day */
ELSE
(
/* Changes for bug3093970 starts here */
first_day_hours =((to_num(substr(end_day,1,2))*60 +
to_num(substr(end_day,4,2))) -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2))) ) / 60

last_day_hours = ((to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2))) -
(to_num(substr(begin_day,1,2))*60 +
to_num(substr(begin_day,4,2))))/60


if first_day_hours <=0
OR first_day_hours > hours_per_day
OR last_day_hours <= 0
OR last_day_hours > hours_per_day THEN
(
/* Leave timings are out off standard timings*/
/* So use 24 hours rule */
first_day_hours = (24*60 -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2))))/60

last_day_hours = (to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2)))/60
)
duration = to_char(first_day_hours+last_day_hours)
duration = to_char(to_num(duration) +
(GET_WORKING_DAYS(date_start,date_end) - 1)* hours_per_day)
)
/* Changes for bug3093970 ends here */
)
/* absence in days */
ELSE
(
duration = to_char(DAYS_BETWEEN(date_end,date_start) + 1)
)
/* use of error messages:
if to_num(duration) = 0 then
(
duration = 'FAILED'
invalid_msg = 'HR_ABSENCE_CANNOT_BE_ZERO'
)
*/
)
return duration, invalid_msg


Moderated by  CT, delboy 

Forum Statistics
Forums48
Topics2,167
Posts11,720
Members1,239
Most Online90
Mar 5th, 2017
Today's Birthdays
No Birthdays
Recent vacancies
Top Posters(All Time)
CT 2,061
bcooper 1,279
delboy 1,217
paulgos 423
SBi 412
Gus 252
pat 245
vkumar 223
April
S M T W T F S
1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30
Powered by UBB.threads™ PHP Forum Software 7.6.0
Page Time: 0.026s Queries: 15 (0.007s) Memory: 2.8026 MB (Peak: 2.9958 MB) Zlib disabled. Server Time: 2017-04-25 14:24:04 UTC