-- *****************************************************************************************************
-- ************************************** FUNC_PERCENTIL ***********************************************
-- *****************************************************************************************************

USE [METRICAS]
GO

-- ****** Object:  UserDefinedFunction [dbo].[FUNC_PERCENTIL]    
-- ****** Script Date: 07/12/2015 09:44:13 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[FUNC_PERCENTIL](@Factor_Percentil float) RETURNS FLOAT

AS BEGIN

DECLARE @Factor FLOAT,@Resultado FLOAT
SET @Factor = @Factor_Percentil

SELECT @Resultado = dbo.FUNC_LERP(MAX(d), 0.0, 1.0, MAX(a.TIEMPO), MAX(b.TIEMPO))
FROM
	(
		SELECT FLOOR(kf) AS k, kf-FLOOR(kf) AS d
		FROM(SELECT 1+@Factor*(COUNT(*)-1) AS kf FROM TEMP_TIEMPOS) AS x1
	) AS x2
JOIN TEMP_TIEMPOS a
ON 
(
SELECT COUNT(*) FROM TEMP_TIEMPOS aa WHERE aa.TIEMPO < a.TIEMPO) < k
JOIN TEMP_TIEMPOS b
ON 
(SELECT COUNT(*) FROM TEMP_TIEMPOS bb WHERE bb.TIEMPO < b.TIEMPO) < k+1


RETURN @Resultado
END

GO

-- *****************************************************************************************************
-- ************************************** FUNC_LERP ****************************************************
-- *****************************************************************************************************

USE [METRICAS]
GO

-- ****** Object:  UserDefinedFunction [dbo].[FUNC_LERP]    
-- ****** Script Date: 07/12/2015 09:45:53

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[FUNC_LERP] 
	(@value float,@low float,@high float,@newlow float,@newhigh float)
RETURNS float -- between newlow and newhigh
AS
BEGIN
  RETURN CASE 
      WHEN @value between @low and @high and @newlow <= @newhigh THEN @newlow+dbo.FDIV((@value-@low),@high-@low)*(@newhigh-@newlow)
      WHEN @value = @low and @newlow is not NULL THEN @newlow
      WHEN @value = @high and @newhigh is not NULL THEN @newhigh
      ELSE NULL
	END
END

GO