QW2021/20210526_Scripts_Update/funktionen.sql

USE [qw2021]
GO

/****** Object:  UserDefinedFunction [dbo].[calc_leistung]    Script Date: 26.05.2021 06:43:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[calc_leistung] (
	@type VARCHAR(255)
	,@wert VARCHAR(MAX)
	)
RETURNS INT
AS
BEGIN
	DECLARE @min INT;
	DECLARE @sek INT;
	DECLARE @hs INT;
	DECLARE @m INT
	DECLARE @cm INT
	DECLARE @timestring VARCHAR(MAX);
	DECLARE @pos INT

	SET @timestring = @wert

	IF @type = 'MM:SS:HS'
	BEGIN
		IF LEN(@timestring) = 8
			SET @timestring = '00:' + @timestring;

		IF LEN(@timestring) = 5
			SET @timestring = '00:00:' + @timestring;
		SET @min = DATEPART(MINUTE, @timestring);
		SET @sek = DATEPART(SECOND, @timestring);
		SET @hs = DATEPART(millisecond, @timestring);
		SET @min = @min * 1000 * 60;
		SET @sek = @sek * 1000;
		SET @hs = @hs * 10;

		RETURN @min + @sek + @hs;
	END;

	IF @type = 'SS:HS'
	BEGIN
		SET @timestring = @wert
		SET @timestring = replace(@timestring, '.', ':');

		IF len(@timestring) = 4
			SET @timestring = '00:00:0' + @timestring

		IF len(@timestring) = 5
			SET @timestring = '00:00:' + @timestring
		SET @min = DATEPART(MINUTE, @timestring);
		SET @sek = DATEPART(SECOND, @timestring);
		SET @hs = DATEPART(millisecond, @timestring);
		SET @min = @min * 1000 * 60;
		SET @sek = @sek * 1000;
		SET @hs = @hs * 10;

		RETURN @min + @sek + @hs;
	END

	IF @type = 'MM.CM'
	BEGIN
		SET @timestring = @wert

		IF len(@timestring) = 4
			SET @timestring = '0' + @timestring
		SET @pos = charindex('.', @timestring)
		SET @m = SUBSTRING(@timestring, 1, @pos - 1)
		SET @cm = SUBSTRING(@timestring, @pos+1, 2)
		SET @m = @m * 100

		RETURN @m + @cm


	END;
return 0
end
GO

/****** Object:  UserDefinedFunction [dbo].[leistungen_rechnen]    Script Date: 26.05.2021 06:43:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[leistungen_rechnen] (
	@disziplin VARCHAR(255)
	,@Resultat VARCHAR(255)
	)
RETURNS INT
AS
BEGIN
	DECLARE @res INT;
	DECLARE @hh VARCHAR(2);
	DECLARE @mm VARCHAR(2);
	DECLARE @ss VARCHAR(2);
	DECLARE @hs VARCHAR(2);
	DECLARE @p INT;
	DECLARE @me VARCHAR(2);
	DECLARE @cm VARCHAR(2);
	DECLARE @resstring VARCHAR(255);
	declare @2p int
	IF @disziplin IN (
			'10H'
			,'60M'
			,'600'
			,'60H'
			,'80M'
			,'80H'
			)
	BEGIN
		SET @resstring = @Resultat;
		SET @p = CHARINDEX('.', @resstring);
		if @p=0 set @resstring=@resstring+'.00'
		SET @p = CHARINDEX('.', @resstring);
		SET @2p = CHARINDEX(':', @resstring);

		IF @p > 0 and @2p=0 
		BEGIN
			SET @ss = SUBSTRING(@resstring, 1, @p - 1);
			SET @hs = SUBSTRING(@resstring, @p + 1, LEN(@resstring) - @p);

			IF LEN(@ss) = 1
				SET @ss = '0' + @ss;

			IF LEN(@hs) = 1
				SET @hs = @hs + '0';
			SET @res = (@ss * 1000) + @hs * 10;
		END
		ELSE
		BEGIN
			SET @p = CHARINDEX(':', @resstring)

			IF @p > 0
			BEGIN
				IF len(@resstring) = 7
					SET @resstring = '0' + @resstring
				SET @mm = SUBSTRING(@resstring, 1, 2)
				SET @ss = SUBSTRING(@resstring, 4, 2)
				SET @hs = SUBSTRING(@resstring, 7, 2)
				SET @res = (@mm * 60 * 100) + (@ss * 100) + (@hs )
				set @res=@res*10
			END
		END;
	END
	ELSE
	BEGIN
		SET @resstring = @Resultat
		SET @p = CHARINDEX('.', @resstring)
		if @p=0 set @resstring=@resstring+'.00'
		SET @p = CHARINDEX('.', @resstring)

		SET @ME = SUBSTRING(@resstring, 1, @p - 1);
		SET @CM = SUBSTRING(@resstring, @p + 1, LEN(@resstring) - @p);

		IF LEN(@me) = 1
			SET @me = '0' + @me

		IF LEN(@cm) = 1
			SET @cm = @cm + '0'
		SET @res = ((@me * 100) + @cm)*10
	END

	RETURN @res;
END;
GO

/****** Object:  UserDefinedFunction [dbo].[Punkte_Rechnen]    Script Date: 26.05.2021 06:43:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[Punkte_Rechnen]
(
   @geschlecht VARCHAR(255),
   @disziplin VARCHAR(255),
   @Wert VARCHAR(255)
   
)
RETURNS float
BEGIN

    DECLARE @awert DECIMAL(20,10)
	DECLARE @bwert DECIMAL(20,2)
	DECLARE @cwert DECIMAL(20,3)
	DECLARE @tempwert DECIMAL(38,15)
	DECLARE @result DECIMAL(38,15)
	DECLARE @werte_disziplin VARCHAR(20)

	SET @werte_disziplin=@disziplin
	IF @disziplin='BAL' SET @Werte_Disziplin='BALL'
	IF @disziplin='WEZ' SET @werte_disziplin='WEIT'
	IF @disziplin='600' SET @werte_disziplin='600'
	SELECT @awert=awert, @bwert=bwert,@cwert=cwert FROM dbo.WerteTabelle WHERE gender=@geschlecht AND disziplin=@werte_disziplin


	IF @werte_disziplin IN ('Weit','Drei','Stab','Kugel','Speer','Diskus','Hammer','Ball','Hoch') BEGIN
	set @tempwert=-1
       SET @tempwert = TRY_CAST(REPLACE(@WERT, ',', '') AS decimal(10, 8)) 
	   if @tempwert is null  set @tempwert=@wert
   	   SET @result=power(((@tempwert-@bwert)/100),@cwert)
	   SET @result=@awert*@result
	END
	IF @Werte_Disziplin IN ('50','60','80','100','200','300','400','600','800','1000','1500','2000','3000','5000','1000','50H','60h','80h','100h','110h','300h','400h','1500 St','2000 St','3000 St','4x100','4x400') begin
		DECLARE @t TIME
		IF LEN(@wert)=5 BEGIN
		   SET @wert='00:00:'+@Wert
		end
		if len(@wert)=4 begin
		   set @wert='00:00:0'+@wert;
		end
		wHILE LEN(@wert)<8 begin
		   SET @wert='00:'+@wert
		end
		set @wert=replace(@wert,',','.')
		SET @t=CONVERT( TIME, @wert);
		DECLARE @hs INTEGER	
		SET @hs=0

		SET @hs=@hs+DATEPART(MINUTE,@t)*60*100
		SET @hs=@hs+DATEPART(Second,@t)*100
		SET @hs=@hs+DATEPART(MILLISECOND,@t)/10
		SET @tempwert=@hs
		SET @result=power(((@bwert - @tempwert)/100),@cwert)
		SET @result=@awert*@result
	end
	--SET @result= (@tempwert-@bwert)/100
	--SET @result=POWER(@tempwert,@cwert)

	RETURN @result

END
GO