/* AUTHOR : Brahmanand Shukla DATE : 23-Jul-2018 DESCRIPTION : Generic function to check if a particular value is within the tolerance limit of the Benchmark value EXAMPLE : -- Sample with Tolerance Basis as 'PERCENTAGE' SELECT [dbo].[ufn_IsWithinToleranceRange] ( 100 -- @BenchmarkValue , 120 -- @ValueToCompare , 'PERCENTAGE' -- @ToleranceBasis , 20 -- @Tolerance ) -- Sample with Tolerance Basis as 'VALUE' SELECT [dbo].[ufn_IsWithinToleranceRange] ( 100 -- @BenchmarkValue , 120 -- @ValueToCompare , 'PERCENTAGE' -- @ToleranceBasis , 10 -- @Tolerance ) */ CREATE FUNCTION [dbo].[ufn_IsWithinToleranceRange] ( @BenchmarkValue NUMERIC(24, 6) , @ValueToCompare NUMERIC(24, 6) , @ToleranceBasis VARCHAR(10) , @Tolerance NUMERIC(18, 2) ) RETURNS TINYINT WITH SCHEMABINDING /* Parameter Description @BenchmarkValue -- This is Benchmark value on which tolerance is to be applied , @ValueToCompare -- This is value which is to be compared with the Benchmark , @ToleranceBasis -- This is Tolerance Basis which means whether the Tolerance will be Percentage Based ('PERCENTAGE') or Value Based ('VALUE') , @Tolerance -- This is Tolerance. It will be Tolerance Percentage if @ToleranceBasis = 'PERCENTAGE' and it will be Tolerance Value if @ToleranceBasis = 'VALUE' */ /* Output 0 -- If supplied value is outside the Tolerance Range of the Benchmark Value 1 -- If supplied value is within the Tolerance Range of the Benchmark value 2 -- Invalid input */ AS BEGIN DECLARE @IsWithinToleranceRange TINYINT DECLARE @ToleranceLowerLimit NUMERIC(24, 6) DECLARE @ToleranceUpperLimit NUMERIC(24, 6) DECLARE @ToleranceValue NUMERIC(24, 6) IF (@ToleranceBasis = 'PERCENTAGE') BEGIN SET @ToleranceValue = (@BenchmarkValue * NULLIF(@Tolerance, 0) / 100); END ELSE IF (@ToleranceBasis = 'VALUE') BEGIN SET @ToleranceValue = @Tolerance; END IF @ToleranceValue IS NOT NULL BEGIN SET @ToleranceLowerLimit = (@BenchmarkValue - @ToleranceValue); SET @ToleranceUpperLimit = (@BenchmarkValue + @ToleranceValue); END ELSE BEGIN SET @IsWithinToleranceRange = 2; RETURN @IsWithinToleranceRange; END IF (@ValueToCompare BETWEEN @ToleranceLowerLimit AND @ToleranceUpperLimit) BEGIN SET @IsWithinToleranceRange = 1; END ELSE BEGIN SET @IsWithinToleranceRange = 0; END RETURN @IsWithinToleranceRange; END<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
Advertisements