Minimum Time versus Maximum Time

[eric333]

Good evening,

I have a series of records that have a date and time stamp. I have grouped the records by two levels (reference number and employee ID). I need to identify the earliest record (minimum) and the latest record (maximum) and then calculate the time difference between the two. Does anyone have any suggestions?

 

[lbass]

The earliest and latest record within the innermost group? If so, use:

datediff("s",minimum({table.datetime},{table.employeeID}), maximum({table.datetime},{table.employeeID}))

This assumes that refno is group #1 and employee ID is group #2.

-LB

 

[eric333]

Thanks!!

So that returns the value in seconds. How do I display that value in HH:MM:SS format?

 

[eric333]

Oh yeah...one more thing, I also need to insert totals at the reference number level (in other words, the total amount of time spent on this particular reference number, and a grand total for all time spent. The formula field I created to calculate the difference is not listed in the fields to summarize.

 

[lbass]

You should lay out the entire requirement in your first post, instead of stringing them.

To get the values per higher order group and for the grand total, you have to use the seconds for the calculation, and then in the display formula, convert the result to a string.

//[@reset} for the refno group header (suppress the formula):
whileprintingrecords;
numbervar refnotot;
if not inrpeatedgroupheader then
refnotot := 0;

//{@accum} to be placed in the employeeID group header or footer and suppressed:

whileprintingrecords;
numbervar refnotot := refnotot + datediff("s",minimum({table.datetime},{table.employeeID}), maximum({table.datetime},{table.employeeID}));
numbervar grtot := grtot + datediff("s",minimum({table.datetime},{table.employeeID}), maximum({table.datetime},{table.employeeID}));

//{@displrefnotot to be placed in the refno group footer:
whileprintingrecords;
numbervar renotot;

//{@displgrtot} for the report footer:
whileprintingrecords;
numbervar grtot;

Within the last two display formulas, build in the conversion to string by setting "dur" := to the particular variable, using faq767-3543.

-LB

 

[eric333]

Thanks for the information, and sorry for not including the original requirements. I thought the calculations would be easier once I had the minimum and maximum defined. Anyways, the suggestions are a bit over my head, but I'll try. Thanks again.