July 5, 2011 at 3:00 am
Hi,
I have got an Unit of Measure table in my database currently. I need function to calculate the multiplier value between two different units of meassure. eg. Multiplier value in brackets.
Ton(1)
-Kilogram(1000)
--Gram(1000)
---Milligram(1000)
The function needs a required Unit of Measure parameter as well as the current unit of measure.
So when I pass the following to the function( required Unit of Measure = Ton; current Unit of measure = Milligram)
The function should return 1000*1000*1000.
Here is the table
CREATE TABLE [Setup].[UOM](
[ID] [int] IDENTITY(1,1) NOT NULL,
[description] [varchar](50) NULL,
[multiplier] [decimal](19, 2) NULL,
[parentID] [int] NULL
)
July 5, 2011 at 4:06 am
if anyone needs the solution here is what I came up with.......:w00t:
/*
Select dbo.GetUOMMultiplier(
29,--@requiredUOMID INT,
26
)
Select *
FROM Setup.UOM
*/
--=========================================================
ALTER FUNCTION GetUOMMultiplier(
@requiredUOMID INT,
@currentUOMID INt
)
RETURNS DECIMAL(19,2)
AS
BEGIN
IF(@requiredUOMID!=@currentUOMID)
BEGIN
DECLARE @parentID INT
SET @parentID =0
DECLARE @currMultiplier DECIMAL(16,2)
SET @currMultiplier =1;
SELECT @currMultiplier = multiplier,
@parentID = ParentID
FROM Setup.UOM
WHERE ID =@currentUOMID
--IF(@parentID > 0 AND @parentID != @requiredUOMID)
IF(@parentID > 0 AND @parentID != @requiredUOMID)
BEGIN
Select @currMultiplier = @currMultiplier* dbo.GetUOMMultiplier(
@requiredUOMID,
@parentID
)
END
END
IF(@currMultiplier=0)
BEGIN
Set @currMultiplier=1
Select @currMultiplier = @currMultiplier* dbo.GetUOMMultiplier(
@parentID,
@requiredUOMID
)
--SET @currMultiplier=9999
END
RETURN ISNULL(@currMultiplier,1)
END
July 10, 2011 at 2:16 pm
In T-SQL, making use of an in-line table-valued function:
IFOBJECT_ID(N'dbo.MetricConversion', N'IF')
IS NULL
EXECUTE ('CREATE FUNCTION dbo.MetricConversion() RETURNS TABLE AS RETURN SELECT 1 AS x');
ALTER FUNCTION dbo.MetricConversion
(
@valueDOUBLE PRECISION,
@from_unitCHARACTER VARYING(2),
@to_unitCHARACTER VARYING(2)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
WITH ConversionTable AS
(
SELECT
name,
abbreviation,
factor
FROM
(
VALUES
(
CONVERT(CHARACTER VARYING(5), '' COLLATE Latin1_General_CS_AS),
CONVERT(CHARACTER VARYING(2), '' COLLATE Latin1_General_CS_AS),
CONVERT(DOUBLE PRECISION, 1E0))
),
/*see http://en.wikipedia.org/wiki/SI_prefix*/
('yotta', 'Y' , CONVERT(DOUBLE PRECISION, 1E24)),
('zetta', 'Z' , CONVERT(DOUBLE PRECISION, 1E21)),
('exa' , 'E' , CONVERT(DOUBLE PRECISION, 1E18)),
('peta' , 'P' , CONVERT(DOUBLE PRECISION, 1E15)),
('tera' , 'T' , CONVERT(DOUBLE PRECISION, 1E12)),
('giga' , 'G' , CONVERT(DOUBLE PRECISION, 1E09)),
('mega' , 'M' , CONVERT(DOUBLE PRECISION, 1E06)),
('kilo' , 'K' , CONVERT(DOUBLE PRECISION, 1E03)),
('hecto', 'h' , CONVERT(DOUBLE PRECISION, 1E02)),
('deca' , 'da', CONVERT(DOUBLE PRECISION, 1E01)),
('deci' , 'd' , CONVERT(DOUBLE PRECISION, 1E-01)),
('centi', 'c' , CONVERT(DOUBLE PRECISION, 1E-02)),
('milli', 'm' , CONVERT(DOUBLE PRECISION, 1E-03)),
('micro', 'µ' , CONVERT(DOUBLE PRECISION, 1E-06)),
('nano' , 'n' , CONVERT(DOUBLE PRECISION, 1E-09)),
('pico' , 'p' , CONVERT(DOUBLE PRECISION, 1E-12)),
('femto', 'f' , CONVERT(DOUBLE PRECISION, 1E-15)),
('atto' , 'a' , CONVERT(DOUBLE PRECISION, 1E-18)),
('zepto', 'z' , CONVERT(DOUBLE PRECISION, 1E-21)),
('yocto', 'y' , CONVERT(DOUBLE PRECISION, 1E-24))
)
AS V (name, abbreviation, factor)
)
SELECT
@value * F.factor / T.factor AS result
FROM ConversionTable AS F
CROSS JOIN ConversionTable AS T
WHERE
F.abbreviation = @from_unit
AND T.abbreviation = @to_unit;
SELECT
mc.result
FROM dbo.MetricConversion(234.56, '', 'K') AS mc;
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply