CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Gopal
Gopal

Reputation: 11972

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/2010 
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

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

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

Upvotes: 0

Views: 423

Answers (3)

Gai
Gai

Reputation: 325

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

Upvotes: 0

Radu Caprescu
Radu Caprescu

Reputation: 993

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))

Upvotes: 1

gbn
gbn

Reputation: 432210

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')

Upvotes: 0

Related Questions