Time Difference in a Group

[tkschief]

I am using Crystal Reports 11 and have a report that is grouped by case number. In each group, there are 4 events. I'm not sure how to calculate the time difference in minutes between the Locate Date and each of the 3 deadline events. I attempted a formula but the time difference is always 0 so obviously I am doing something wrong.

CASE NUMBER DATETIME EVENT TIME DIFF
Case Number 05/18/12 12:19PM Locate Date
Case Number 05/18/12 12:42PM Deadline Service w/in 90 Days 23 min
Case Number 05/18/12 12:44PM Deadline Order w/in 6 Months 25 min
Case Number 05/18/12 12:47PM Deadline Order w/in 12 Months 28 min

05/18/12 12:47PM is a datetime field
Goal: 1st deadline datetime minus locate datetime = time difference in minutes
2nd deadline datetime minus locate datetime = time difference in minutes
3rd deadline datetime minus locate datetime = time difference in minutes

I thought I would need to create a variable for the Locate Date in order to calculate the time difference between the Locate Date and each of the Deadline dates.

Attempted Formula:
DateDiff('m',{Command.Create Date},{@LocateDateVar})

Attempted Variable:
If {Command.EVENT_TYPE_ID}=1000223 then {Command.Create Date}

Any help would be appreciated. Thanks!

 

[xxyyzz49]

In your DATEDIFF funtion you are using the wrong interval. 'm' is for month which will give a 0 for the sample you provided. The correct interval is 'n' for minute. I would use a global variable for the locate date then reference it from the other three records resetting for each group.

 

[lbass]

You could use this:

datediff("n", maximum({@LocateDateVar},{table.casenumber}),{Command.Create Date})

-LB