May 10, 2006 at 11:25 am
Hi All
I need some help converting these into the proper statements that will work in a Stored Procedure for SQL:
CDbl(Format(([Quantity]*[UnitCost]),"General Number")) AS ExtendedCost
CDbl(Format(([Quantity]*[UnitPrice]),"General Number")) AS ExtendedPrice
CDbl([ExtendedPrice]-[ExtendedCost]) AS GrossMargin
IIf([ExtendedPrice]<>0,Format(([GrossMargin]/[ExtendedPrice]),"Percent")) AS Percentage
Thanks for your help
Bill
May 10, 2006 at 11:33 am
You're probably better off to post this to the Access forum.
May 10, 2006 at 11:39 am
Pam is right here, but I'll take a stab at this.
I'm guessing you are doing this in a SELECT statement in SQL and these are the items you are selecting? Also, I am assuming a money type for the first three and a float type for the last one since the types are different in SQL. You will probably want to tweak this...
SELECT
CONVERT ( money , [Quantity] * [UnitCost] ) AS ExtendedCost,
CONVERT ( money , [Quantity] * [UnitPrice] ) AS ExtendedPrice,
CONVERT ( money , [ExtendedPrice] - [ExtendedCost] ) AS GrossMargin,
CASE WHEN [ExtendedPrice] <> 0 THEN CONVERT ( float , [GrossMargin] / [ExtendedPrice] ) END AS Percentage
FROM [TABLENAME]
May 10, 2006 at 11:49 am
I just realized you're probably going to want to create variables instead because I don't think you can reference computed columns in other computed columns. In that case, it will probably look like this...
DECLARE @ExtendedCost money
DECLARE @ExtendedPrice money
DECLARE @GrossMargin money
DECLARE @Percentage float
SELECT @ExtendedCost = [Quantity] * [UnitCost],
@ExtendedPrice = [Quantity] * [UnitPrice]
FROM [TABLENAME]
SET @GrossMargin = @ExtendedPrice - @ExtendedCost
IF @ExtendedPrice <> 0
BEGIN
SET @Percentage = @GrossMargin / @ExtendedPrice
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply