USE [Vertragsverwaltung]
GO

/****** Object:  UserDefinedFunction [dbo].[fnkt_get_last_status]    Script Date: 23.04.2013 08:09:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnkt_get_last_status]
(
	@ticketid varchar(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
   DECLARE @res VARCHAR(255)
   SELECT TOP 1 @res=statusid FROM TXP_Ticket_Data WHERE ticketid=@ticketid ORDER BY Status_Changedate desc
	RETURN @res

END

GO

/****** Object:  UserDefinedFunction [dbo].[get_anteil]    Script Date: 23.04.2013 08:09:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[get_anteil]
(
   @engagement int,
   @matyp int
)
RETURNS float
AS
BEGIN
    DECLARE @fhranteil INT
    SELECT @fhranteil=Fuehrungsanteil FROM dbo.MitarbeiterTyp WHERE mitarbeitertypnr=@matyp

	DECLARE @res FLOAT
    SET @res=(@engagement * (100-@fhranteil))/100
	RETURN @res

END


GO

/****** Object:  UserDefinedFunction [dbo].[get_arbeitstage]    Script Date: 23.04.2013 08:09:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
Create FUNCTION [dbo].[get_arbeitstage]
(
   @jahr int,
   @monat int
)
RETURNS float
AS
BEGIN
    DECLARE @res int
	SELECT @res = arbeitstage FROM jahrestabelle WHERE jahr=@jahr AND monat=@monat
	RETURN @res

END


GO