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