Help Desk Code - Need Help...

[proflola]

Here's the scenario....

Our Helpdesk needs to create reports that calculate the duration of a call in HR:MM:SS, however it can only include our working hours minus weekends. Our work day is from 7:00 Am - 5:30 PM. Rosemary from this Forum was kind enough to help me with a solution. It works great however some of the data is inaccurate. I'll post the formula below and in my next post, I'll show you the result.
------

DatetimeVar FDay:= cdate({CallLog.RecvdDate}) + ctime({CallLog.recvdTime})
;
DatetimeVar LDay:= if isnull({CallLog.ClosedDate})
then currentdatetime
else if {CallLog.ClosedDate} = ""
then currentdatetime
else Cdate({CallLog.ClosedDate}) + ctime({CallLog.ClosedTime});


TimeVar BusinessStartTime:= time(7,00,0); //ENTER START TIME;
//example for 7:30 AM: time(7,30,0)
TimeVar BusinessEndTime:= time(17,0,0); //ENTER Business Day END TIME;
//example for 5:00 PM: time(17,0,0)
NumberVar BSTime;
NumberVar BETime;
Numbervar Days;
Numbervar Weekends;
Numbervar Finaldays;
DateVar StartDate;
DateVar EndDate;
NumberVar halfdays;
NumberVar fulldays;
NumberVar hours;
//If first day or last day is null or less than null assign hours to 0 and finish the formula
if FDay <= Date(0,0,0) or LDay <=Date(0,0,0) then
hours:= 0
else if
//If call opened outside business hours then straight time ... remove if not what you want.

(time(fday) > BusinessEndTime and time(lday) > BusinessEndTime and ((dayofweek(fday) = dayofweek(lday))
or dayofweek(fday) = [6] and datediff("d",fday,lday) <= 2)
)
then
hours:= datediff("s",fday, lday) /3600

//end new section
//Else assign hours
else (
//Assign Business Start Time and Business End Time
BSTime:= hour(BusinessStartTime) +
(minute(BusinessStartTime) +second(BusinessStartTime));
BETime:= hour(BusinessEndTime) + (minute(BusinessEndTime)+second(BusinessEndTime)) ;

//Assign First Day and Last Day
//Determine whether FDay falls within
//start time and end time

if hour(FDay) in BSTime to BETime then
FDay:= FDay
else if hour(FDay) > BETime then
FDay:= datetime(date(FDay), BusinessEndTime)
else if hour(FDay) < BSTime then
FDay:= datetime(date(FDay), BusinessStartTime);
//Determine whether LDay falls within start
//time and end time
if hour(LDay) in BSTime to BETime then
LDay:= LDay
else if hour(LDay) > BETime then
LDay:= datetime(date(LDay), BusinessEndTime)
else if hour(LDay) < BSTime then
LDay:= datetime(date(LDay), BusinessStartTime);


//Assign StartDate and EndDate
//if the first day falls on a weekend,
//StartDate is equal to the following Monday
//for calculation reasons
If DayOfWeek(FDay) = 7 Then
StartDate := date(FDay) + 2
Else
If DayOfWeek(FDay) = 1 Then
StartDate := date(FDay) + 1
Else
StartDate:=date(FDay);
//if the last day falls on a weekend,
//EndDate is equal to the following Monday
//for calculation reasons
If DayOfWeek(LDay) = 7 Then
EndDate := date(LDay) + 2
Else If DayOfWeek(LDay) = 1 Then
EndDate := date(LDay) + 1
Else
EndDate := date(LDay);
//Calculate days (including First day and Last day)
Days:= (EndDate - StartDate)+1;
//Calculate Weekends
if Days >= 7 then
WeekEnds := (Round((Days/7),0))*2
else if DayOfWeek(StartDate) > DayOfWeek(EndDate) then
WeekEnds := 2
else
WeekEnds := 0;

//Calculate FinalDays
//If the Last Day is on a weekend then
//FinalDays subtract the weekend days

If DayOfWeek(LDay) = 7 then
FinalDays:= FinalDays - 1;
If DayOfWeek(LDay) = 1 then
FinalDays:= FinalDays - 2;
//Assign FinalDays to Days - Weekends
FinalDays:= Days - WeekEnds;
//Calculate Hours
//If there is less than 2 days, hours = last
//days hour - first days hour
If FinalDays <= 1 then
(if hour(LDay) <= BETime then
hours:= (time(LDay) - time(FDay))/3600
else
hours:= (BusinessEndTime - time(FDay))/3600)

//Else hours = how many hours on the two half
//days + how many hours for the full days
Else (
halfdays:= ((BusinessEndTime - time(FDay)) /3600 +
(time(LDay) - BusinessStartTime)
/3600);
fulldays:= (FinalDays-2) * 9;
hours:= halfdays + fulldays);
) ; hours

 

[proflola]

Here's a sample of the result:

CallID Rcvd Date/Time ClDate/Time Duration
1234 8/23/2006 3:58:18AM 8/23/2006 9:34:39AM 2:34:39
2345 8/1/2006 6:22:56PM 8/2/2006 8:29:27AM 24:29:42

You can see that the second instance doesn't work. The calculation *should* omit any time after 5:30 PM, so the actual duration would be 1:29:27. You can see how it really scews our ability to track the actual duration for the call.

Thanks again for any help you can give.

SJD

 

[Madawc]

Do a test version of the report, displaying the values for all of the intermediate steps. That way, you'll see just what's gone wrong.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10