# Thread: Exclude certain time periods

1. Registered User
Join Date
Jul 2011
Posts
33

## Unanswered: Exclude certain time periods

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.

2. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483

for between 2004 and 2009
Code:
``` WHERE from_dt <= '2009-12-31'
AND to_dt   >= '2004-01-01'```

3. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Though I'm not sure that I understand the requiremens enough,

Example 1: select people who have lived for some point between 2004 and 2009.
Code:
```------------------------------ Commands Entered ------------------------------
WITH
a_table
( person , from_dt , to_dt , house_num ) AS (
VALUES
( 'dave'   , '2000-01-01' , '2002-01-01' , 34 )
, ( 'susan'  , '2008-01-01' , '2009-06-01' , 93 )
, ( 'alice'  , '2000-01-01' , '2004-01-01' , 02 )
, ( 'betty'  , '2000-01-01' , '2011-01-01' , 03 )
, ( 'chris'  , '2009-12-31' , '2010-12-31' , 04 )
, ( 'jenny'  , '2010-01-01' , '2010-12-31' , 39 )
)
SELECT person
, from_dt
, to_dt
, house_num
FROM  a_table
WHERE
from_dt <= '2009-12-31'
AND to_dt   >= '2004-01-01'
;
------------------------------------------------------------------------------

PERSON FROM_DT    TO_DT      HOUSE_NUM
------ ---------- ---------- -----------
susan  2008-01-01 2009-06-01          93
alice  2000-01-01 2004-01-01           2
betty  2000-01-01 2011-01-01           3
chris  2009-12-31 2010-12-31           4

4 record(s) selected.```

Example 2: select people who have NOT lived for ANY(or ALL) point between 2004 and 2009.
Code:
```------------------------------ Commands Entered ------------------------------
WITH
a_table
( person , from_dt , to_dt , house_num ) AS (
VALUES
( 'dave'   , '2000-01-01' , '2002-01-01' , 34 )
, ( 'susan'  , '2008-01-01' , '2009-06-01' , 93 )
, ( 'alice'  , '2000-01-01' , '2004-01-01' , 02 )
, ( 'betty'  , '2000-01-01' , '2011-01-01' , 03 )
, ( 'chris'  , '2009-12-31' , '2010-12-31' , 04 )
, ( 'jenny'  , '2010-01-01' , '2010-12-31' , 39 )
)
SELECT person
, from_dt
, to_dt
, house_num
FROM  a_table
WHERE
NOT
(    from_dt <= '2009-12-31'
AND to_dt   >= '2004-01-01'
)
;
------------------------------------------------------------------------------

PERSON FROM_DT    TO_DT      HOUSE_NUM
------ ---------- ---------- -----------
dave   2000-01-01 2002-01-01          34
jenny  2010-01-01 2010-12-31          39

2 record(s) selected.```
Last edited by tonkuma; 03-11-13 at 11:05. Reason: Remove last paragraph, beggining "By the way, ...".

4. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595