declare @t table(rego varchar(10),costcentername varchar(20),startdate varchar(10),enddate varchar(10))
insert into @t values('AE70PV','51417 64702','2007-01-01','2007-09-17')  
insert into @t values('AE70PV','51417 34504','2007-09-18','2100-01-01')  
insert into @t values('AK11ZS','51417 34501','2007-01-01','2007-09-16')  
insert into @t values('AK11ZS','51417 XX198','2007-09-17','2100-01-01')  
insert into @t values('AK83HB','51417 66303','2007-02-27','2007-09-06')  
insert into @t values('AK83HB','51417 34504','2007-09-07','2100-01-01')  
insert into @t values('AM77KA','51417 34501','2007-01-01','2007-09-16')  
insert into @t values('AM77KA','51417 XX198','2007-09-17','2100-01-01')  
insert into @t values('AP32XJ','51417 27510','2007-06-28','2007-09-12')  
insert into @t values('AP32XJ','51417 XX232','2007-09-13','2007-09-25')  
insert into @t values('AP32XJ','51417 27510','2007-09-26','2100-01-01')  
insert into @t values('AP98XI','51417 64302','2007-06-29','2007-09-17')  
insert into @t values('AP98XI','51417 64702','2007-09-18','2100-01-01')  
insert into @t values('AQ23AY','51417 XX254','2007-07-14','2007-09-11')  
insert into @t values('AQ23AY','51417 27510','2007-09-12','2007-09-25')  
insert into @t values('AQ23AY','51417 27503','2007-09-26','2100-01-01') 

declare @startdate datetime,@enddate datetime
set @startdate='2007-09-01'
set @enddate  ='2007-09-30'


select
    t.*
from
    @t t
where
    ((t.startdate between @startdate and @enddate)
     or
     (t.enddate   between @startdate and @enddate))
    and
    not exists(select 
                   1
               from 
                   @t 
               where 
                   rego=t.rego 
                   and
                   ((startdate between @startdate and @enddate)
                    or
                    (enddate   between @startdate and @enddate))
                   and 
                   datediff(dd,case when startdate>@startdate then startdate else @startdate end,
                               case when enddate  <@enddate   then enddate   else @enddate   end)
                   >
                   datediff(dd,case when t.startdate>@startdate then t.startdate else @startdate end,
                               case when t.enddate  <@enddate   then t.enddate   else @enddate   end))

/* 
rego       costcentername       startdate  enddate    
---------- -------------------- ---------- ---------- 
AE70PV     51417 64702          2007-01-01 2007-09-17
AK11ZS     51417 34501          2007-01