Count of number of times re-assigned?

[Berto2002]

I am trying to search Wiki and Forums for how the Product counts the number of re-assignments during the lifecycle of a Request. I have been asked for a report on those which have been reassigned >4 times.

Where can I find the documentation, please?

Does anyone have a report I could borrow from or know which tables and columns we need for this?

Thanks in advance.

Berto2002

[Emily Patrick]

+1 too, if anyone has an idea on this.  

 

[Steve Giller]

A rough guide would be something like:

SELECT COUNT(*) as Assignments, h_request_id FROM h_itsm_request_team_assignment WHERE h_previous_user_id != h_user_id GROUP BY h_request_id HAVING Assignments > 3

but it's not quite as straightforward as that, so you'd need to compare the results with the Request Timelines to check how accurate a count you are getting before relying on that data for a report.

[Berto2002]

I adapted to this which then only returns values where the status is not closed or cancelled so it can be used for current data:

SELECT COUNT(*) as Assignments, h_request_id, h_team_ID
FROM h_itsm_request_team_assignment A INNER JOIN h_itsm_requests R
ON A.h_request_id = R.h_pk_reference
WHERE A.h_previous_user_id != A.h_user_id AND R.h_status != 'status.closed' AND R.h_status != 'status.cancelled'
GROUP BY A.h_request_id
HAVING Assignments > 5