Average hour and minutes of Multiple Time Values

[jemsmom]

I need to find the average time of multiple date time fields that are in 24 hour format. using Crystal 2008 with SQL Server.

My report is grouped by department and patient class and I need to provide the average time of day a patient is discharged for both groups and overall for the report. I would like it to display the hour and minutes like 2:46.

Thank you.

 

[MCuthill]

jemsmom,

Googling "Averaging Hour and Minutes in Crystal Reports" returns a thread on how this can be acheived, the link to which can be found here.

In summary, you need two fields:
{@Seconds}

(hour({datetimefield})*3600) + (minute({datetimefield})*60) + second({datetimefield})

Then in your group level:
{@AverageTime}

local numbervar Hours := 0; 
local numbervar Minutes := 0;  
local numbervar Seconds := 0;  
local numbervar TotalSeconds := [red]average({@Seconds})[/red];

    Hours := Truncate(TotalSeconds / 3600); 
    Minutes := truncate((TotalSeconds - (Hours * 3600))/60); 
    Seconds := Truncate(Totalseconds - (Hours * 3600) - (Minutes * 60)); 
    
        Time(Hours, Minutes, Seconds);

I have not tested the above, but in reading it, it does precisely what I was thinking to do and I would think it should work as a starting point.

Please note, you will need to substitute your field in place of "{DateTimeField}" in the above as well as change the fourth line of the second formula to be the appropriate group level you need the average on (Patient Class, in your case... I beleieve). I have marked this text in [red]red[/red] for you.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[jemsmom]

Worked perfect. Thank you for the response and the link.