Service Order escalation process

Hi Pascal,

While I am not going to do all the development for you, perhaps the below can be used as a guideline for what you want to do:

select item_id, serial_no, callt_id, sum(list1)
from (

select item_id, serial_no, callt_id, count(item_id) list1
from service_call with(nolock)
where callt_id = 'Support'
group by item_id, serial_no, callt_id


union all

select item_id, serial_no, callt_id, count(item_id) list1
from c_service_call with(nolock)
where callt_id = 'Support'
group by item_id, serial_no, callt_id


) FinalList

group by item_id, serial_no, callt_id
having sum(list1) > 5

The trick is to do a union all join between two queries; one for active and one for closed tickets and then sum the respective counts to qualify if over the threshold.

I did not do anything to check specific date ranges or other criteria. Please note, I have not tried this within a process flow itself but just pure SQL and it returned the expected results I wanted for my criteria.

There may be and probably are more elegant ways to do this.  If this is not sufficient, I recommend you ask for consulting services to help you develop exactly what you need.