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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.