Checking for price updates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Checking for price updates

Author

Topic

prettypixie
Starting Member

8 Posts

Posted - 2007-04-23 : 15:27:13

Hi all,

I am trying to write a sp that searches 2 joined tables - ActiveArticle & ActiveArticlePrice for any products that exist in the update tables - UpdateArticle & UpdateArticlePrice. They contain the products listed for ALL suppliers so I first have to join both sets of tables based on the Supplier and then search the Active tables for any articles that have the same ArticleNumber, LotSize but different Prices. And I am getting all muddled up on how to do this, this is what I have so far, of course it isn't working:

ALTER PROCEDURE sp_GetPriceListUpdates
@SupplierGUID uniqueidentifier
AS

SELECT 'B' AS FLAG, 'New Price' AS TEXT, * FROM UpdateArticle UA, UpdateArticlePrice UAP

WHERE EXISTS (SELECT * FROM UpdateArticle UA, UpdateArticlePrice UAP
WHERE UA.SupplierArticleGUID=UAP.SupplierArticleGUID AND UA.SupplierGUID=@SupplierGUID

AND UA.UnitPrimeCost NOT IN

(Select * WHERE UA.SupplierArticleNumber AND UAP.LotSize IN (SELECT SupplierArticleNumber AND LotSize FROM ActiveArticle AA, ActiveArticlePrice AAP WHERE AA.SupplierArticleGUID=AAP.SupplierArticleGUID)
)

I would really appreciate any info on how I should be going about this problem!
Thanks,
PP

Kristen
Test

22859 Posts

Posted - 2007-04-24 : 04:15:49

That's a long work from being something which is syntactically correct, let along going to be the tested and proven logic.

I would recommend you build this up in simple stages along the lines of:


SELECT TOP 100 *		-- Lets just see a few examples to start with
FROM	dbo.ActiveArticle AS AA
	JOIN dbo.ActiveArticlePrice AS AAP
		 ON AAP.My = AA.My
	JOIN dbo.UpdateArticle AS UA
		 ON UA.SupplierArticleNumber = AA.SupplierArticleNumber
		AND UA.SupplierArticleGUID = AA.SupplierArticleGUID
	JOIN dbo.UpdateArticlePrice AS UAP
		 ON UAP.SupplierArticleNumber = AAP.SupplierArticleNumber
		AND UAP.LotSize= AAP.LotSize
WHERE	AP.UnitPrimeCost <> UAP.UnitPrimeCost

Its hard to guess which columns mean what, and are joined etc., but hopefully you get the basic idea

If this isn't the answer then you need to post the DDL for the tables involved, a few sample data rows (in the for of some INSERT statements) and an example of what the output should be

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-24 : 04:17:54

Is this same question?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82466

Peter Larsson
Helsingborg, Sweden

prettypixie
Starting Member

8 Posts

Posted - 2007-04-24 : 08:15:06

Thanks Kristen! I will try now your advice, I know I got it all a bit confused.

Hi Peso, well it is based on the same problem but i changed it around a bit and it is a question on a part of the problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources