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

Identify duplicate records where all fields, except one field, are the same. Expand / Collapse
Author
Message
Posted Wednesday, December 22, 2010 9:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 16, 2011 6:11 AM
Points: 7, Visits: 42
I have a temp table with 9 fields.( Customer number, size, description, brand, etc.) The table also has a field call bid_price, if two records are identical except for bid_price, I what to flag those records some way so I know I have multiple prices for the same item.

CREATE TABLE #MX (
item_num int
,pack int
,item_size varchar(10)
,old_desc1 varchar(50)
,manufacturer_item_nbr varchar(25)
,brand_name varchar(25)
,opco_desc varchar(50)
,bid_price_old money
,flag int )

INSERT INTO #MX
select i.item_num
,i.pack
,i.item_size
,bi.old_desc1
,ii.manufacturer_item_nbr
,b.brand_name
,o.opco_desc
,bd.bid_price_old
,0 as flag
from opco_item_tbl i
inner join cd_item_tbl ii on i.item_num = ii.item_nbr
inner join cd_base_item_tbl bi on ii.base_item_nbr = bi.base_item_nbr
inner join cd_brand_tbl b on bi.brand_nbr = b.brand_nbr
inner join bid_detail_tbl bd on i.item_num = bd.item_num and i.opco_num = bd.opco_num
inner join bid_customer_tbl bc on bd.bid_num = bc.bid_num and bd.opco_num = bc.opco_num
inner join opco_customer_tbl c on bc.customer_ident = c.customer_ident
inner join pfg_opco_tbl o on i.opco_num = o.opco_num
where
i.item_num in Lst.Lst35
)
and
chain_code = '*ABC'
group by .........
Post #1038329
Posted Wednesday, December 22, 2010 1:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
try something like this.

select item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag, COUNT(distinct bid_price_old) as bidCount
from #mx
group by item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1038472
Posted Wednesday, December 22, 2010 1:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 16, 2011 6:11 AM
Points: 7, Visits: 42
Sean Lange (12/22/2010)
try something like this.

select item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag, COUNT(distinct bid_price_old) as bidCount
from #mx
group by item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag



This is good the query returns several records with a count of 2 (duplicate bid prices) but..... I also need to be able to see the duplicate prices. It would be nice if i could return a field with both prices in one field like: $24.15 - $24.95. Is this possible???
Post #1038482
Posted Wednesday, December 22, 2010 2:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
Jeff Moden has written some fantastic article on this site about doing that sort of thing. I think you would find his articles can get you the exactly what you need. It will be an excellent learning experience too.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1038494
Posted Wednesday, December 22, 2010 2:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:20 AM
Points: 12,927, Visits: 32,332
based on Sean's post, i think if you join the grouping back to the original table, you can at least see the dupes :
SELECT #mx.bid_price_old, #mx.* FROM #mx
LEFT OUTER JOIN (
select item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag,
COUNT(distinct bid_price_old) as bidCount
from #mx
group by item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag )
myAlias
ON #mx.item_num = myAlias.item_num
AND #mx.pack = myAlias.pack
AND #mx.item_size = myAlias.item_size
AND #mx.old_desc1 = myAlias.old_desc1
AND #mx.manufacturer_item_nbr = myAlias.manufacturer_item_nbr
AND #mx.brand_name = myAlias.brand_name
AND #mx.opco_desc = myAlias.opco_desc
AND #mx.flag = myAlias.flag




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1038497
Posted Wednesday, December 22, 2010 2:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
Excellent! I can tell it is almost quitting time....for the rest of the year.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1038500
Posted Wednesday, December 22, 2010 6:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 5,367, Visits: 8,986
GElwood (12/22/2010)
I have a temp table with 9 fields.( Customer number, size, description, brand, etc.) The table also has a field call bid_price, if two records are identical except for bid_price, I what to flag those records some way so I know I have multiple prices for the same item.


This sounds like you should be able to use the DENSE_RANK() function (partitioned by the first nine columns, ordered by the bid_price column) to get you the base information. Duplicates would be where the DENSE_RANK() returns a number > 1.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1038573
Posted Thursday, December 23, 2010 7:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 16, 2011 6:11 AM
Points: 7, Visits: 42
Lowell (12/22/2010)
based on Sean's post, i think if you join the grouping back to the original table, you can at least see the dupes :
SELECT #mx.bid_price_old, #mx.* FROM #mx
LEFT OUTER JOIN (
select item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag,
COUNT(distinct bid_price_old) as bidCount
from #mx
group by item_num, pack, item_size, old_desc1, manufacturer_item_nbr, brand_name, opco_desc, flag )
myAlias
ON #mx.item_num = myAlias.item_num
AND #mx.pack = myAlias.pack
AND #mx.item_size = myAlias.item_size
AND #mx.old_desc1 = myAlias.old_desc1
AND #mx.manufacturer_item_nbr = myAlias.manufacturer_item_nbr
AND #mx.brand_name = myAlias.brand_name
AND #mx.opco_desc = myAlias.opco_desc
AND #mx.flag = myAlias.flag





This looks like it has potential but is still not showing the 2 different bid prices? For this record the bid prices should be 24.85 and 21.46.??

[b]24.85 37677 96 1.5OZ TOPPING CHOC FUDGE PACKET 24620 JHS PFG-Thoms Proestler Co 24.85
Post #1038774
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse