|
Berichtsname |
MDUBA2 |
|
Voraussetzungen |
Maschinendatenerfassung |
|
Tabelle(n) |
MDZLOG (Betriebsmittel-Zeitkonto) |
|
Prozedur |
pMDZLOGStillstandAnzahlBetriebsmittelMonat |
|
Parameter |
Firmennummer Betriebsmittelnummer Letzten n Tage (anstelle von/bis Datum) Von Datum Bis Datum |
|
Ergebnistabelle |
Unterbrechungsart Jahr/Monat Anzahl Unterbrechungen |
|
Aufbereitung |
Tabelle wird gedreht |
|
Crystal-Reports |
-- |
|
Excel-Diagramme |
Säulendiagramm |
CREATE PROCEDURE pMDZLOGStillstandAnzahlBetriebsmittelMonat(@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
,[Monat] = LEFT(CONVERT(VARCHAR(6),ZSVDAT,112),4) + ''/'' + RIGHT(CONVERT(VARCHAR(6),ZSVDAT,112),2)
,[Anzahl] = COUNT(*)
FROM ' + @TmpTableName + ' AS MDZLOG
INNER JOIN PAUART ON
BG_KEY = ZSUART
GROUP BY
BGBEZ1
,LEFT(CONVERT(VARCHAR(6),ZSVDAT,112),4) + ''/'' + RIGHT(CONVERT(VARCHAR(6),ZSVDAT,112),2)
ORDER BY
BGBEZ1
,LEFT(CONVERT(VARCHAR(6),ZSVDAT,112),4) + ''/'' + RIGHT(CONVERT(VARCHAR(6),ZSVDAT,112),2)'
-- SQL ausführen
--
EXEC (@SQLString)