Sql query question 4 experts

I have a table that contains my Service Orders and for the service orders there could be many service troubles. I need to a query that will get the results as folows: Joe went to fix a problem and opened service order number 1. The issue was not resolved, customer calls back. Now Bob goes to fix the issue that Joe didn't resolve. Out of this Joe is docked a repeat service. Joe has 1 repeat. The next day customer calls back: issue is not resolved and Joe goes out to fix the problem. Now Bob is docked a repeat. Bob has 1 repeat. The issue was not resolved and Joe is sent to fix the issue again. Joe is docked another repeat. Now joe has 2 repeats. Issue still not resolves and Bob goes out to fix the problem. Joe is docked another repeat. Now Joe has 3 repeats. Issue still not resolved and Bob is sent out again to fix problem. Bob is docked a repeat. Now Bob has 2 repeats and Joe has 3 repeats for the same service order.

The serv_trouble is numeric. The serv_order has a start date and a closed date in another table. Unfortunately the Serv_trouble does not have a date assigned.

I need ideas for how to write this type of query. Below is a sample of the table:

Serv_Trouble TechID Tech ServOrder
1 1 joe 1
2 2 bob 1
3 1 joe 1
4 1 joe 1
5 2 bob 1
6 2 bob 1
7 1 joe 1
1 1 joe 2
2 1 joe 2
3 2 bob 2
4 2 bob 2
5 3 larry 2
7 2 bob 2

Any help will be very, and I mean very appreciated. I have searched most SQL sites for ideas but non seem to solve something like this.

What do you expect in the result based on the table data?

Code:

---

declare @orders table        (
                        id smallint identity(1,1),
                        ServOrder smallint,
                        ServTrouble smallint,
                        TechID smallint,
                        TechName varchar(10) collate database_default,
                        primary key clustered(ServOrder, ServTrouble, Techid)
                        );
                                               
insert into @orders (ServOrder, ServTrouble, TechID, TechName)
values (1, 1, 1, 'joe'), (1, 2, 2, 'bob'), (1, 3, 1, 'joe'), (1, 4, 1, 'joe'), (1, 5, 2, 'bob'), (1, 6, 2, 'bob'), (1, 7, 1, 'joe'),
(2, 1, 1, 'joe'), (2, 2, 1, 'joe'), (2, 3, 2, 'bob'), (2, 4, 2, 'bob'), (2, 5, 3, 'larry'), (2, 6, 3, 'larry'), (2, 7, 2, 'bob');


select * from @orders order by ServOrder, ServTrouble;

select ServOrder, TechID, TechName, count(distinct uplimit) as repeats
from
(
        select a.*,
                (       
                select min(ServTrouble)
                from @orders as b
                WHERE b.ServOrder = a.servorder
                and (
                        ( b.ServTrouble >= a.servtrouble
                          and  exists (select * from @orders as c where c.ServOrder = b.servorder and c.ServTrouble = b.ServTrouble + 1 and c.TechID <> b.TechID)
                        )
                        or
                        ( b.ServTrouble = (select max(ServTrouble) from @orders as d where d.ServOrder = b.ServOrder)
                          and exists(select * from @orders as e where e.ServOrder = b.ServOrder and e.ServTrouble < b.ServTrouble and e.TechID <> b.TechID)
                        )
                    )
                ) AS uplimit
        from @orders as a
) AS o
group by ServOrder, TechID, TechName
order by ServOrder, repeats, TechName;

---

--HTH--