Home Self-Service Portal - Community

First Response time from portal and database reports are different

I am trying to fetch report for untouched ticket that means duration between created date and first response time(First analyst comment after ticket created).
I am finding issues in report i.e many tickets have first response time greater than created date hence duration comes in negative. Can someone suggest me if it is portal issue or database entries in different timezone issues? I have checked created date and first response time both are converted in local time in views created by me. I would like to get some input by others if they have faced this issues and what would be resolution ?

Thank you.


  • Options
    Gerhard_GoossensGerhard_Goossens Customer Advanced IT Monkey ✭✭✭
    It will be awesome if you can share your SQL :-)
    Maybe I can compare my values and see if we get the same results.
  • Options
    Rajnish_Kumar_MishraRajnish_Kumar_Mishra Customer IT Monkey ✭
    Hi Gerhard, I have created view to get first response time and below is the query.

    [FirstResponseTime] = (SELECT TOP 1 dbo.fn_GetLocalTime(EnteredDate_4E8F8D74_6B59_27B4_325B_60E901FFA4F6) AS [FirstResponseAgent]
    FROM [MT_System$WorkItem$TroubleTicket$AnalystCommentLog](nolock) ACLog
    INNER JOIN RelationshipView R(nolock) ON R.TargetEntityId = ACLog.BaseManagedEntityId
    INNER JOIN dbo.BaseManagedEntity BME1(nolock) ON BME1.BaseManagedEntityId = R.SourceEntityId  
    INNER JOIN dbo.BaseManagedEntity BME2(nolock) ON BME2.BaseManagedEntityId = R.TargetEntityId
    WHERE  R.IsDeleted=0 AND ISNULL(IsPrivate_CE9EEEC2_6842_846E_C82B_4E8E63361DA3,0)=0   
    AND R.[SourceEntityId]=SR.BaseManagedEntityId
    AND BME1.BaseManagedTypeId ='04B69835-6343-4DE2-4B19-6BE08C612989'  -- Service Request ManagedTypeId | Query: SELECT TOP 1 * from ManagedType WHERE TypeName LIKE 'System.WorkItem.ServiceRequest' 
    AND BME2.BaseManagedTypeId='F14B70F4-878C-C0E1-B5C1-06CA22D05D40' --  AnalystCommentLog ManagedTypeId | Query: SELECT TOP 1 * FROM ManagedType WHERE TypeName LIKE '%AnalystCommentLog%'
    ORDER BY EnteredDate_4E8F8D74_6B59_27B4_325B_60E901FFA4F6)
Sign In or Register to comment.