Problem with DateDiff and mulitple groups

[OKCMikeSoll]

I have a report that basically gives the details of a helpdesk problem ticket. I am trying to get the age(time) between the {TABLE.OPEN_TIME} from Group #1 and the Datestamp from the first record in Group #2 that has the activity type of "Comm with Customer". I have a formula that gives me the age but this report is different then the ones I have done in the past becuase the ending date/time is in another group and has to somehow be associated to the first record with a specific "Activity Type".

Ticket # DateTime Acivity Type
Group #1(Ticket #) 1/1/2005 10:23PM

Group #2(Activity #) 1/1/2005 10:23PM Open
Group #2(Activity #) 1/1/2005 11:05PM Comm with Customer
Group #2(Activity #) 1/2/2005 08:35AM Comm with Customer
Group #2(Activity #) 1/2/2005 09:35AM Reassignment
Group #2(Activity #) 1/3/2005 10:45AM Closed


Please let me know if you need more information and thank you in advance for your assistance.

CR v.9.0
Oracle 9i

-Mike

 

[bdreed35]

Create a formula to isloate the dates of the specific activity number like this:

//@CommWithCustomerDates
if {TABLE.ACTIVITY_TYPE} = "Comm with Customer" then
{TABLE.ACTIVITY_DATETIME}
else
date(2999,1,1) //need a default out of range date

Create another formula to calculate the time difference in seconds and place in the Group 1 Header or Footer:

//@TimeDiff
DateDiff("s",{TABLE.OPEN_TIME},minimum({@CommWithCustomerDates},{TABLE.TICKET#})

~Brian