CA Service Management

I have built a report with the duration time that a ticket is on a specific group, but now i need to modify this report and not to consider the time duration when the status of the ticket is on "Awaiting customer" (for example).

In my current report i just get the time in my "Group_name" filtering the changes in "field_name  =  'group'" and using the PDMDownTime function:

SELECT

  cr.ref_num,

  ktd.end_time,

  ktd.field_name,

  ktd.field_value,

  ktd.ktd_duration,

  ktd.next_value,

  ktd.prev_time,

  ktd.user_context_userid,

  cr.resolve_date,

  PDMSeconds(PDMDownTime('Workshift_name','Mon - Fri { 9:00 am - 06:00 pm }', ( ktd.prev_time ), ( ktd.end_time )))

FROM

  cr INNER JOIN ktd ON (cr.id=ktd.obj_id)

WHERE

  (

   (

    ktd.field_name  =  'group'

    AND

    ktd.field_value  =  'Group_name'

   )

   AND

   ( (( cr.resolve_date ) between @Prompt('Earliest Resolve Date', 'D', ,mono,free) AND @Prompt('Latest Resolve Date', 'D', ,mono,free) )  )

  )

The problem with this is that in my duration time i also consider the time if the status of the ticket is in awaiting customer.

Please any help will be appreciated.

Feel free to ask any dubts or questions.

PD:using SDM 12.9 and BOXI r3.

Hernanlo_

Hi,

As you mostly know this timestamp for status change are logged in the activity log.

So you will have to build a sql query with a join to this table that will retrieve the two consecutive activities and calculate the time in between to be able finally to subtract that time.

Will be complex query and not either sure that this is directly achievable in BOXI and CA universe.

Another approach may be to pre-calculate that time in SDM directly into a custom field and pick up from there.

Hope this help

\J