/*
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
