create table #emails (personId int, email nvarchar(100), type char(1) )

insert into #emails values (1, 'bob@company.com', 't');
insert into #emails values (1, 'bob@hotmail.com', 'h');
insert into #emails values (2, 'bill@hotmail.com', 't');
insert into #emails values (2, 'bill@gmail.com', 'h');

select * from #emails

drop table #emails

where (email like '%@company.com' and type = 'h') or
      (email not like '%@company.com' and type = 't')

create table #emails (personId int, email nvarchar(100), type as case when email like '%@company.com' then 'h' else 't' end)