0

Hi I have a table with the following fields:

person
from_dt
to_dt
house_num

it describes what house number a person lived in, and gives the from_dt and to_dt. The from_dt date back to 2000-01-01 and the to_dt dates to 2012-01-01

The Question:

I want to only select people who have lived for some point between 2004 and 2009.

ie exclude people like

person  from_dt     to_dt       house_num
-----------------------------------------
dave    2000-01-01  2002-01-01  34

however keep someone like:

person  from_dt     to_dt       house_num
-----------------------------------------
susan   2008-01-01  2009-06-01  93

I am having trouble with the logic here. Does anyone have any suggestions of how I can use the from_dt and to_dt to only include people who have lived in a house for a day or more during 2009?

Thanks very much.

fthiella
  • 48,073
  • 15
  • 90
  • 106
brucezepplin
  • 9,202
  • 26
  • 76
  • 129

1 Answers1

1

Not tested so might have typos but here is the idea - from has to be in the range or to has to be in the range or they have to start before and end after.

select * 
from table
where (year(from_dt) < 2009 and year(from_dt) > 2004) 
   or (year(to_dt) < 2009 and year(to_date) > 2004)
   or (year(from_dt) < 2004) and year(to_date) > 2009)

You could also test for NOT both before or both after.

select *
from table
where not ((year(from_date) < 2004 and year(to_dt) < 2004)
         or(year(from_date) > 2009 and year(to_dt) > 2009))

Or (as Nikola pointed out)

select * 
from table
where year(from_dt) <= 2009 and year(to_dt) >= 2004
Hogan
  • 69,564
  • 10
  • 76
  • 117
  • this looks good to me. Just tested it and got sensible numbers back. Thanks very much. - Just noticed, would this include people who have started living before 2004 and live into the 2004-2009 study window? I don't think it does. – brucezepplin Mar 08 '13 at 11:19
  • I'm sorry, I have to correct this example: start of first range must be before end of second one, and end of first range must be after the start of second one. So: `year(from_dt) <= 2009 and year(to_dt) >= 2004`. And it would be better not to use functions for performance sake. – Nikola Markovinović Mar 08 '13 at 11:33
  • @NikolaMarkovinović - Thanks for your eagle eye on that typo. I'm interested to understand what you mean by "better not to use functions for performance sake". Which functions are you talking about? `year()`? Don't you think that `year()` will perform better than say... >= '1-1-2004'? – Hogan Mar 08 '13 at 20:22
  • No, because it is not [sargable](http://en.wikipedia.org/wiki/Sargable), that is, query optimizer is unable to use index because possibly indexed column changes value. Very first example in wiki article I have linked here shows this exact situation, year vs. date. – Nikola Markovinović Mar 08 '13 at 22:26