Calculate between created columns [SOLVED]

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 2008 Forums

Transact-SQL (2008)

Calculate between created columns [SOLVED]

Author

Topic

sql_dan
Starting Member

43 Posts

Posted - 2010-06-30 : 07:30:44

Hi All,

I have a problem that i'm hoping will be quick and easy but so far cant find help on Google or here!

Here is my query...

quote:
select distinct si.ID,SupplierItemCode,
SupplierItemDescription,
[Cost] = CASE WHEN PurchasePrice is null then listprice else purchaseprice END,
[Measure] = CASE WHEN RIGHT(OrderingMeasureDescription,1) in ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') THEN '0' ELSE OrderingMeasureDescription END,
[Cost Per Item] = CASE WHEN PurchasePrice is null then CAST(ListPrice AS MONEY) ELSE CAST(PurchasePrice AS MONEY) END / CASE WHEN RIGHT(OrderingMeasureDescription,1) in ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') THEN CAST('0' AS FLOAT) ELSE CAST(OrderingMeasureDescription AS FLOAT) END
from SupplierItems si
join listprices lp on si.id = lp.supplieritemid
where si.changeddate > '2009'

This doesn't work as SQL can't convert the OrderingMeasuringDescription into float from varchar which ok I get.

All I want to do though is divide the two created columns [Cost] and [Measure] to get the individual cost per item.

Thanks for the help!
Dan

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 07:34:20

repeat both statements in a dividing formula

No, you're never too old to Yak'n'Roll if you're too young to die.

sql_dan
Starting Member

43 Posts

Posted - 2010-06-30 : 07:39:45

quote:
Originally posted by webfred

repeat both statements in a dividing formula

Like I have in the [Cost Per Item] line??

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 07:46:17

Oh I see.
Yes I thougt that but you could get a "divison by zero" error in some cases.
Maybe instead of taking '0' you can take '1' for division?

No, you're never too old to Yak'n'Roll if you're too young to die.

sql_dan
Starting Member

43 Posts

Posted - 2010-06-30 : 08:05:12

quote:
Originally posted by webfred

Oh I see.
Yes I thougt that but you could get a "divison by zero" error in some cases.
Maybe instead of taking '0' you can take '1' for division?

I still get the conversion error!

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 08:13:52

Are you sure that if right(OrderingMeasureDescription,1) is not in (A,B, ...) then OrderingMeasureDescription is a numeric value?

No, you're never too old to Yak'n'Roll if you're too young to die.

sql_dan
Starting Member

43 Posts

Posted - 2010-06-30 : 08:53:34

Thanks for the help webfred!
I had a ton of extra items that weren't covered in the in ('A','B...) converts now just have a divide by 0 error which I can handle!

Thanks again!
Dan

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-30 : 09:10:59

[code];WITH cteYak(ID, SupplierItemCode, SupplierItemDescription, Cost, Measure)
AS (
SELECT si.ID,
si.SupplierItemCode,
si.SupplierItemDescription,
CAST(COALESCE(lp.PurchasePrice, lp.ListPrice) AS FLOAT) AS Cost,
CASE
WHEN si.OrderingMeasureDescription LIKE '%[^0-9]%' THEN CAST(0 AS FLOAT)
ELSE CAST(si.OrderingMeasureDescription AS FLOAT)
END AS Measure,
FROM dbo.SupplierItems AS si
INNER JOIN dbo.ListPrices AS lp ON lp.SupplierItemID = si.ID
WHERE si.ChangedDate >= '20090101'
)
SELECT ID,
SupplierItemCode,
SupplierItemDescription,
Cost,
Measure,
Cost / NULLIF(Measure, 0) AS [Cost Per Item]
FROM cteYak[/code]

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 09:22:03

No, you're never too old to Yak'n'Roll if you're too young to die.

sql_dan
Starting Member

43 Posts

Posted - 2010-06-30 : 09:54:14

quote:

Originally posted by Peso

;WITH cteYak(ID, SupplierItemCode, SupplierItemDescription, Cost, Measure)
AS (
	SELECT		si.ID,
			si.SupplierItemCode,
			si.SupplierItemDescription,
			CAST(COALESCE(lp.PurchasePrice, lp.ListPrice) AS FLOAT) AS Cost,
			CASE
				WHEN si.OrderingMeasureDescription LIKE '%[^0-9]%' THEN CAST(0 AS FLOAT)
				ELSE CAST(si.OrderingMeasureDescription AS FLOAT)
			END AS Measure, 
	FROM		dbo.SupplierItems AS si
	INNER JOIN	dbo.ListPrices AS lp ON lp.SupplierItemID = si.ID
	WHERE		si.ChangedDate >= '20090101'
)
SELECT	ID,
	SupplierItemCode,
	SupplierItemDescription,
	Cost,
	Measure,
	Cost / NULLIF(Measure, 0) AS [Cost Per Item]
FROM	cteYak

Wow PESO Your a genius!! This code is superb!
Thank you so very much!

Dan

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources