December 30, 2014 at 11:18 am
Comments posted to this topic are about the item Calculate Volume - Most Advanced UOC
January 30, 2015 at 7:43 am
Vignesh,
This is a useful function, but I think it would be hard to maintain as adding a new unit of measure would be tedious. Converting the input value to a base unit (e.g. cubic centimeters) and then converting this to the output value would be much simpler.
Create Function [dbo].[fn_calc_uoc_volume] (@UnitFrom nvarchar(100), @UnitTo nvarchar(100), @Value float)
Returns Float
As
Begin
--
-- Convert the input value to a base unit of cubic centimeters.
--
Set @Value = Case @UnitFrom
When 'cubic centimeter' Then @Value
When 'cubic feet' Then @Value * 28316.846592
When 'cubic inch' Then @Value * 16.387064
When 'cubic meter' Then @Value * 1000000.0
When 'cubic yard' Then @Value * 764554.857984
When 'fluid ounce - UK' Then @Value * 28.4130625
When 'fluid ounce - US' Then @Value * 29.5735295625
When 'gallon - UK' Then @Value * 4546.09
When 'gallon - US' Then @Value * 3785.411784
When 'liter' Then @Value * 1000.0
When 'pint - UK' Then @Value * 568.26125
When 'pint - US' Then @Value * 473.176473
When 'quart - UK' Then @Value * 1136.5225
When 'quart - US' Then @Value * 946.352946
End;
--
-- Convert from cubic centimeters to the desired unit.
--
Set @Value = Case @UnitTo
When 'cubic centimeter' Then @Value
When 'cubic feet' Then @Value * 3.531466672148859e-5
When 'cubic inch' Then @Value * 0.0610237440947323
When 'cubic meter' Then @Value * 0.000001
When 'cubic yard' Then @Value * 1.307950619314392e-6
When 'fluid ounce - UK' Then @Value * 0.035195079727854
When 'fluid ounce - US' Then @Value * 0.033814022701843
When 'gallon - UK' Then @Value * 2.199692482990878e-4
When 'gallon - US' Then @Value * 2.641720523581484e-4
When 'liter' Then @Value * 0.001
When 'pint - UK' Then @Value * 0.0017597539863927
When 'pint - US' Then @Value * 0.0021133764188652
When 'quart - UK' Then @Value * 8.798769931963512e-4
When 'quart - US' Then @Value * 0.0010566882094326
End;
Return @Value;
End;
Surprisingly it is also faster - 100000 iterations run in 1.8 seconds on my server, compared to 3.9 seconds for the original. It also handles the trivial case of converting a unit of measure to itself.
Best regards
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply