SQL_FUNC_PERCENTIL

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