SQL Query a date range based on another date range

Using TSQL I am looking for a way to pull a list of committee members based on the timeframe they were actively on the committee to be isolated by year. Unfortunately I have a start and end date that in most cases represents a three year assignment, so the standard BETWEEN clause doesn’t appear to answer the question.

Member                        StartDate      EndDate
John             7-1-2013      8-15-2016
Mary              5-1-2012      2-15-2014
Jane            4-1-2011      8-15-2014

I need to determine if the start and end date range is active during a date range say 7-1-2013 thru 6-30-2014 then the fiscal year shown would be 2014.

Thanks,
Mike
OCUWAsked:
Who is Participating?
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.

arnoldCommented:
You can do a two between with and
Max of  start date and min of end date when the date of interest is compared to the start and end dates, if the result is the same, that means the date is valid for the start period,  and the min date of the comparative end date and the enddate will give you the appropriate period.  
In your example you
Want to validate whether and to what extent the three referenced will fit into your window of interest
7/1/2013-6/30/2014 for John
7/1/2013-2/15/2014 for mary
7/1/2013-6/30/2014 for Jane.

Mary 's example should her partial service .........

Declare @stratwindow datetime
Declare @endwindow datetime
Set @startwindow='07/01/2013'
Set @endwindow='06/30/2014'

Select * from tablename where @endwindow>@startwindow and @startwindow between startdate and enddate and @endwindow between startdate and enddate

The first is a sanity check to make sure the defined window is correctly set.
You need to answer the question about Mary who is valid in the range of  7/1/2013-2/15/2014.
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
arnoldCommented:
If you need information coming back to reflect the active window, comparative/conditional option in the select

I think you will need to use cast/convert to make sure you are comparing datetime and similary formatted.
Select name,
      ( If @startwindow > startdate then @startwindow else startdate ) as startdate,
      ( If @endwindow > enddate then enddate else @endwindow)  as enddate,
From tablename where (the clause from the earlier comments).
0
OCUWAuthor Commented:
This worked like a charm,

Thanks for the help!



 Declare @startwindow datetime
 Declare @endwindow datetime
 Set @startwindow='07/01/2012'
 Set @endwindow='06/30/2013'
 
select [YEAR] = '2012',  ID , stdate,  endate
from frdcsmst
where cmmcat = 'BOD' and
@endwindow>@startwindow and @startwindow between stdate and endate and @endwindow between stdate and endate
0
arnoldCommented:
Note the person active you've define as for the entire period and that is what is being returned.

You can make your query modular by using
Select [year]=datepart(yyyy,@endwindow), ID, startdate, enddate .....

This way you can ......... Avoid typos.(forgetfulness to update all required changes, the cammcT can be compared to a variable declared earlier.
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.