Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
| Search | Today's Posts | Mark Forums Read
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 23rd, 2006, 02:41 PM
Authorized User
 
Join Date: Jun 2004
Location: York, East Yorkshire, United Kingdom.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default Select min(price) Group By sku = duplicates

This is driving me nuts, I can't crack it or find the information I need to crack it....

INSERT INTO tbl_product_production
(sku,[cost del exc],distributor)
SELECT LTRIM(RTRIM(LOWER(sku))), MIN([cost del exc]),distributor
FROM tbl_product_stage T1
WHERE (NOT EXISTS (SELECT LOWER(RTRIM(LTRIM(sku)))
FROM tbl_product_production T2
WHERE (lower(rtrim(ltrim(T1.sku)))) = (LOWER(RTRIM(LTRIM(T2.sku))))))GROUP BY LTRIM(RTRIM(LOWER(sku))), distributor

This works, however if I have a part number (6677), which is available from three different distribution houses, I want it to insert into the table the lowest price, the sku part number and the distributor which has the lowest price.... BUT it inserts three records, one for each distributor, not just one...

INSERT INTO tbl_product_production
(sku,[cost del exc])
SELECT LTRIM(RTRIM(LOWER(sku))), MIN([cost del exc]),distributor
FROM tbl_product_stage T1
WHERE (NOT EXISTS (SELECT LOWER(RTRIM(LTRIM(sku)))
FROM tbl_product_production T2
WHERE (lower(rtrim(ltrim(T1.sku)))) = (LOWER(RTRIM(LTRIM(T2.sku))))))GROUP BY LTRIM(RTRIM(LOWER(sku)))

If I run this it works as it should... but I dont get the distributor detail.

I thought of running an insert only inserting the sku, then running an update afterwards, however it is SOOOOO slow and I should have to right?

I'm sure I am missing something fundemental....... Any help greatfully received... Stuart.
  #2 (permalink)  
Old February 23rd, 2006, 04:05 PM
Authorized User
 
Join Date: Jun 2004
Location: York, East Yorkshire, United Kingdom.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Also, in addition, I need to select the lowest price that has stock. but take into account that none of them might not have stock so display the lowest price anyway if this is the case.

[sku],(min([cost del exc])),[stock level],[distributor]

Thought it important to point it out now.
  #3 (permalink)  
Old March 6th, 2006, 01:36 AM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Try running your original query as a select into a temp table. Then do a MIN(price) from there.



Similar Threads
Thread Thread Starter Forum Replies Last Post
Restart new group number in Group Footer sukarso Crystal Reports 2 October 13th, 2006 12:11 PM
Select the latest date, then the lowest price. Stuart Stalker SQL Server DTS 2 October 12th, 2006 03:42 AM
Select From Group? chris_jones199 SQL Language 4 October 12th, 2005 05:08 AM
sorting duplicates in group by Dean Lovell SQL Language 1 September 7th, 2005 01:30 PM
get a lowest price Jane SQL Language 1 March 11th, 2005 10:02 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.