USE [Vertragsverwaltung]
GO

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

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Create Function [dbo].[fnkt_get_KPI_hierarchie]
(	
	@startkey int
)
RETURNS  @tmp1 TABLE 
(
  ID INT 
)     
AS
begin

    WITH tmp1 (id)
    AS
    (
        SELECT oenr
        FROM oe
        WHERE oenr=@startkey
        UNION ALL
        SELECT C.oenr FROM oe C
        INNER JOIN tmp1 R ON C.parentid = R.id
    )
    INSERT INTO @tmp1
    SELECT id
    FROM tmp1

    RETURN


end	
GO

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

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[get_monatstabelle](@from AS DATETIME, @to AS DATETIME)
   RETURNS @Dates TABLE(start_date DATETIME, End_Date datetime)
 AS
 BEGIN
   DECLARE @st DATETIME
   DECLARE @ed DATETIME 

   WHILE @from < @to BEGIN
      SELECT @st=DATEADD(MONTH, DATEDIFF(MONTH, 0, @from), 0)
	  SELECT @ed=DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @from) + 1, 0)) 
	  SET @ed=DATEADD(HOUR,23,@ed)
	  SET @ed=DATEADD(MINUTE,59,@ed)
	  SET @ed=DATEADD(SECOND,58,@ed)

	  INSERT @dates (start_date, end_date) VALUES(@st, @ed)
	  SET @from = DATEADD(MONTH, 1,@from)
   end
  RETURN
 END


GO