Include data in resultset not fulfilling the filter condition without using temp tables

Please use following sample data.

declare @tbl table (id int, fid int, arrival datetime, created datetime)
insert into @tbl select 1, 10, DATEADD(dd, -10, GETDATE()), DATEADD(dd, -6, getdate())
insert into @tbl select 1, 10, DATEADD(dd, -10, GETDATE()), DATEADD(dd, -6, getdate())
insert into @tbl select 1, 10, DATEADD(dd, -10, GETDATE()), null
insert into @tbl select 2, 20, DATEADD(dd, -3, GETDATE()), DATEADD(dd, -2, getdate())
insert into @tbl select 2, 20, DATEADD(dd, -3, GETDATE()), null

I would like to have all rows between arrival '2011-02-25' and '2011-02-28' which created after this date '2011-02-20' including created date null.

Query1:

select * from @tbl 
where arrival >= '2011-02-25' and arrival < '2011-02-28'
and created >= '2011-02-20'

Above query fetch two rows but I need 3rd row of FID = 10 which has created date null

Qery2: select row of FID = 20 which I don't need because it is not in range of Arrival date.

select * from @tbl 
where arrival >= '2011-02-25' and arrival < '2011-02-28'
and created >= '2011-02-20' OR created is null

This is sample data. The original query fetch data from different table and has joins with 10+ tables so I don't want to run query again to include rows in temp table.

Thanks.

EDIT: Sorry, wanted to ask this but put wrong question. Thanks for your help.

declare @tbl table (id int, fid int, arrival datetime, created datetime)
insert into @tbl select 1, 10, DATEADD(dd, -10, GETDATE()), DATEADD(dd, -6, getdate())
insert into @tbl select 1, 10, DATEADD(dd, -10, GETDATE()), DATEADD(dd, -6, getdate())
insert into @tbl select 1, 10, null, DATEADD(dd, -6, getdate())
insert into @tbl select 2, 20, DATEADD(dd, -3, GETDATE()), DATEADD(dd, -2, getdate())
insert into @tbl select 2, 20, null, DATEADD(dd, -2, getdate())

select * from @tbl
where arrival >= '2011-02-26' and arrival < '2011-02-28'

Need the third row of fid = 10 too whe开发者_运维问答re arrival date is NULL

This might do what you want.

;with cte as
(
  select distinct fid
  from @tbl
  where
    arrival >= '2011-02-26' and
    arrival < '2011-02-28'
)
select *
from cte as C
  inner join @tbl as T
    on C.fid = T.fid
where
  (arrival >= '2011-02-26' and
   arrival < '2011-02-28') or
  arrival is null

I think should work:

select * from @tbl 
where (arrival >= '2011-02-25' and arrival < '2011-02-28')
and (created >= '2011-02-20' or created is Null)

As per your Edit you will need to do this:

select * from @tbl 
where ((arrival >= '2011-02-25' and arrival < '2011-02-28') or arrival is null)
and (created >= '2011-02-20' or created is Null)

This will return the 3rd FID = 10 row, however it will also return the row ID = 2 and FID = 20 because that row also satisfies the filter conditions.

This is a slightly different take from the more obvious answers posted above

select * from @tbl where arrival >= '2011-02-25' and arrival < '2011-02-28' and COALESCE(created,'2011-02-25') >= '2011-02-20'