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.
74 lines
5.0 KiB
74 lines
5.0 KiB
USE [Vertragsverwaltung_20160404]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[sp_rpt_fhrlst_checkliste] Script Date: 02.12.2016 09:08:53 ******/
|
|
DROP PROCEDURE [dbo].[sp_rpt_fhrlst_checkliste]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[sp_rpt_fhrlst_checkliste] Script Date: 02.12.2016 09:08:55 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[sp_rpt_fhrlst_checkliste]
|
|
@von DATETIME = NULL ,
|
|
@bis DATETIME = NULL ,
|
|
@oestruktur INTEGER = NULL ,
|
|
@struktur_Ausschluss VARCHAR(255) = NULL ,
|
|
@Honoris_Ausschluss VARCHAR(255) = NULL
|
|
AS
|
|
BEGIN
|
|
IF @von IS NULL
|
|
SET @von = '01.01.2013 00:00:01'
|
|
IF @bis IS NULL
|
|
SET @bis = '30.06.2013 23:59:50'
|
|
IF @oestruktur IS NULL
|
|
SET @oestruktur = 25
|
|
IF @struktur_ausschluss IS NULL
|
|
SET @struktur_ausschluss = ''
|
|
IF @honoris_ausschluss IS NULL
|
|
SET @honoris_ausschluss = ''
|
|
|
|
DECLARE @dd1 VARCHAR(2)
|
|
DECLARE @mm1 VARCHAR(2)
|
|
DECLARE @yy1 VARCHAR(4)
|
|
DECLARE @datum VARCHAR(255)
|
|
SET @dd1 = DAY(@von)
|
|
SET @mm1 = MONTH(@von)
|
|
SET @yy1 = YEAR(@von)
|
|
IF LEN(@dd1) < 2
|
|
SET @dd1 = '0' + @dd1
|
|
IF LEN(@mm1) < 2
|
|
SET @mm1 = '0' + @mm1
|
|
SET @datum = @yy1 + '-' + @mm1 + '-' + @dd1 + ' 00:00:00'
|
|
SET @von = CONVERT(DATETIME, @datum, 20)
|
|
SET @dd1 = DAY(@bis)
|
|
SET @mm1 = MONTH(@bis)
|
|
SET @yy1 = YEAR(@bis)
|
|
IF LEN(@dd1) < 2
|
|
SET @dd1 = '0' + @dd1
|
|
IF LEN(@mm1) < 2
|
|
SET @mm1 = '0' + @mm1
|
|
SET @datum = @yy1 + '-' + @mm1 + '-' + @dd1 + ' 23:59:59'
|
|
SET @bis = CONVERT(DATETIME, @datum, 20)
|
|
|
|
DECLARE @tmpa TABLE (tgnummer varchar(255), name varchar(255), kst varchar(255), summe float, projektnr int, projektname varchar (255))
|
|
|
|
INSERT @tmpa EXEC dbo.sp_rpt_fhrlst_projekte_nach_ma @von,@bis, @oestruktur, @struktur_Ausschluss,@Honoris_Ausschluss
|
|
INSERT @tmpa EXEC dbo.sp_rpt_fhrlst_incidents_nach_ma @von,@bis, @oestruktur, @struktur_Ausschluss,@Honoris_Ausschluss
|
|
|
|
|
|
|
|
SELECT TOP (100) PERCENT TGNummer, NAME, KST, SUM(summe) AS Summe, ProjektNr, Projektname
|
|
FROM @tmpa
|
|
GROUP BY TGNummer, NAME, KST, ProjektNr, Projektname
|
|
ORDER BY tgnummer, ProjektNr
|
|
|
|
END
|
|
|
|
|
|
GO
|