You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
84 lines
1.7 KiB
84 lines
1.7 KiB
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
|
|
|
|
|