Need Help formatting query to pull date-related data from Table

  • bkufleitner

    Valued Member

    Points: 64

    I need help correctly formatting the following query to pull some date-related records:

    This is for a table with records that has a column for the record's EFFECTIVE_DATE and a column for the record's INEFFECTIVE_DATE.

    -All 5 conditions (ie the lines in parenthesis) must be met (at the same time)for the query, because I want to pull all of this data at the same time in one single query.

    -The date format in the table is MM/DD/YYYY

    I know I don't have it formatted correctly, but heres what I'm trying to do (the dates need to be put in place of " is in the past", "is in the future" etc )

    select * from TableName

    WHERE

    (EFFECTIVE_DATE is in the past AND INEFFECTIVE_DATE is in the past)

    AND

    (EFFECTIVE_DATE is in the future AND INEFFECTIVE_DATE is in the future)

    AND

    (EFFECTIVE_DATE is in the future AND INEFFECTIVE_DATE is NULL)

    AND

    (EFFECTIVE_DATE is NULL AND INEFFECTIVE DATE is NULL)

    AND

    (EFFECTIVE_DATE is in the past AND INEFFECTIVE_DATE is in the present) ;

    Thanks in Advance!

  • scdecade

    Mr or Mrs. 500

    Points: 561

    These are temporal tables?  When you look at the table name in SSMS it says "TableName (System-Versioned)"?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • bkufleitner

    Valued Member

    Points: 64

    I just put the word "TableName" there to generically take the place of the actual table name (for security reasons)...u can use the word TableName also where required in your query to take the place of the actual tablename also...thanks!!

  • drew.allen

    SSC Guru

    Points: 76686

    Your conditions are contradictory.  You will NEVER get any records that meet all criteria.  Since AND is both commutative and associative, your conditions are equivalent to

    (
    EFFECTIVE_DATE is in the past
    AND EFFECTIVE_DATE is in the future
    AND EFFECTIVE_DATE is NULL
    )
    AND
    (
    INEFFECTIVE_DATE is in the past
    AND INEFFECTIVE_DATE is in the present
    AND INEFFECTIVE_DATE is in the future
    AND INEFFECTIVE_DATE is NULL
    )
    ;

    I suspect what you really want is a CASE expression that will produce a value based on various conditions.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Jonathan AC Roberts

    SSCoach

    Points: 17088

    Maybe some of those AND's should be OR's?

  • bkufleitner

    Valued Member

    Points: 64

    U are correct...I should have worded it as: I want to see records that meet the requirements of each of the line items...hope that helps

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply