Date range question
-
I need some help getting the corresponding records from my db. I can't seem to get it right. I am hoping you guys can shine some light on this.
I am storing contracts that have a startdate and enddate. Then I have implemented a report that let's you select one or more years and displays the corresponding rates. Let's simply focus on the years.
- Say I have a contract that starts in 2007 and ends in 2010.
- The user selects 2007 and 2008 as years he wants to see
My first approach was to determine the minimum and the maximum year and to create a BETWEEN statement as:
*** where (startYear between MINYEAR and MAXYEAR) OR (endYear between MINYEAR and MAXYEAR)
Previous statement returns the desired records.
But now let's say the user only selects 2008.
The previous statement worked ok as long as the selected year either corresponds with a startyear or an endyear.
Now this query would not return any records. Neither the startYear nor the endYear of the contract matches between 2008 and 2008.
My 2nd approach with an IN-clause does not work for a single year either when the selected year is not a start- or endYear:
*** where startYear IN (2008) OR endYear IN (2008)
I will omit the obviously not working statements like startYear >= minYear and/or endYear <= maxYear, since these would either
return too few or too many results.
I am hoping somebody can give me a hint how to tackle this on first glance "simple issue". For I don't see it.
-
You should be able to get the overlap using this (DDL and sample data would help)
select startYear,endYear,
.
.
from Contracts
where MAXYEAR >= startYear
and MINYEAR =< endYear
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
That's it !!! Thanks a million Mark.
This is exactly what I need.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply