Checking to see if a member enrolled in a certain time period

I have a table that has two fields enroll_date and exit_date.  A member may have multiple records for different enrollments.
Example:  Enroll                                    Exit
                01-01-2009                           06-30-1009
                07-01-2009                           12-31-2009
                01-01-2010                           06-30-2010

Is there a way to check to see if a member was enrolled at any time between two dates.
Example:

I would like to check to see if a member was enrolled between 03-01-2009 and 07-31-2009?
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
 
LowfatspreadCommented:
use an EXISTS test...

select memberid
 from yourtable as a
 where exists (select memberid from yourtable as x
       where a.memberid=x.memberid
        and x.enroll <= '20090301' and x.exit >= 20090731')

would be the basic sql required... adjust the equality test depending on wether the enrollment is active on the exit date...

however if you are saying that the period you are testing for could span multiple enrollment periods...

then the test basically needs to be reversed  and you need a calendar table containing all the dates between the start and the end of the period in question... in that case you are lot for the situation where ther doesn't exist a day within the range which isnt covered by an enrollment....





select memberid
 from yourtable as a
cross join calendar as c
 where not exists (select memberid from yourtable as x
       where a.memberid=x.memberid
        and c.calendardate <  x.enroll or c.calendardate > x.exit)
 and c.calendardate between '20090301' and '20090731'


which actual database system are you using?
0
 
Aloysius LowCommented:
i'm thinking a simple where condition would suffice:
where enrol_date <= 01mar2009 or exit_date >= 31jul2009

is this not getting the result you wanted?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.