if ({HPD_HelpDesk.Assign_Time}) > ({HPD_HelpDesk.Resolved_Time}) then "Re-Opened"

 

Its very crude and whilst does sort of 'count' calls that were at a status of Assigned after a resolved entry, it would miss a large number.

 

So after talking to some of our developers I wrote this and added it as the workflow behind a selection field in Remedy (Done in SQL)

 

SELECT case_id FROM hpd_helpdeskhistory h1

WHERE H1.CASE_STATUS = 5

AND EXISTS

(

SELECT 1 FROM hpd_helpdeskhistory h2

WHERE h1.CASE_ID = h2.CASE_ID

AND h2.CASE_STATUS NOT IN (4,5)

AND h2.REQUEST_ID > h1.REQUEST_ID

)

 

 

Within the Record Selection

 

{HPD_Helpdesk_HelpdeskHistory.Create_Time} = lastfullmonth // Actual helpdesk case create time
and
{HPD_Helpdesk_HelpdeskHistory.Case_Status} = "Resolved" // Case Status at time of update
and
{HPD_Helpdesk_HelpdeskHistory.Case_Type} = "Incident"

 

and in the Group Selection

 

// Find cases that have been saved as "Resolved" more than once
Count ({HPD_Helpdesk_HelpdeskHistory.Case_Status},{HPD_Helpdesk_HelpdeskHistory.Case_ID}) > 1
and
// Filter out duplicates to show each Case ID only once
{HPD_Helpdesk_HelpdeskHistory.Create_Date} = Maximum ({HPD_Helpdesk_HelpdeskHistory.Create_Date},{HPD_Helpdesk_HelpdeskHistory.Case_ID})