convert function from Access SQL to T-SQL 2005
Can someone please convert this access sql function for me to work in t-sql 2005.
I am tring to take the selling price minus the cost as one number. And divide that by the original selling price to produce a second number
Thanks :)
=IIf([Selling Price]=0,0,([Selling Price]-Nz([Cost]))/[Se开发者_C百科lling Price])
IIRC it should be something along the lines of;
ISNULL((ISNULL([Selling Price],0) - ISNULL(Cost,0)),0) / ISNULL([Selling Price],0) AS Margin
But here I am getting a divide by Zero error.
any suggestions?
SELECT
CASE
WHEN ISNULL([Selling Price],0) = 0 THEN 0
ELSE ([Selling Price] - ISNULL([Cost],0))/[Selling Price]
END AS fieldName
FROM TableName
CASE
WHEN ISNULL([Selling Price], 0) = 0 THEN 0
ELSE ([Selling Price] - ISNULL([Cost], 0)) / [Selling Price]
END
精彩评论