Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    1

    Unhappy Unanswered: NOT BETWEEN Operator returns all the records when used with DATE datatype.

    Hello: I am new here and are moving from Access to SQLSERVER and just went through some training. I am creating this query which is based of two tables, these are dump tables, there is no PK and FK relationship between these tables. But it has one to many record relationship. Please find the Query which I wrote it returns all the records instead of the records which satisfy the WHERE clause.

    SELECT DISTINCT member_id_2,
    claim_id,
    carrier_program,
    EOB,
    group_id,
    Division,
    group_name,
    fill_date,
    paid_date,
    paid_mo,
    fill_mo,
    drug_cost,
    co_pay,
    total_paid,
    brand_name,
    strength,
    generic_flag,
    formulary_flag,
    quantity_sup,
    days_supply,
    MedEcon_RXData.class,
    prescribin_dea_id,
    provider_name,
    provider_id,
    nabp_id,
    MedEcon_RXData.status
    FROM MedEcon_RXData ,
    member_span
    WHERE MedEcon_RXData.member_id_2 = member_span.member#
    AND (fill_date NOT BETWEEN member_span.YMDEFF1 and member_span.YMDEND1)
    AND (member_span.RIDER_TITLE_PHARMACY IS NOT NULL)
    AND (member_span.VOID <> 'V')
    GROUP BY member_id_2, claim_id, carrier_program, EOB, group_id, Division, group_name, fill_date, paid_date, paid_mo, fill_mo, drug_cost, co_pay, total_paid, brand_name, strength, generic_flag, formulary_flag, quantity_sup, days_supply,
    MedEcon_RXData.class, prescribin_dea_id, provider_name, provider_id, nabp_id,
    MedEcon_RXData.status


    Please advise where I am going wrong.

    Thanks,
    Chetan

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    this is going to be tuff to debug without data. I am going to guess that the "fill_date NOT BETWEEN member_span.YMDEFF1 and member_span.YMDEND1" part of your where clause is the problem as the other three tests are straight forward. What happens if you comment out the above mentioned date test? What happens if you drop the "NOT"?
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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