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

SQL Server 2000 Forums

SQL Server Development (2000)

Pricing query

Author

Topic

TheOski
Starting Member

5 Posts

Posted - 2006-11-07 : 17:06:46

Hey guys,

I am trying to get product pricing information from one system to another. I got the format knock down for a single item BAD-CLIP.
How can I replace the single item with multiple items so the output would look like at the bottom of the email. A function?

Query to generate a price item for a single item:

SELECT '','','BAD-CLIP', name, '','unit','free',1, (SELECT CAST(Price AS FLOAT) FROM Products WHERE Code = 'BAD-CLIP')* multiplier AS 'Price',
(SELECT CAST(ShipPrice AS Float) FROM Products WHERE Code = 'BAD-CLIP')* shipmultiplier AS 'ShipPrice' ,0,'False' FROM tempKeycodes

Query to pull a list of items:

SELECT Code FROM Products WHERE uid > 9000

Sample output:

,,BAD-CLIP, ,,unit,free,1,15.0,3.0,0,False
,,BAD-CLIP,CANADA,,unit,free,1,15.0,9.0,0,False
,,BAD-CLIP,DSHPLISTLESS25,,unit,free,1,11.25,3.0,0,False
,,BAD-CLIP,DSHPLISTLESS30,,unit,free,1,10.5,3.0,0,False
,,BAD-CLIP,DSHPLISTLESS35,,unit,free,1,9.75,3.0,0,False
,,BAD-CLIP,DSHPLISTLESS40,,unit,free,1,9.0,3.0,0,False
,,BAD-CLIP,DSHPLISTLESS45,,unit,free,1,8.25,3.0,0,False
,,BAD-CLIP,DSHPLISTLESS50,,unit,free,1,7.5,3.0,0,False
,,BAD-CLIP,DSLISTLESS15,,unit,free,1,12.75,3.0,0,False
,,BAD-CLIP,DSLISTLESS25,,unit,free,1,11.25,3.0,0,False
,,BAD-CLIP,DSLISTLESS30,,unit,free,1,10.5,3.0,0,False
,,BAD-CLIP,DSLISTLESS35,,unit,free,1,9.75,3.0,0,False
,,BAD-CLIP,DSLISTLESS40,,unit,free,1,9.0,3.0,0,False
,,BAD-CLIP,DSLISTLESS45,,unit,free,1,8.25,3.0,0,False
,,BAD-CLIP,DSLISTLESS50,,unit,free,1,7.5,3.0,0,False
,,BAD-CLIP,HPLISTLESS40,,unit,free,1,9.0,3.0,0,False
,,BAD-CLIP,HPLISTLESS45,,unit,free,1,8.25,3.0,0,False
,,BAD-CLIP,HPLISTLESS50,,unit,free,1,7.5,3.0,0,False
,,BAD-CLIP,LISTLESS40,,unit,free,1,9.0,3.0,0,False
,,BAD-CLIP,LISTLESS45,,unit,free,1,8.25,3.0,0,False
,,BAD-CLIP,LISTLESS50,,unit,free,1,7.5,3.0,0,False
,,BAD-CLIP,LISTLESS51,,unit,free,1,7.35,3.0,0,False
,,BAD-CLIP,MILITARY,,unit,free,1,15.0,10.5,0,False
,,BAD-CLIP,SELISTLESS40,,unit,free,1,9.0,3.0,0,False
,,BAD-CLIP,SELISTLESS45,,unit,free,1,8.25,3.0,0,False
,,BAD-CLIP,SELISTLESS50,,unit,free,1,7.5,3.0,0,False
,,THE_NEXT_ITEM,,,unit,free,1,15.0,3.0,0,False
,,THE_NEXT_ITEM,CANADA,,unit,free,1,15.0,9.0,0,False
,,THE_NEXT_ITEM,DSHPLISTLESS25,,unit,free,1,11.25,3.0,0,False
,,THE_NEXT_ITEM,DSHPLISTLESS30,,unit,free,1,10.5,3.0,0,False
,,THE_NEXT_ITEM,DSHPLISTLESS35,,unit,free,1,9.75,3.0,0,False
,,THE_NEXT_ITEM,DSHPLISTLESS40,,unit,free,1,9.0,3.0,0,False
,,THE_NEXT_ITEM,DSHPLISTLESS45,,unit,free,1,8.25,3.0,0,False
,,THE_NEXT_ITEM,DSHPLISTLESS50,,unit,free,1,7.5,3.0,0,False
,,THE_NEXT_ITEM,DSLISTLESS15,,unit,free,1,12.75,3.0,0,False
,,THE_NEXT_ITEM,DSLISTLESS25,,unit,free,1,11.25,3.0,0,False
,,THE_NEXT_ITEM,DSLISTLESS30,,unit,free,1,10.5,3.0,0,False
,,THE_NEXT_ITEM,DSLISTLESS35,,unit,free,1,9.75,3.0,0,False
,,THE_NEXT_ITEM,DSLISTLESS40,,unit,free,1,9.0,3.0,0,False
,,THE_NEXT_ITEM,DSLISTLESS45,,unit,free,1,8.25,3.0,0,False
,,THE_NEXT_ITEM,DSLISTLESS50,,unit,free,1,7.5,3.0,0,False
,,THE_NEXT_ITEM,HPLISTLESS40,,unit,free,1,9.0,3.0,0,False
,,THE_NEXT_ITEM,HPLISTLESS45,,unit,free,1,8.25,3.0,0,False
,,THE_NEXT_ITEM,HPLISTLESS50,,unit,free,1,7.5,3.0,0,False
,,THE_NEXT_ITEM,LISTLESS40,,unit,free,1,9.0,3.0,0,False
,,THE_NEXT_ITEM,LISTLESS45,,unit,free,1,8.25,3.0,0,False
,,THE_NEXT_ITEM,LISTLESS50,,unit,free,1,7.5,3.0,0,False
,,THE_NEXT_ITEM,LISTLESS51,,unit,free,1,7.35,3.0,0,False
,,THE_NEXT_ITEM,MILITARY,,unit,free,1,15.0,10.5,0,False
,,THE_NEXT_ITEM,SELISTLESS40,,unit,free,1,9.0,3.0,0,False
,,THE_NEXT_ITEM,SELISTLESS45,,unit,free,1,8.25,3.0,0,False
,,THE_NEXT_ITEM,SELISTLESS50,,unit,free,1,7.5,3.0,0,False

Thanks,

Oskar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 17:33:50

Where is the question?

Peter Larsson
Helsingborg, Sweden

TheOski
Starting Member

5 Posts

Posted - 2006-11-07 : 17:51:21

How can I replace the single item (BAD-CLIP) with multiple items (SELECT Code FROM Products WHERE uid > 9000) so the output would look like at the bottom of the email, a function?

Thanks,

Oskar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 18:04:37

So 'BAD CLIP' and 'THE_NEXT_ITEM' are result of second query?
Use CROSS JOIN!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 18:07:35

Maybe this?

SELECT		'',
		'',
		q.Code,
		tk.name,
		'',
		'unit',
		'free',
		1,
		q.Price * tk.multiplier AS 'Price', 
		q.ShipPrice * tk.shipmultiplier AS 'ShipPrice',
		0,
		'False'
FROM		tempKeycodes tk
CROSS JOIN	(
			SELECT	Code,
				Price,
				ShipPrice
			FROM	Products
			WHERE	uid > 9000
		) q

Peter Larsson
Helsingborg, Sweden

TheOski
Starting Member

5 Posts

Posted - 2006-11-07 : 18:33:48

Works great. Thanks a lot!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources