Calculate Pressure - Most Advanced UOC

• ece.viky

  Right there with Babe

  Points: 727

  More actions

  December 30, 2014 at 11:18 am

  #314179

  Comments posted to this topic are about the item Calculate Pressure - Most Advanced UOC

• ShawnPO

  Valued Member

  Points: 54

  More actions

  February 13, 2015 at 3:05 pm

  #1777624

  I agree with the other threads. I think this would be much more functional and maintainable without a huge CASE statement. It is an interesting thought exercise, and with the Lookup Table method and a couple of other minor mods, you could extend this function to do pretty much any conversion you want.

  But again, SQL _can_ do this; but _should_ it?

  IF OBJECT_ID (N'PressureUnitConversion', N'U') IS NOT NULL

  DROP TABLE dbo.PressureUnitConversion;

  GO

  /* Create a lookup table instead of a big CASE statement. */

  CREATE TABLE dbo.PressureUnitConversion ( FromUnit varchar(64), ToUnit varchar(64), ConversionFactor float );

  INSERT INTO PressureUnitConversion (FromUnit, ToUnit, ConversionFactor)

  VALUES

  ( 'A','B', 1.01325 ),

  ( 'A', 'KP', 101.325 ),

  ( 'A', 'MMM', 760.1275318829707 ),

  ( 'A', 'P', 101325 ),

  ( 'A', 'PSI', 14.69594940039221 ),

  ( 'B', 'A', 0.9869232667160128 ),

  ( 'B', 'KP', 100 ),

  ( 'B', 'MMM', 750.1875468867217 ),

  ( 'B', 'P', 100000.0 ),

  ( 'B', 'PSI', 14.50377438972831 ),

  ( 'KP', 'A', 0.0098692326671601 ),

  ( 'KP', 'B', 0.01 ),

  ( 'KP', 'MMM', 7.501875468867217 ),

  ( 'KP', 'P', 1000.0 ),

  ( 'KP', 'PSI', 0.1450377438972831 ),

  ( 'MMM', 'A', 0.0013155687145324 ),

  ( 'MMM', 'B', 0.001333 ),

  ( 'MMM', 'KP', 0.1333 ),

  ( 'MMM', 'P', 133.3 ),

  ( 'MMM', 'PSI', 0.0193335312615078 ),

  ( 'P', 'A', 9.869232667160128e-6 ),

  ( 'P', 'B', 0.00001 ),

  ( 'P', 'KP', 0.001 ),

  ( 'P', 'MMM', 0.0075018754688672 ),

  ( 'P', 'PSI', 1.450377438972831e-4 ),

  ( 'PSI', 'A', 0.068045961016531 ),

  ( 'PSI', 'B', 0.06894757 ),

  ( 'PSI', 'KP', 6.894757 ),

  ( 'PSI', 'MMM', 51.72360840210053 ),

  ( 'PSI', 'P', 6894.757 )

  ;

  IF OBJECT_ID (N'ufn_Calc_UoC_Pressure', N'FN') IS NOT NULL

  DROP FUNCTION ufn_Calc_UoC_Pressure;

  GO

  CREATE FUNCTION dbo.ufn_Calc_UoC_Pressure ( @FromConversionUnit varchar(64),@ToConversionUnit varchar(64),@UnitValue float )

  RETURNS FLOAT

  WITH RETURNS NULL ON NULL INPUT /* How do we handle NULL inputs? */

  AS

  BEGIN

  /* PRESSURE

  ===============ACRONYM==========

  A - atmosphere

  B - bar

  KP - kilopascal

  MMM - millimeter of mercury

  P - pascal

  PSI - pound per square inch

  */

  IF @FromConversionUnit = @ToConversionUnit

  BEGIN

  RETURN @UnitValue

  END

  DECLARE @ConversionFactor FLOAT = ( SELECT ConversionFactor FROM PressureUnitConversion WHERE FromUnit = @FromConversionUnit AND ToUnit = @ToConversionUnit )

  DECLARE @ReturnValue FLOAT = COALESCE(@UnitValue,0) * COALESCE(@ConversionFactor,0)

  RETURN @ReturnValue

  END

  GO

  /************************************************************

  *********Tests*********

  --examples

  select dbo.ufn_Calc_UoC_Pressure('A','A' ,10)

  select dbo.ufn_Calc_UoC_Pressure('A','B' ,10)

  select dbo.ufn_Calc_UoC_Pressure('A','KP',10)

  select dbo.ufn_Calc_UoC_Pressure('A','MMM',10)

  select dbo.ufn_Calc_UoC_Pressure('A','P',10)

  select dbo.ufn_Calc_UoC_Pressure('A','PSI',10)

  select dbo.ufn_Calc_UoC_Pressure('B','B' ,10)

  select dbo.ufn_Calc_UoC_Pressure('B','A' ,10)

  select dbo.ufn_Calc_UoC_Pressure('B','KP',10)

  select dbo.ufn_Calc_UoC_Pressure('B','MMM',10)

  select dbo.ufn_Calc_UoC_Pressure('B','P',10)

  select dbo.ufn_Calc_UoC_Pressure('B','PSI',10)

  select dbo.ufn_Calc_UoC_Pressure('KP','KP' ,10)

  select dbo.ufn_Calc_UoC_Pressure('KP','A' ,10)

  select dbo.ufn_Calc_UoC_Pressure('KP','B',10)

  select dbo.ufn_Calc_UoC_Pressure('KP','MMM',10)

  select dbo.ufn_Calc_UoC_Pressure('KP','P',10)

  select dbo.ufn_Calc_UoC_Pressure('KP','PSI',10)

  select dbo.ufn_Calc_UoC_Pressure('MMM','MMM' ,10)

  select dbo.ufn_Calc_UoC_Pressure('MMM','A' ,10)

  select dbo.ufn_Calc_UoC_Pressure('MMM','B',10)

  select dbo.ufn_Calc_UoC_Pressure('MMM','KP',10)

  select dbo.ufn_Calc_UoC_Pressure('MMM','P',10)

  select dbo.ufn_Calc_UoC_Pressure('MMM','PSI',10)

  select dbo.ufn_Calc_UoC_Pressure('P','P' ,10)

  select dbo.ufn_Calc_UoC_Pressure('P','A' ,10)

  select dbo.ufn_Calc_UoC_Pressure('P','B',10)

  select dbo.ufn_Calc_UoC_Pressure('P','KP',10)

  select dbo.ufn_Calc_UoC_Pressure('P','MMM',10)

  select dbo.ufn_Calc_UoC_Pressure('P','PSI',10)

  select dbo.ufn_Calc_UoC_Pressure('PSI','PSI' ,10)

  select dbo.ufn_Calc_UoC_Pressure('PSI','A' ,10)

  select dbo.ufn_Calc_UoC_Pressure('PSI','B',10)

  select dbo.ufn_Calc_UoC_Pressure('PSI','KP',10)

  select dbo.ufn_Calc_UoC_Pressure('PSI','MMM',10)

  select dbo.ufn_Calc_UoC_Pressure('PSI','P',10)

  SELECT dbo.ufn_Calc_UoC_Pressure(NULL,'a',10)

  SELECT dbo.ufn_Calc_UoC_Pressure('a',NULL,10)

  SELECT dbo.ufn_Calc_UoC_Pressure('a','b',NULL)

  SELECT dbo.ufn_Calc_UoC_Pressure('a','b','asdf') --- FAILS WITH CONVERSION ERROR

  ************************************************************/