>>Select min(sdatetime) as FirstDate >>from dbo.Meetings (nolock) >>where iscancelled=0 >>and roomclosed=0 >>and isblock=0 >>and substring(scheduledBy,1,4)<>'TEST' >>and substring(scheduledBy,1,3)<>'CQI' >>>
>>Select min(sdatetime) as FirstDate >>from dbo.Meetings (nolock) >>where iscancelled=0 >>and roomclosed=0 >>and isblock=0 >and scheduledBy not like 'TEST%' >and scheduledBy not like 'CQI%' >The other way would be a left join with itself and a like condition, e.g.
;with cte_ToExclude as (select IDField from dbo.Meetings (nolock) where iscancelled=0 and roomclosed=0 and isblock=0 and (scheduledBy like 'TEST%' OR scheduledBy like 'CQI%')) select min(M.sDateTime) as FirstDate from dbo.Meetings (nolock) M where M.iscancelled=0 and M.roomclosed=0 and M.isblock=0 LEFT JOIN cte_ToExclude C on M.IdField = C.IDField where c.IDField IS NULLOr use not exist condition here:
select min(M.sDateTime) as FirstDatefrom dbo.Meetings (nolock) M where M.iscancelled=0 and M.roomclosed=0 and not exists(select 1 from from dbo.Meetings (nolock) M2 where M2.iscancelled=0 and M2.roomclosed=0 and M2.isblock=0 and M2.scheduledBy like 'TEST%' and M2.IdField = M1.IDField) and not exists (select 1 from from dbo.Meetings (nolock) M3 where M3.iscancelled=0 and M3.roomclosed=0 and M3.isblock=0 and M3.scheduledBy like 'CQl%' and M3.IdField = M1.IDField)