Help with Query - Products

  • Flag Product_ID_Group Product_ID ID

    0 123456 NULL 456

    0 789012 NULL 456

    1 789012 NULL 456

    Flag Product_ID_Group Product_ID ID

    0 1 456789 132

    0 1 156894 132

    1 1 456789 132

    The data will look like as shown above in the Products table. The product ID Group and Product ID don't always have the information. It is one or the other. Based on the flag column and the number of times the product ID appears it is either a new product or an old product.

    The new vs the old product needs to be chosen based on the "flag" .

    If the product_ID or the product_ID_Group appears twice or 2 rows with 2 different flags - 0 and 1, then that is the Old product.

    If the product_ID or the product_ID_Group appears only once with a flag of 0 then it is the new product

    I need to write the query in such a way that the output from the products table is going to be updated in a different table called Reports.

    ID New Product Old Product

    456 123456 789012

    132 156894 456789

    Thanks for the help in advance

  • How are you choosing which is new product and which is old product? It looks random to me.

  • The new vs the old product needs to be chosen based on the "flag" .

    If the product_ID or the product_ID_Group appears twice or 2 rows with 2 different flags - 0 and 1, then that is the Old product.

    If the product_ID or the product_ID_Group appears only once with a flag of 0 then it is the new product.

  • Try this code, please note how I took the data you provided and included it in a way that can be used by others as well:

    [font="Courier New"]-- test table to hold test data

    DECLARE @products TABLE(flag bit, product_id INT, product_id_group INT, id INT)

    -- insert test data

    INSERT INTO @products

       SELECT      

           0,             123456,                NULL,        456

       UNION ALL

       SELECT

           0,             789012,                NULL,        456

       UNION ALL

       SELECT

           1,             789012,                NULL,        456

       UNION ALL

       SELECT

           0,               1,                       456789,    132    

       UNION ALL

       SELECT

           0,               1,                       156894,    132

       UNION ALL

       SELECT

           1,               1,                       456789,    132

    -- proposed solution

    ;WITH cteCounts AS

       (

       SELECT

           COUNT(DISTINCT flag) AS flag_count,

           ISNULL(product_id_group, product_id) AS product_id,

           id

       FROM

           @products

       GROUP BY

           ISNULL(product_id_group, product_id) ,

           id

       )

       SELECT

           A.id,

           A.product_id AS new_product,

           B.product_id AS old_product

       FROM

           cteCounts A JOIN

           cteCounts B ON

               A.id = B.id AND

               A.product_id <> B.product_id

       WHERE

           A.flag_count = 1

           [/font]

    This works for the data you provided, but I can't guarantee it won't need to be changed to work for the real data.

Viewing 4 posts - 1 through 3 (of 3 total)

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