how do i find out if one timeperiod is within another timeperiod in sql?

Question

lets say i have a supervisor that is contracted to work from date_a to date_b (e.g. 01/01/2005 to 01/01/2010). I have just recruited and new employee which needs a supervisor. the new employee is contracted to work from date_c to date_d. A supervisor can only be assigned to an employee, if the employee's contract timeperiod falls within supervisor's contract timeperiod. How do i work this out in SQL (Microsoft SQL Server 2000)?

Answer 1

in other word has condition
1. employe contract start(date_c) >= supervisor contract start
(date_a)
or
date_c >= date_a
or
date_a <= date_c


2. employe contract end(date_d) <= supervisor contract end(date_b)
or
date_d <= date_b
or
date_b>= date_d


Mervin one almost correct

select * from tablename where data_a<=date_c and date_b>=date_d

Answer 2

Try this fragment... you'll need to massage it to fit your constraints, but....

select
case when
startdate >= {ts '2001-05-24 10:30:29.123'}
AND enddate <= {ts '2001-05-24 10:30:29.123'}
AND
startdate >= {ts '2002-05-24 10:30:29.123'}
and enddate <= {ts '2002-05-24 10:30:29.123'}

then 1 else 0 end
from supervisor

the timestamp 2001-05-24 is May 24, 2001 (for when the employee starts) and 2002-05-24 is the end date. Use whatever date you need. You may have to join on your employee table to get that date... but I am assuming you know the dates...

Answer 3

select * from table where data_adate_d