Converting from Access to SQL - CDBL Question

• William Hall Dillon-327038

  SSC Eights!

  Points: 990

  More actions

  May 10, 2006 at 11:37 am

  #68518

  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

• David A. Long

  SSCrazy Eights

  Points: 8421

  More actions

  May 10, 2006 at 11:23 pm

  #637153

  CAST(CONVERT(varchar,[Quantity]*[UnitCost],1) AS float) AS ExtendedCost

  CAST(CONVERT(varchar,[Quantity]*[UnitPrice],1) AS float) AS ExtendedPrice

  CAST([ExtendedPrice]-[ExtendedCost] AS float) AS GrossMargin

  CONVERT(varchar,CAST([GrossMargin]/[ExtendedPrice]*100 AS int))+'%' AS Percentage

  Andy

• Udo Mader

  SSC Enthusiast

  Points: 113

  More actions

  May 11, 2006 at 7:23 am

  #637233

  case when extendedprice <> 0 then CONVERT(varchar,CAST(round([GrossMargin]/[ExtendedPrice]*100, 2) AS float)) else '0.00' end + '%' as percentage

  udo