average time 1

[jschill2628]

I have three date fields and three time fields. I need to create a chart that shows the average amount of time spent on an incident.

Fields:
Dispatch Date: Misc Date 9
Dispatch Time: SA215
Officer Arrival Date: Misc Date 10
Officer Arrival Time: SA216
Officer Completion Date: Misc Date 11
Officer Completion Time: SA217

I have two problems
- One I am not sure how to create a formula that would give me the average time.
- Second, what if it takes an officer three days to complete a task, how do I get a time from that?

Any help would be appreciated. I am working in Crystal XI. Thanks in advance!

 

[lbass]

Are the date and time fields actually of date and time datatype? If you right click on the fields->browse or run the mouse over the fields, what datatypes are displayed?

-LB

 

[jschill2628]

If I run over the Date(s) they are Date Type.
If I run over Time(s) it is a String Type.
The times are also reported in military time if that helps. Thank you!

 

[lbass]

Please show samples of how the time field displays.

-LB

 

[fisheromacse]

1. What is the total response time that you need to calculate in order to get your average?

Is it from the time of call to officer arrival? and again from officer arrival to completion? or from time of call to completion? or from officer arrival to completion?

Once these values are known it should be a matter of using converting to datetime, calculation of the datediff, then calculation of averages.


2. you can convert the date difference into days + hours + minutes, or can leave as the total # of minutes/hours/seconds/etc.

 

[fisheromacse]

1. What is the total response time that you need to calculate in order to get your average?

Is it from the time of call to officer arrival? and again from officer arrival to completion? or from time of call to completion? or from officer arrival to completion?

Once these values are known it should be a matter of using converting to datetime, calculation of the datediff, then calculation of averages.

How exactly is the time data displayed?


2. you can convert the date difference into days + hours + minutes, or can leave as the total # of minutes/hours/seconds/etc.

 

[jschill2628]

@LB
Time is just a four digit number like 1010, or 2319.

@fisheromacse
1. from dispatch time to officer completion time
or if I can I could give them several graphs such as dispatch time to officer arrival time, and then officer arrival time to officer completion time
so one major one, or three total.

2.I figured out how to put the date and the time together
DateDiff ({@Dispatch Date & Time}, , )
DateDiff ({@Officer Arrival Date & Time}, , )
DateDiff ({@Officaer Completion Date & Time}, , )

but I don’t know the start time, or end times., so I am not sure how to use datediff

 

[lbass]

How did you set up the datetimes?

To use datediff for the dispatch to arrival difference, use the following:

datediff("n",{@DispatchDatetime},{@OfficerArrivalDAtetime})

This will return the number of minutes. You can then right click on the formula and insert an average at a group and/or report level. You can adapt this to the other time differences, also.

If you then want to convert the differences back into a string, refer to faq767-3543.

-LB

 

[fisheromacse]

At the details level, this would give you the number of minutes between the dispatch time and the completion time.
(Also, i simplified the names of your formulas in my example...easier on my old eyes not seeing the '&')

{@ETime}
DateDiff ('n',{@DispatchDateTime},{@OfficaerCompletionDateTime})

You would then use the results of the above formula in another formula in the report footer to calculate the average # of minutes by dividing {@ETime} by the # of completed records.


I don't have Crystal in front of me right now, so hope i have the syntax/etc straight.

 

[jschill2628]

I combined the Date and time:
//Dispatch Date & Time
Totext ({IRSCLAIM.MISC9_DT}& " "&{IRSCLAIMSA1.SA215})

which I don't believe is correct, becuase in the above formula:

datediff('n',{@Dispatch Date & Time},{@Officer Completion Date & Time})

is giving me an error, highlighting {@Dispatch Date & Time}, and saying that I need a date.

I understand the concept of this above formula, but I want to "combine date and time, and then get the difference, and then the average. If that makes scense.

 

[lbass]

Your formula should look like this for the diff between discharge and arrival time (plug in the correct fields for arrival):

stringvar dx := {IRSCLAIMSA1.SA215};
timevar dy := time(val(left(dx,2)),val(right(dx,2)),0);
datetime dz := datetime({IRSCLAIM.MISC9_DT},dy);
stringvar ax := {table.arrivaltime};
timevar ay := time(val(left(ax,2)),val(right(ax,2)),0);
datetime az := datetime({table.arrivaldate},ay);
datediff("n",ax,ay);

-LB

 

[jschill2628]

This is wonderful! and I do understand how it works, except it is highlighting the third line, the second datetime and the remainder of the formula and saying that "the remaining text does not seem to be part of the formula"

stringvar dx := {IRSCLAIMSA1.SA215};
timevar dy := time(val(left(dx,2)),val(right(dx,2)),0);
datetime dz := datetime({IRSCLAIM.MISC9_DT},dy);
stringvar ax := {IRSCLAIMSA1.SA216};
timevar ay := time(val(left(ax,2)),val(right(ax,2)),0);
datetime az := datetime({IRSCLAIM.MISC10_DT},ay);
datediff("n",dz,az);

Thank you!

 

[lbass]

Sorry, should have been "datetimevar dz" at the beginning of the line. Same with "datetimevar az".

-LB

 

[jschill2628]

PERFECT!! Thank you so much!!

 

[bluraz]

Average datediff for Report Footer:

Crystal XI
SQL 2005

I am running into a roadblock trying to calculate
the average datediff in the report footer. And am
hoping for some assistance to do so.

I am successful calculating the average datediff in
Group Footer 1 using the following....

I am currently calculating the datediff:
datediff ('s', {@shared_ArrShip}, {@shared_DepShip}) / 3600

My 'shared' formulas are coded this way, because I have them displayed in my GF2 (by Bill Number)...

{@shared_ArrShip}:
global datetimeVar ArrDate;
if {ZZ_ARRIVE_SHIP_VS_DEPART_SHIP;1.STATUS_CODE} = "ARRSHIP" then
ArrDate := {ZZ_ARRIVE_SHIP_VS_DEPART_SHIP;1.CHANGED};
ArrDate;

{@shared_DepShip}:
global datetimeVar DepDate;
if {ZZ_ARRIVE_SHIP_VS_DEPART_SHIP;1.STATUS_CODE} = "DEPSHIP" then
DepDate := {ZZ_ARRIVE_SHIP_VS_DEPART_SHIP;1.CHANGED};
DepDate;

I then go on to calculate an average for GF1 (by driver):

I am using a "reset" formula in GH1, I have an "accum"
formula suppressed in GF2, and finally I have a
display formula (my GF1 'average datediff') called 'display_GF1'

@resetgroup:
whileprintingrecords;
numbervar secs;
numbervar cnt;
if not inrepeatedgroupheader then (
secs := 0;
cnt := 0
);

@accum:
whileprintingrecords;
numbervar secs := secs + {@DateDiff};
numbervar cnt := cnt + 1;

@display_GF1:
whileprintingrecords;
numbervar secs;
numbervar cnt;
secs/cnt


Thank you, Amy

 

[lbass]

You need a second variable to accumulate the grandtotal so that it won't be reset and also to count the groups. So change {@accum} to:

whileprintingrecords;
numbervar secs := secs + {@DateDiff};
numbervar cnt := cnt + 1;
numbervar grtot := grtot + {@DateDiff};
numbervar gtcnt := gtcnt + 1;

Then in the report footer use:

whileprintingrecords;
numbervar grtot;
numbervar gtcnt;
grtot/gtcnt

-LB

 

[bluraz]

Thank you very much!!! Worked perfectly... I apologize for the double post; did not realize I posted under someone elses question.