OCUW
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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,@endw indow), 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.
You can make your query modular by using
Select [year]=datepart(yyyy,@endw
This way you can ......... Avoid typos.(forgetfulness to update all required changes, the cammcT can be compared to a variable declared earlier.
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).