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--