Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with Query - Products Expand / Collapse
Author
Message
Posted Tuesday, December 23, 2008 9:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 17, 2011 10:12 PM
Points: 14, Visits: 42
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



Post #624774
Posted Tuesday, December 23, 2008 10:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 11,219, Visits: 12,976
How are you choosing which is new product and which is old product? It looks random to me.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #624860
Posted Tuesday, December 23, 2008 11:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 17, 2011 10:12 PM
Points: 14, Visits: 42
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.



Post #624931
Posted Tuesday, December 23, 2008 1:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 11,219, Visits: 12,976
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:

-- 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
      


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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #625016
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse