Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Need help with calculating activity time Post Reply Post New Topic
Author Message
jennifer_falcon
Newbie
Newbie
Avatar

Joined: 15 Jan 2009
Online Status: Offline
Posts: 35
Quote jennifer_falcon Replybullet Topic: Need help with calculating activity time
     Posted: 14 Oct 2009 at 9:06am

Good Day,

I am working with Crystal Reports 9 and DB2 8.5. I connect to the database through an ODBC driver.

I have a list of activities and their date/time in an incident. The information can look like this:

Date/Time                   Type
01/09/09 10:40:53     Open
01/09/09 10:45:43     Pending Customer
01/09/09 10:50:43     Resolved
01/09/09 11:02:00     Resolved
01/09/09 12:00:00     Closed
 
I need to be able to calculate the working time that this incident was worked on. For our organization, it would mean the time the ticket was opened until the time it was first resolved. If a ticket is in a Pending Customer status, the working time should not accumulate. The timer should stop for the period of time the ticket was in the Pending Customer state.
 
In theory, the calculation should be something like:
Show me the time from when the incident was opened to when it was FIRST resolved.
Then, subtract the amount of time the incident was in the Pending Customer status.
Then, give me the total time the ticket was worked on.
 
I need to be able to calculate this information based on the FIRST time the incident was Resolved. Plus, I need to be able to take into account a 9 business hour day.
 
Any ideas?
 
Not every incident will have a Pending Customer status. Some incidents may just go from Opened to Resolved.
 
Thanks!
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 15 Oct 2009 at 6:31am
shared numbervar working;
shared booleanvar resolved;
shared stringvar lastType;
 
if not resolved then
  if lastType <> "Customer Pending" then
    working := working + datediff(s, Previous({table.dateTime}), {table.dateTime});
 
lastType = {table.Type};
if {table.Type} = "Resolved" then
  resolved := true;
 
""  //hide the output of the formula
 
this will give you the time the ticket was worked on in seconds.  It assumes that the in the example the time between Open and Pending Customer is valid as work time and that from Pending to Resolved is not.
 
To display the amount of time worked you will need further logic to account for when you are closed, weekends(if this is needed) and to convert the number of seconds to days:hours:minutes:seconds.
 
Hopefully this is a step in the right direction.
IP IP Logged
Jyothi Yepuri
Senior Member
Senior Member


Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
Quote Jyothi Yepuri Replybullet Posted: 15 Oct 2009 at 4:19pm
Below is the formula to find time between ticket open to resolved which only considers business hours,weekdays...try same formula for pending customer to resolved and subtract that value from this value.


DATETIMEVAR StDate:= {Ticket open dareTime};
DATETIMEVAR EndDate:= {first resolved datetime};
NUMBERVAR Weeks;
NUMBERVAR Days;

// Business hours start and end on each day
TIMEVAR SLA_Open := TIME(8,0,0);
TIMEVAR SLA_Close := TIME(17,30,0);

NumberVar WeekendTime ;
NUMBERVAR NonWorkTime ;
NUMBERVAR Weeks;
NUMBERVAR Days;


IF WeekDayName(DAYOFWEEK(StDate)) = "Saturday" THEN
   StDate:= DATETIMEVALUE(DATE(DATEADD('D',2,StDate)) , SLA_Open);

IF WeekDayName(DAYOFWEEK(StDate)) = "Sunday" THEN
   StDate:= DATETIMEVALUE(DATE(DATEADD('D',1,StDate)) , SLA_Open);

IF TIME(StDate) > SLA_Close   THEN
    StDate := DATETIMEVALUE(DATE(StDate) , SLA_Close);
    
IF TIME(StDate) < SLA_Open THEN
    StDate := DATETIMEVALUE(DATE(StDate) , SLA_Open);


IF WeekDayName(DAYOFWEEK(endDate)) = "Saturday" THEN
endDate = DATETIMEVALUE(DATE(DATEADD('D',2,endDate)) , SLA_Open);

IF WeekDayName(DAYOFWEEK(endDate)) = "Sunday" THEN
endDate = DATETIMEVALUE(DATE(DATEADD('D',1,endDate)) , SLA_Open);

IF TIME(endDate) > SLA_Close   THEN
    endDate := DATETIMEVALUE(DATE(endDate) , SLA_Close);
    
IF TIME(endDate) < SLA_Open THEN
    endDate := DATETIMEVALUE(DATE(endDate) , SLA_Open);

