Audit Trail close date

[zootweller]

Hi

I am trying to calculate the SLA resolution times for a call management system, but the main table's close date field actually records the last time the call was closed. If it was closed, re-opened and closed again, it updates.
To see when a call was initially closed, I have to look in an audit-trail table - is there a way that I can retrieve the datetime for the 1st closure so it can be used to calculate the real SLA time? Audit trail closure entries have an 'entry_type' of 'Quick Solution' and I was thinking along the lines of IF AuditTable.Entry_Type = 'Quick Solution' then Minimum(AuditTable.EntryDateTime) or similiar, but may be going up the wrong path...

 

[HowardHammerman]

How about this:

Group on an ID field such as service call ID.
Next sort on the date/time field in decending order
Next select the correct records as you indicated above:
'entry_type' = 'Quick Solution' . Finally supress the detail section and put the info you need in the group footer section.

That should work.

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

 

[lbass]

You could try a formula like {@firstauditclose}:

if isnull({AuditTable.EntryDateTime}) or
{AuditTable.Entry_Type} <> 'Quick Solution' then
datetime(9999,09,09,0,0,0) else
{AuditTable.EntryDateTime}

Then you could insert a minimum on this formula to get the earliest date from the audit table. This would allow for multiple closing dates in the audit table.

You could then do a second formula for display of the first closing date:

if {@firstauditclose} = datetime(9999,09,09,0,0,0) then
{main.closedate} else {@firstauditclose}

-LB

 

[zootweller]

Thanks!
I have used the 2 formula ibass provided, the second formula slightly altered:
if {@firstauditclose} = datetime(9999,09,09,0,0,0) then
{main.closedate} else Minimum({@firstauditclose}, {main_table.call_reference}).
The true SLA can be calculated from there..

Cheers!!!


(p.s. I just need to convert the decimal hours into true hours for the SLA but that's another story!)