Average response

[Edearl1]

I am still having issues with an overall average response time here is what I have at present. I have returns that are negative and believe they are to do with these being calls that are entered and recur (i.e. dialysis patients) Any help will be greatly appreciated. This is an example of the prescheduled call that may have been entered into the system days, weeks or months ago.

Time Rec At Scene Time Avg Resp Time
11:20:11 12:02:20 04-00:42:09

This is the formula I use to get the Avg Resp Time in the above example: //This is the display of the response time in the (Day,hr,min,sec) dd-hh:mm:ss format

whileprintingrecords;
NumberVar totalsecs :=(((({@Date Time At Scene} - {@Date Time Received})*24)*60)*60);
Numbervar dd := 0;
Numbervar hh := 0;
Numbervar mm := 0;
Numbervar ss := 0;

// for trips that never got on scene
If {Trips.atsdate} = "1900-01-01" then totalsecs := 0 else totalsecs := totalsecs;

If totalsecs < 0 then
(
// for trips that had a negative response time
// Days
If totalsecs <= -86400 then
(dd :=Truncate (totalsecs / 86400,0 );
totalsecs := totalsecs - (dd * 86400 ) );

// Hours
If totalsecs <= -3600 then
(hh :=Truncate (totalsecs / 3600,0 );
totalsecs := totalsecs - (hh * 3600 ) );

// Minutes
If totalsecs <= 60 then
(mm := Truncate (totalsecs / 60,0 );
totalsecs := totalsecs - (mm * 60 ) );

// Seconds
ss := totalsecs;

If dd=0 then
// display hh:mm:ss
ToText(hh,"00",0 ) + ":" + ToText(mm,"00",0 ) + ":" + ToText(ss,"00",0 )

else

// display dd-hh:mm:ss
ToText(dd,"00",0 ) + "-" + ToText(hh,"00",0 ) + ":" + ToText(mm,"00",0 ) + ":" + ToText(ss,"00",0 )

)

else

(
//for trips with a normal response time
// Days
If totalsecs >= 86400 then
(dd :=Truncate (totalsecs / 86400,0 );
totalsecs := totalsecs - (dd * 86400 ) );

// Hours
If totalsecs >= 3600 then
(hh :=Truncate (totalsecs / 3600,0 );
totalsecs := totalsecs - (hh * 3600 ) );

// Minutes
If totalsecs >= 60 then
(mm := Truncate (totalsecs / 60,0 );
totalsecs := totalsecs - (mm * 60 ) );

// Seconds
ss := totalsecs;

If dd=0 then
// display hh:mm:ss
ToText(hh,"00",0 ) + ":" + ToText(mm,"00",0 ) + ":" + ToText(ss,"00",0 )

else

// display dd-hh:mm:ss
ToText(dd,"00",0 ) + "-" + ToText(hh,"00",0 ) + ":" + ToText(mm,"00",0 ) + ":" + ToText(ss,"00",0 )
)


This is a total of approximately 10 pages of data using the avg @ datediff forumla, placed in a group footer 101,018.32, this should be in hh:mm:ss format.
This is the formula I use: datediff("s",{@Date Time Received},{@Date Time At Scene})/60

Anything you can tell me that I need to do differently to get a good average would be benefical.


Here is a better example of a single days for a specific zone.

 

[Madawc]

Have you looked at Crystal's Running Totals? They might help.

I'm not sure I follow what you are after, though. First you list formulas to convert dates to seconds, then you show DateDiff which does the job for you. Just what is the issue?

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP