Calculating Date/Time Field in Group 1

[FluffyKitty]

Thanks in advance for your help.

Problem:
(Sales Database)
I've grouped records by the Order_Number and each Order_Number has multiple records in the detail section. There is a Date/Time field used to track the order process. I would like to calculate the difference in time between the 1st Record and the Last Record in the GROUP. Thus giving me a "TOTAL TIME" it took to complete the Order.

I've tried Max & Min functions in the Group footer, but they return the record for the entire table.

Please advise.

Thank You,
Fluffy Kitty

 

[Guest_imported]

Hi,
I believe there is a function called TimeDiff that is used to compute the time difference. You could probably use this function after you have used the max and min functions to retrieve the first and last records in that group. This way you will have the time taken to process the order. Let us know what happens after you try this out.

 

[chris777]

When you used min/max did you include the "group by" field?
i.e. minimum({table.datetime}, table.ordernumber})
That may get you the values you need Chris
DFW Crystal User Group

http://www.dfwcug.com

 

[rosemaryl]

Dear Fluffy Kitty:

Here is an example of a formula. My result (totalsec) is then converted to display Days:Hours:Minutes:Seconds.

Place the formula in the Group Header or Group Footer.

//Formula Begin
WhilePrintingRecords;
NumberVar TotalSec := datediff("s", minimum({Table.DateField},{Table.OrderNo}), maximum({Table.DateField},{Table.OrderNo})) ;
NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
NumberVar Seconds := Remainder ( TotalSec , 60);

Totext ( Days, '00', 0,'') + ':'+
Totext ( Hours, '00', 0,'') + ':'+
Totext ( Minutes,'00', 0,'') + ':'+
Totext ( Seconds,'00', 0,'')

//Formula End

Hope this helps,

rosemary Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[kenhamady]

I would think you could just do:

maximum({Table.DateField},{Table.OrderNo}) -
minimum({Table.DateField},{Table.OrderNo}) Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com