SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table OPERATOR (
   IDOPERATOR int not null,
   [TYPE]     nvarchar(20) not null,
   constraint [TYPE]
     check ([TYPE] in ('assistant1','assistant2','assistant3')),  
   constraint PK_OPERATOR 
     primary key (IDOPERATOR)
);

create table HOURS (
   IDHOUR     int identity(1,1) not null,
   IDOPERATOR int not null,
   STARTHOUR  time not null,
   ENDHOUR    time not null,
   constraint PK_HOURS 
     primary key (IDHOUR),
   constraint FK_HOURS_OPERATOR
     foreign key (IDOPERATOR) references OPERATOR(IDOPERATOR)
);
insert into OPERATOR 
(IDOPERATOR , [TYPE]) values
  (1,'assistant1')
, (2,'assistant2')
, (3,'assistant3')
;
insert into HOURS (idoperator, starthour, endhour) values
  (2,'14:30','15:30')
, (1,'09:00','17:33')
;
5 rows affected

SELECT op.IDOPERATOR, op.[type]
,sum(datediff(minute,starthour,endhour)) as tmdiff
,case 
 when [type] = 'assistant1'
  and sum(datediff(minute,starthour,endhour)) > 480 
  then 'cease operation'
 when [type] = 'assistant2'
  and sum(datediff(minute,starthour,endhour)) > 420 
  then 'cease operation'
 when [type] = 'asssistant3'
  and sum(datediff(minute,starthour,endhour)) > 540 
 then 'cease operation'
 when max(hr.IDOPERATOR) is null
 then 'unknown'
 else 'without effect'
 end AS [result]
FROM OPERATOR op
LEFT JOIN HOURS hr
  on hr.IDOPERATOR = op.IDOPERATOR
GROUP BY op.IDOPERATOR, op.[type];
 


IDOPERATOR
type
tmdiff
result


1
assistant1
513
cease operation

2
assistant2
60
without effect

3
assistant3
null
unknown


Warning: Null value is eliminated by an aggregate or other SET operation.


IF OBJECT_ID (N'dbo.ufnEvalHourDiff', N'FN') IS NOT NULL  
    DROP FUNCTION dbo.ufnEvalHourDiff;
--
-- A User Defined Function
--
CREATE FUNCTION dbo.ufnEvalHourDiff
(
 @OperatorType NVARCHAR(20), 
 @TimeDiffMinutes INT
)  
RETURNS VARCHAR(30)   
AS   
BEGIN
  DECLARE @result VARCHAR(30);
  
  IF (@OperatorType = 'assistant1' 
      AND @TimeDiffMinutes > 480) OR
     (@OperatorType = 'assistant2' 
      AND @TimeDiffMinutes > 420) OR
     (@OperatorType = 'assistant3' 
      AND @TimeDiffMinutes > 540)
  begin
    SET @result = 'cease operation';
  end
  ELSE
    SET @result = 'without effect';
  
  RETURN @result;
END;
SELECT op.IDOPERATOR, op.[type]
, SUM(DATEDIFF(MINUTE, starthour, endhour)) AS tmdiff
, dbo.ufnEvalHourDiff(op.[type], SUM(DATEDIFF(MINUTE, starthour, endhour))) as result
FROM OPERATOR op
LEFT JOIN HOURS hr
  ON hr.IDOPERATOR = op.IDOPERATOR
GROUP BY op.IDOPERATOR, op.[type];


IDOPERATOR
type
tmdiff
result


1
assistant1
513
cease operation

2
assistant2
60
without effect

3
assistant3
null
without effect
IDOPERATOR	type	tmdiff	result
1	assistant1	513	cease operation
2	assistant2	60	without effect
3	assistant3	null	unknown