DateTimeDiff of records within group 1

[theDeej]

SeagateInfo 7.5.2
Seagate Crystal Report 8.0.1.1

I need to be able to find the datetime difference between records within a group. For each call#, we track history of when the status of a call changes throughout the life of the call. There are multiple statuses and not all calls will go though all possible statuses.

I have grouped the report by call number - the group header contains the call#, opendate, closedate etc. (general call information) in the details I have placed the status history information; datetime the call status changed and the status it changed to.

How can I find the difference between the datetime for each status change beginning with the opendate and ending with closedate capturing everything in between? I need to be able to report how long a each particular call was in each particular status.

Thanks in advance!

 

[BlurredVision]

Might be able do the following:

IF PROBLEM_STATUS = 'TRANSFERRED' THEN

datetimediff(opendatetimefield, closeddatetimefield);

Brian

http://www.crystalinfo.net

 

[dgillz]

Are there multiple database records per call? Or just one database record per call with multiple fields? Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[theDeej]

Multiple records per call - and that's exactly where I run into trouble...

 

[dgillz]

Group by call number and then use maximum and minimum summary operations. Take the minimum of call start date/time and the maximum of call end date/time.

Then do a datediff() on the 2 summary fields. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[theDeej]

Let me be more clear.

I have a call table with open dates and close dates. I have a history table with records containing newstatusname and changedate (there is a new record everytime the status of a call changes - which can be as little as one or as many as 50).

Call# 1234 Opened: MM/DD/YYYY Closed: MM/DD/YYYY HH:MM
Status1 MM/DD/YYYY HH:MM
Status2 MM/DD/YYYY HH:MM
Status3 MM/DD/YYYY HH:MM
Status4 MM/DD/YYYY HH:MM

So, what I need to be able to do is calculate How long (DD:HH:MM) that the call was in each status. So if the call opened on 01/01/2002 and on 01/02/2002 it was changed to "Status1" then the call was in open status for 1 day. Then if on 02/03/2002 the status changed to "Status2" then the call was in "Status1" for 2 days. If the call is then closed on 02/04/2002 the the call was in "Status2" for 1 day.

I guess the part I'm having trouble with is calculating the datediff between the changedate for record 1 and the changedate for record 2.

How can I get Cyrstal to calculate this for me?

 

[dgillz]

Datediff({datefield},Previous({datefield))) should do the trick. You will probably have to use variables and keep adding the elapsed time to the variable as long as the status is unchanged. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[theDeej]

Thank you Thank you! Had never used previous/next before... Definitely helps!

Now my question is: For the first calculation it uses the previous record which is in the previous group. Is there a way to use previous/next within the group only?

 

[dgillz]

In oyur formula, start out with:

Not OnFirstRecord

You need to use similar logic if you emply the next function, namely Not OnLastRecord Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com