Problem selecting a record based on criteria from another field.

[OKCMikeSoll]

If there is a thread that will answer this question please let me know which. Otherwise, I am tring to get aging information from 2 date/time fields. I have formulas to calculate the age but the challange for me is trying to select a specific date/time record base on criteria from another feild. Please see example below.

Group1(Incident_Num.IncidentTable)
Group2(Activity_Num.ActivityTable)

Example:
Ticket Number
G1: 123456
Activity Num Type Date Stamp
G2: 1 Ticket Open 1/1/2005 10:30AM
G2: 2 Called Customer 1/2/2005 11:00AM
G2: 3 Called Customer 1/2/2005 11:30AM
G2: 4 Ticket Closed 1/3/2005 10:00AM

I need to somehow extract the Date/Time value for the first activity that has a "Type" of "Ticket Open" then for the second value I need to extract the Date/Time value for the first record that has a "Type" of "Called Customer". There could be more than one activity with the "Type" of "Called Customer" so I need to get the first one so I can show the age from ticket open to first contact.

CR Version: 9
Database: Oracle

Any assistance would be most appreciated as this has been quite a headache for me. Please let me know if you need additional details.

Thank you in advance.
Mike Soll

 

[lbass]

Why are you grouping on activity number? WHat does your detail level data look like?

-LB

 

[OKCMikeSoll]

I was grouping on the activity number becuase they are related to the incident number and are sequential. It may be the wrong way to go about it but it is basically replicating a ticket with all history from that ticket. I have nothing the in the details section.

-Mike Soll

 

[lbass]

What would the details look like if you placed the GH2 field in the detail section?

-LB

 

[OKCMikeSoll]

It apears to display all activities associated with the Ticket Number(GH1) but it create duplicate records and they are no long sequential. For example:

Ticket Number
G1: 123456
Activity Num Type Date Stamp
D: 1 Ticket Open 1/1/2005 10:30AM
D: 1 Ticket Open 1/1/2005 10:30AM
D: 1 Ticket Open 1/1/2005 10:30AM
D: 4 Ticket Closed 1/3/2005 10:00AM
D: 4 Ticket Closed 1/3/2005 10:00AM
D: 4 Ticket Closed 1/3/2005 10:00AM
D: 4 Ticket Closed 1/3/2005 10:00AM

-Mike Soll

 

[OKCMikeSoll]

Any ideas?

 

[lbass]

Assuming you keep your group #2 on activity number, create these three formulas:

//{@reset} to be placed in GH#1:
whileprintingrecords;
datetimevar opendate := datetime(0,0,0,0,0,0);
datetimevar firstcalldate := datetime(0,0,0,0,0,0);
numbervar cnt := 0;
numbervar cnt2 := 0;

//{@diff}:
whileprintingrecords;
datetimevar opendate;
datetimevar firstcalldate;
numbervar cnt := cnt + 1;
numbervar cnt2;
numbervar datediffresult;

if cnt = 1 then
opendate := {table.datestamp};
if {table.type} = "Called Customer" then
cnt2 := cnt2 + 1;
if cnt2 = 1 then
firstcalldate := {table.datestamp};
datediffresult := datediff("n", opendate, firstcalldate);

//{@displ} to be placed in the GF#1:
whileprintingrecords;
numbervar datediffresult;

The second formula calculates the difference in minutes--change it to days ("d", hours ("h"), or seconds ("s"), as you wish.

-LB