Report to show "Work Item State Aging" in Hours and Minutes.

[Hareesh]

Hi
I am trying to create a report on work item State Aging using report studio.  Our requirement is to get "Days in State " in hrs or get a report  to show the value of aging in hours as my request state changes within a day.

I have created a report for a particular WorkItem State aging i.e., report to know how many hours,minutes the state was there before transition.

Example :
New : 12 minutes 50 seconds 469 milliseconds
Inprogress : 1 hour 37 minutes 31 seconds 930 milliseconds

Below is the procedure I tried to achieve and got stuck at a point.Need suggestion on this

State Tranistion is as below
New -> InProgress
InProgress -> complete
complete -> closed
Closed -> reopened
reopened -> Inprogress
Inprogress -> complete

Process:
In a Report page,  values from ODS are used as below

Request Area -> Request -> Project Name
Request Area -> Request -> Name
Request Area -> Request State History -> Request state
Request Area -> Request State History -> Last Updated
Request Area -> Request State History -> Request state ID

Similarly created QUERY2 considering the values as below

Request Area -> Request -> Project Name
Request Area -> Request -> Name
Request Area -> Request State History -> Pevious Request state
Request Area -> Request State History -> Last Updated
Request Area -> Request State History -> Previous Request state ID


Created a Join Between Query1 and Query2 where Request State ID = Previous Request State ID and Lastupdate(previous request state) - Lastupdate(request state)

Where is showing the needed output as below.

But when the workitems undergo another loop in the workflow.It is calculating the earlier timing also.Let me make it understand clearly with a example


With respect ot above condition,calculation happens this way.

Request State ID = Previous Request State ID and Lastupdate(previous request state) - Lastupdate(request state)


In Progress : Feb 25, 2015 11:10:41 AM - Feb 25, 2015 11:01:25 AM = 9 minutes 15 seconds 720 milliseconds

In progress state in a second loop :  Feb 25, 2015 12:38:57 PM - Feb 25, 2015 12:31:15 PM = 7 minutes 41 seconds 855 milliseconds

These is what we expected as a output.But the below calculation is happening. How can we avoid this.

(lastupdate of previous request in secondloop) Feb 25, 2015 12:38:57 PM - Feb 25, 2015 11:01:25 AM(Lastupdate of request state in 1st loop)

Marked as Yellow in attachment.

How can I avoid the above mentioned of miscalculation .Can Anyone help me in this?