Post reply

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

  • December 12, 2019 at 7:39 pm

    #3707508

    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!

  • December 12, 2019 at 9:08 pm

    #3707514

    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

  • December 12, 2019 at 9:13 pm

    #3707515

    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!!

  • December 12, 2019 at 9:33 pm

    #3707518

    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

  • December 12, 2019 at 9:48 pm

    #3707525

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

  • December 12, 2019 at 11:09 pm

    #3707538

    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 5 (of 5 total)

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