Weeks:= (Truncate (EndDate - dayofWeek(EndDate) + 1 - (StDate - dayofWeek(StDate) + 1)) /7 ) * 5;

Days := DayOfWeek(EndDate) - DayOfWeek(StDate) + (if DayOfWeek(StDate) = 1 then -1 else 0) +
                                                  (if DayOfWeek(EndDate) = 7 then -1 else 0);   

// Non Worktime on Business days
NonWorkTime := DATEDIFF("N",DATETIMEVALUE(CurrentDate, SLA_Close),DATETIMEVALUE(CurrentDate+1,SLA_Open)) * (Weeks + Days);


//a weekend in minutes is Count of saturdays and sundays   * 24 hours * 60 minutes
WeekendTime := (DateDiff("ww",stDate,Enddate, crSaturday ) +DateDiff("ww",stDate,Enddate, crSunday)) * 24 * 60;

(DATEDIFF('N', stDATE, endDate)- NonWorkTime - WeekendTime )/60


Hope you find this useful,
Jyothi

IP IP Logged
jennifer_falcon
Newbie
Newbie
Avatar

Joined: 15 Jan 2009
Online Status: Offline
Posts: 35
Quote jennifer_falcon Replybullet Posted: 16 Oct 2009 at 2:20pm
Good Day. I am encountering a few issues I don't know how to deal with.
 
I have my report looking like this:
 
Type                    Date                           Indicator                         #Seconds
Open                   10/06/09 10:53
Alert                    10/06/09  10:58            False                               334
Alert2                10/06/09 11:28 AM         False                               2135
Alert3                10/06/09 11:58              False                                3936
Resolved           10/06/09 3:20 PM          True                                  0
Update              10/07/09 9:53 AM          False                                3936
Assignment       10/07/09 9:55 AM          False                                3936
 
Once the report hits the True, it does not continue to accumulate the #Seconds field. It stops doing anything. There are other status' that may appear after the Resolved I need to worry about. How can I get the number of seconds to continue calculating?
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 16 Oct 2009 at 2:26pm
modify the if that sets the resolved := true to have an else and set resolved := false
 
The original description said to stop accumulating at the first Resolve...
 
HTH
IP IP Logged
jennifer_falcon
Newbie
Newbie
Avatar

Joined: 15 Jan 2009
Online Status: Offline
Posts: 35
Quote jennifer_falcon Replybullet Posted: 16 Oct 2009 at 2:38pm
Sometimes I could just jump for joy!  It is now accumulating!
Sorry about my original description. The requirements for my report changed since I originally asked the question.
I will now see if I can get the rest of the report to work. Thanks!!!!!!!
IP IP Logged
jennifer_falcon
Newbie
Newbie
Avatar

Joined: 15 Jan 2009
Online Status: Offline
Posts: 35
Quote jennifer_falcon Replybullet Posted: 19 Oct 2009 at 2:40pm
Good Day,
 
I have one last issue to deal with before my report is complete.
I am trying to calculate the business hours for each incident starting from the first Open Activity timestamp. I can get the business hours to work for each activity row, but I cannot get them to Sum properly.
 
For example:
 
I have incident IM96917.
 
Type              DateStamp                    Business Seconds         Sum
Open            30/09/2009 12:03           (this is blank)              (this is blank)
Alert             30/09/2009 12:03           2 (seconds)                 2.00
Alert             30/09/2009 12:33          1814                            1816
Alert             30/09/2009  13:03         1815                             3631
 
there are more records....the last record for this incident is this:
Closed         01/10/2009 7:48AM        7807                             17807
 
The total time this incident was worked on was 17807 seconds.
 
My calculations work perfectly for this very first incident.
Subsequent incidents are not totalling properly.
 
The next incident displays this:
 
Type              DateStamp                    Business Seconds         Sum
Open             30/09/2009 12:06         -17589                          218
Alert              30/09/2009 12:07         26                                  244
 
There are more records...the last record looks like this:
Closed           30/09/2009                    275                              6270
 
On every subsequent incident, it looks like the report is calculating the first incident datestamp - the datestamp of the second incidents' open datestamp.
Then, the third incident has the calculation (first incident datestamp - the third incidents open datestamp)
 
I don't know how to reset the counter for each of the subsequent incidents.
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 20 Oct 2009 at 5:41am
usually in the group header I place a formula like:
shared numbervar working := 0
 
everytime the group changes (which in you case is incident) the values would be set to 0.
 
HTH
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.