First Response time from portal and database reports are different

Rajnish_Kumar_Mishra

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.

Gerhard_Goossens

It will be awesome if you can share your SQL :-)
Maybe I can compare my values and see if we get the same results.

Rajnish_Kumar_Mishra

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)