Single Query with two condition

Using SQL Server 2005

Leave Table

ID StartDate EndDate 

001 02/03/2010 02/03/2010
002 02/03/2010 null
…

Event Table

ID Date 

001 02/03/20开发者_如何转开发10 
001 02/04/2010 
001 02/05/2010 
002 02/03/2010 
002 02/04/2010 
002 02/05/2010 
….

All the date column datatype is datetime.

I have n number of id.

I want to make a status column, comparing the date from event table with end date from the leave table.

Conditions 1

• If Start and End Date is available in the leave table for the particular id, then it should display as "leave" in the event table for the particular date

Query

Select 
    id, date
    , CASE WHEN t2.id IS NULL THEN null ELSE ‘Leave’ END AS status 
from event table as t1 
left outer join leave table as t2 on 
    t1.id = t2.id and t1.date between t2.startdate and t2.enddate

Conditions 2

• If Start Date is available and End Date is not available in the leave table for the particular id , then it should display as "Leave" in the event table for the remaining dates

Query

 Select 
        id, date, 
        , CASE WHEN t2.id IS NULL THEN null ELSE ‘Leave’ END AS status 
    from event table as t1 
    left outer join leave table as t2 on 
        t1.id = t2.id and t1.date > t2.startdate

Expected Output

ID Date Status

001 02/03/2010  Leave
001 02/04/2010  
001 02/05/2010 
002 02/03/2010 Leave
002 02/04/2010 Leave
002 02/05/2010 Leave
….

The above query is working, but i want to make into single query with the two condition

How to make a query for the above condition.

Need Query Help

---

Maybe this will work for you:

    Select 
    id, date
    , CASE WHEN t2.id IS NULL THEN null ELSE ‘Leave’ END AS status 
from event table as t1 
left outer join leave table as t2 on 
    t1.id = t2.id 
where (t1.date between t2.startdate and t2.enddate)
or (t2.enddate is null and (t1.date > t2.startdate))

---

Try this. It's basically the same query if you use a dummy end date in the future for the leave table.

I chose 06 Jun 2079 as the highest smalldatetime value but you can change this as needed

Select 
    id, date
    , CASE WHEN t2.id IS NULL THEN null ELSE ‘Leave’ END AS status 
from
   eventtable as t1 
   left outer join
   leave table as t2 on t1.id = t2.id and
          t1.date between t2.startdate and ISNULL(t2.enddate, '20790606')

---

I think that using the 'or' condition with the dates can become a performance issue (index usage etc..)

maybe a union (or union all) will work better:

Select 
    id, date
    , CASE WHEN t2.id IS NULL THEN null ELSE ‘Leave’ END AS status 
from event table as t1 
left outer join leave table as t2 on 
    t1.id = t2.id and t1.date between t2.startdate and t2.enddate

Union

Select 
        id, date, 
        , CASE WHEN t2.id IS NULL THEN null ELSE ‘Leave’ END AS status 
    from event table as t1 
    left outer join leave table as t2 on 
        t1.id = t2.id and t1.date > t2.startdate