Results 1 to 2 of 2

Thread: Query Help

  1. #1
    Join Date
    Feb 2009
    Posts
    4

    Unanswered: Query Help

    Hello all,

    I have 2 tables which will produce a final query result, the first table is a base table similar to this:

    Item Week Qty
    111 1 10
    111 2 20
    222 2 30
    222 3 40
    333 3 50

    The second table, call it a substitution table, is as follows:

    ItemOld ItemNew
    333 444
    111 222

    The substitution table has a dual role:

    1. If ItemOld is found in the base table, while its corresponding ItemNew is not, then simply replace the Item number in the base table with ItemNew.

    2. If ItemOld is found in the base table, and its corresponding ItemNew is also in the base table, then;
    a) replace the Item number with ItemNew
    b) exclude records where the week of Item is not matched by the same week for another record with the same ItemNew

    So the final result would look like this:

    Item Week Qty
    222 2 20
    222 2 30
    222 3 40
    444 3 50

    Only 1 record was deleted, the first record because Item 111 with week 1 did not have a corresponding record of 222 with the same week number.

    My apologies for such a lengthy question. I tried to do this completely with queries, as that is necessary in my case. In the end I used a total of 6 select queries, however, it seems like that might be inefficient and too much.

    Does anyone have a simple solution?

    Thanks

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    there's a simple solution for the replacement; by joining the tables in a query and using a new calculated value/column with some iif logic. One can display the replacement value rather than the original value without a problem.

    I don't think there is a simple solution for the second part where you want to exclude a row. It would be simple if a human was involved. But it is going to be a complicated sql statement to dynamically compare value sets across a dynamic group. Off the cuff I see you want to exclude the minimum but crafting that sql isn't something at my fingertips.

    Not sure this helps. Sorry.
    www CahabaData 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
  •