/*
	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;">&#65279;</span>

• Queries
• SQL Server