Stillstanddauer eines Betriebsmittels Summarisch

CREATE PROCEDURE pMDZLOGStillstandDauerBetriebsmittelSumme(@ZSFIRM AS VARCHAR(2), @ZSAGRP AS VARCHAR(6) ,@LASTDAYS AS INT,@ZSVDAT AS DATETIME,@ZSBDAT AS DATETIME)

AS

-- Meldungen des SQL-Servers abschalten

--

SET NOCOUNT ON

-- Variablen definieren

--

DECLARE @SQLString    VARCHAR(2048)

DECLARE @TmpTableName VARCHAR( 128)

DECLARE @SQLVonDatum  VARCHAR(  30)

DECLARE @SQLBisDatum  VARCHAR(  30)

-- Prüfen ob der Abfragezeitraum hinter der aktuellen Zeit liegt

--

IF @ZSBDAT > GETDATE() SET @ZSBDAT = GETDATE()

-- Prüfen ob anstelle eines Zeitraums die letztn n Tage angegeben wurden

--

IF @LASTDAYS > 0

BEGIN

   SET @ZSBDAT = DATEADD(SECOND, -1, CONVERT(VARCHAR,GETDATE(),104)) -- Endet Gestern um 23:59

   SET @ZSVDAT = DATEADD(SECOND,  1, DATEADD(DAY, @LASTDAYS * -1, @ZSBDAT)) -- Beginnt n Tage vorher um 00:00

END

-- Zeitraum in Stringvariablen übernehmen

--

SET @SQLVONDatum = CONVERT(VARCHAR,@ZSVDAT,104) + ' ' + CONVERT(VARCHAR,@ZSVDAT,108)

SET @SQLBisDatum = CONVERT(VARCHAR,@ZSBDAT,104) + ' ' + CONVERT(VARCHAR,@ZSBDAT,108)

-- Eindeutigen Tabellennamen vergeben

--

SET @TmpTableName = 'TEMPDB..MDZLOG_' + REPLACE(CONVERT(VARCHAR,GETDATE(),112) + CONVERT(VARCHAR,GETDATE(),114) ,':','')

--

-- Übernahme der unproduktiven Zeitkontodaten in eine temporäre Tabelle

--

SET @SQLString = 'SELECT

 ZSFIRM

,ZSAGRP

,ZSVDAT

,ZSBDAT

,ZSUART

,SBVDAT = ZSVDAT

,SBBDAT = ZSBDAT

,MINUTEN = 0

INTO ' + @TmpTableName + '

FROM GIPSY4MDE.DBO.MDZLOG AS MDZLOG

 INNER JOIN GIPSY4MDE.DBO.PAMDEP AS PAMDEP ON

  TPFIRM = ZSFIRM AND TPAGRP = ZSAGRP

WHERE

 ZSFIRM = ''' + @ZSFIRM + '''

 AND ZSAGRP = ''' + @ZSAGRP + '''

 AND TPSTAT = ''A''

 AND ZSIOCH = TPCCNT

 AND ZSISON = 0

 AND (( ZSBDAT BETWEEN ''' + @SQLVonDatum + ''' AND ''' + @SQLBisDatum + '''  )

  OR  ( ZSVDAT BETWEEN ''' + @SQLVonDatum + ''' AND ''' + @SQLBisDatum + '''  )

  OR  ( ZSVDAT <=      ''' + @SQLVonDatum + ''' AND YEAR(ZSBDAT) <= 1900 )

  OR  ( ZSVDAT <=      ''' + @SQLVonDatum + ''' AND ''' + @SQLBisDatum + ''' < ZSBDAT )

             )

ORDER BY

 ZSAGRP

,ZSVDAT'

-- Temporäre Tabelle mit allen relevanten Datensätzen erzeugen

--

EXEC (@SQLString)

--

-- Wenn der Zeitstrahl vor dem Abfragezeitpunkt liegt

--

SET @SQLString = 'UPDATE ' + @TmpTableName + '

SET

 SBVDAT = ''' + @SQLVonDatum + '''

WHERE

 ZSVDAT < ''' + @SQLVonDatum + ''''

-- SQL ausführen

--

EXEC (@SQLString)

--

-- Wenn der Zeitstrahl hinter dem Abfragezeitpunkt liegt oder unbeendet ist

--

SET @SQLString = 'UPDATE ' + @TmpTableName + '

SET

 SBBDAT = ''' + @SQLBisDatum + '''

WHERE

 YEAR(ZSBDAT) <= 1900 OR ZSBDAT > ''' + @SQLBisDatum + ''''

-- SQL ausführen

--

EXEC (@SQLString)

--

-- Berechne die Zeitspanne in Minuten, dabei ungültige Zeiträume abfangen

--

SET @SQLString = 'UPDATE ' + @TmpTableName + '

SET

 MINUTEN = CASE WHEN SBBDAT < SBVDAT THEN 0 ELSE DATEDIFF(mi,SBVDAT,SBBDAT) END'

-- SQL ausführen

--

EXEC (@SQLString)

--

-- Zeige das Ergebnis

--

SET @SQLString = 'SELECT

 [Unterbrechungsart] = CASE WHEN BGBEZ1 = '''' THEN ''Unbegründet'' ELSE BGBEZ1 END

,[Stunden] = ROUND(SUM(MINUTEN) / 60.0,0)

FROM ' + @TmpTableName + ' AS MDZLOG

INNER JOIN PAUART ON

 BG_KEY = ZSUART

GROUP BY

 BGBEZ1

HAVING

 ROUND(SUM(MINUTEN) / 60.00,0) > 0

ORDER BY

 BGBEZ1'

-- SQL ausführen

--

EXEC (@SQLString)