December 22, 2010 at 9:01 am
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 .........
December 22, 2010 at 1:29 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 22, 2010 at 1:58 pm
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???
December 22, 2010 at 2:13 pm
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[/url]
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 22, 2010 at 2:15 pm
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
December 22, 2010 at 2:17 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 22, 2010 at 6:47 pm
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
Author - SQL Server T-SQL Recipes
December 23, 2010 at 7:12 am
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.??
24.8537677961.5OZ TOPPING CHOC FUDGE PACKET 24620 JHS PFG-Thoms Proestler Co 24.85
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy