Clarity Service Management

Expand all | Collapse all

Assigned time in group with no client delay

  • 1.  Assigned time in group with no client delay

    Posted 02-11-2016 11:37 AM

    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_



  • 2.  Re: Assigned time in group with no client delay

    Posted 03-02-2016 03:02 AM

    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