December 23, 2008 at 9:02 am
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
December 23, 2008 at 10:33 am
How are you choosing which is new product and which is old product? It looks random to me.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 23, 2008 at 11:57 am
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.
December 23, 2008 at 1:57 pm
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply