Clarity Service Management

Expand all | Collapse all

Activity duration calculation

  • 1.  Activity duration calculation

    Posted 12-12-2015 09:07 AM

    Hi,

     

    Am trying to write a query to get the total spent hours for each activity in an incident. Below is the query and the out put as follows.

     

    select b.id, c.ref_num ,b.call_req_id,b.action_desc,(select DATEADD(s,b.time_stamp+19800,'1970-01-01 00:00:000')) as 'Timestamp' from act_log b inner join stakeholders c on

    b.call_req_id=c.persid.

     

    Am able to reach till this and please let me know how to get the duration. EX: the initial value should be zero then next difference should be( 400761-400760) 1 min 30 sec.

     

    Like wise I should get for individual incident.

     



  • 2.  Re: Activity duration calculation

    Posted 12-14-2015 01:42 AM

    some one may please help..



  • 3.  Re: Activity duration calculation

    Posted 12-28-2015 11:54 AM

    I couldn't find a "stakeholders" table, maybe because I'm still on 12.9.

     

    However, replacing call_req for stakehodlers I was able to make the following changes to your script:

    select

    b.id,

    c.ref_num ,

    b.call_req_id,

    b.action_desc,

    (select DATEADD(s,b.time_stamp+19800,'1970-01-01 00:00:000')) as 'Timestamp',

    ((b.time_stamp - c.open_date)/3600) as Duration

    from

    act_log b inner join call_req c on b.call_req_id=c.persid

     

    Since the subtraction above would get you the seconds between the activity time stamp and the time when the request/Incident/problem was opened, dividing by 3600 should get the hours you're looking for.