Results 1 to 4 of 4
  1. #1
    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. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try something like...

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

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though I'm not sure that I understand the requiremens enough,
    those examples might help you.

    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. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    brucezepplin's "Netiquette" was fine.
    Celko was being rude.
    Celko's code was incorrect.
    Tonkuma's code is correct.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •