Calculating the aging time from Open Time to First Contact Time

[OKCMikeSoll]

The goal of my report is to tell how many days it took a technician to contact the customer. I need a variance of days between the Open Time of the ticket to the First Customer Contact (which is stored as an activity in the ticket with a date stamp). I am thinking it is going to require a few formulas but I am not sure. Below I have listed a formula that I currently use to calculate open ticket aging and I think I can modify it to work for this. BTW, I am using Crystal Reports 9 Pro and reporting off an Oracle Database, the format of the report will be a crosstab.

NumberVar AgeDiff:=CurrentDate-1-Date({TICKET.OPEN_TIME});

If AgeDiff=0 then "( < 1 Day)"
else if AgeDiff>0 and AgeDiff<4 then "( 1 - 3 Days)"
else if AgeDiff>3 and AgeDiff<8 then "( 4 - 7 Days)"
else if AgeDiff>7 and AgeDiff<16 then "( 8 - 15 Days)"
else "(15+ Days)";

The Tables I need to report off of would be:
{TICKET.OPEN_TIME}
{ACTIVITY.STATUS} (This would be "Contact From Customer")
{ACTIVITY.DATE_STAMP}

One issue is that there may be several activties listed as "Contact From Customer" on one Ticket and I just need the variance of days from the open time of the ticket to the ({ACTIVITY.DATE_STAMP}) of the FIRST ({ACTIVITY.STATUS}) = "Contact From Customer"

I hope this is enough information please let me know if you need more. Any help you can provide would be most appreciated!

 

[HowardHammerman]

If you group on the customer ID, you can then sort on the contact date in assending order. That will put the first contact for a customer at the top of the group. Now create your formula like you did and put it in the group header. You will get the correct results.

By the way you can simplify your formual like this:

NumberVar AgeDiff:=CurrentDate-1-Date({TICKET.OPEN_TIME});

If AgeDiff=0 then "( < 1 Day)"
else if AgeDiff<4 then "( 1 - 3 Days)"
else if AgeDiff<8 then "( 4 - 7 Days)"
else if AgeDiff<16 then "( 8 - 15 Days)"
else "(15+ Days)";

as soon as the IF operator finds a true result it exits.



Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269