JRG JRG - 2 years ago 63
SQL Question

Query to find records that were active within a range of dates

I have a database table containing the start and expire date of employee certifications. I need to query the table to get employees who were certified during a date range.

Example table:

EmpID | FromDate | ExpireDate
---------+-------------+--------------
1 | 2/1/2011 | 3/1/2015
2 | 10/1/2010 | 2/1/2013
3 | 3/1/2013 | 5/30/2013
4 | 11/1/2000 | 3/1/2012
5 | 5/6/2013 | 5/30/2017


If a user wants to find employees that were certified between 5/1/2013 and 5/30/2013 they should get back employee ids 1, 3, and 5. Note that employee id 5 was certified during the date range though the FromDate is after the start date of the query (5/1/2013).

Hope that makes sense. I'm having a hard time wrapping my head around how to write the query.

Answer Source

Assuming that your dates are really stored as dates, the following should work:

select t.*
from t
where FromDate <= '5/30/2013' and
      EndDate >= '5/1/2013';

The logic is that FromDate is less than the end of the period and EndDate is after the beginning. That will get any overlap at all with the period.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download