none
Query Finding Two Similar Records

    Question

  • Can someone tell me why the following query doesn't work?  Below the query are the results.  I am trying to find 2 similar records from the same table that happen within the same day.  The one for $49.00 definitely shouldn't have shown up.   It doesn't have another record with the same approved amount.

    SELECT id_num, store_num, approved_Trans_Amt, Insert_Date, rr_num FROM value_card_transactions (NOLOCK) v
    WHERE trans_name = 'SV Replenishment' and response_code = '00' and Insert_Date >= DateAdd(DD, -1, GETDATE())
    AND EXISTS
    (
        SELECT 1 FROM Value_Card_Transactions WHERE id_num = v.id_num and store_num = v.store_num and approved_Trans_Amt = v.Approved_Trans_Amt and trans_name = 'SV Replenishment' and response_code = '00' and Insert_Date >= DateAdd(DD, -1, GETDATE())
    )
    order by id_num

    id_num                       store_num        approved_Trans_Amt    Insert_Date             rr_num
    ---------------------------- ---------------- --------------------- ----------------------- ---------------------------------------
    600649xxxxx01502027          0899             50.00                 2014-07-02 10:15:56.120 39000978596
    600649xxxxx01502027          0899             100.00                2014-07-02 10:15:29.570 39000978595
    627755xxxxx26838108          0897             10.00                 2014-07-02 07:12:46.617 39000978571
    627755xxxxx26838108          0899             49.00                 2014-07-02 10:14:48.467 39000978594
    627755xxxxx26838108          0899             50.00                 2014-07-02 10:14:31.580 39000978593
    627755xxxxx26838108          0899             50.00                 2014-07-02 13:29:03.840 39000978604
    627755xxxxx26838108          0899             100.00                2014-07-02 10:14:13.220 39000978592

    (7 row(s) affected)


    • Edited by rpugh23 Wednesday, July 02, 2014 6:17 PM
    Wednesday, July 02, 2014 6:16 PM

Answers

  • Of course it should be there.  In the exists clause you do not (AFAIK) exclude rows from matching themselves - that would involve comparing whatever the primary (or unique) key values.   Just a guess - but perhaps you should add " and rr_num <> v.rr_num" to the subquery.

    Just to highlight the problem - rows 1, 2, 3, and 7 should not be in your list either if I understand the goal correctly.

    • Marked as answer by rpugh23 Wednesday, July 02, 2014 6:38 PM
    Wednesday, July 02, 2014 6:31 PM

All replies

  • Of course it should be there.  In the exists clause you do not (AFAIK) exclude rows from matching themselves - that would involve comparing whatever the primary (or unique) key values.   Just a guess - but perhaps you should add " and rr_num <> v.rr_num" to the subquery.

    Just to highlight the problem - rows 1, 2, 3, and 7 should not be in your list either if I understand the goal correctly.

    • Marked as answer by rpugh23 Wednesday, July 02, 2014 6:38 PM
    Wednesday, July 02, 2014 6:31 PM
  • That worked.  Thanks.
    Wednesday, July 02, 2014 6:39 PM