Link to home
Start Free TrialLog in
Avatar of OCUW
OCUWFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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).
Avatar of OCUW

ASKER

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
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.