Query Reporter-Report

[Dunkel]

Hallo zusammen!

Ich habe ein, für mich, ziemlich kniffliges Anliegen. Was die ganze Sache überaus verkompliziert ist, dass ich nicht auf Delphi, C#, Java oder sonstiges zurückgreifen kann, ich alles mit dem Tool "Query Reporter" via SQL machen muss, da keine Fremdsoftware (leider fällt auch eigene, selbt programmierte Software unter diese Kategorie) benutzt werden darf. Erschwerender Weise bin ich auch nicht soo der SQL-Crack; hier und da mal eine kleine Abfrage, gerne auch über mehrere verschachtelte Tabellen, aber das war's auch schon. :coder:


Kurze Beschreibung des Problems:
Eine vollautomatisierte Anlage kommuniziert via "Telegrammen" mit einem Zentralrechner. Dieser Zentralrechner speichert die komplette Kommunikation zwecks späterer Nachverfolgung durch Systemadministratoren in einer Datenbanktabelle.
Da so eine vollautomatische und recht komplexe Anlage nur in den seltensten Fällen ohne Zwischenfälle rund um die Uhr durchläuft, muss öfters am Tag händisch in die Anlage eingegriffen werden. Und genau um die Auswertung dieser händischen Eingriffe geht es, wann eine Störung im automatischen Ablauf stattgefunden hat und wie lange das Beheben gedauert hat.


Kurze Skizzierung der Tabellenstruktur mit exemplarischem Inhalt:

Code:

Sender  |  Zeitstempel  |  Sequence   |        Telegramm
----------------------------------------------------------------
  01    |  Datum+Zeit   | fortlaufend | (100)100199RBRBG100S.
  02    |  Datum+Zeit   | fortlaufend | (100)200299RBAKL100S.
  02    |  Datum+Zeit   | fortlaufend | (100)300299RBAKL1AU..
  01    |  Datum+Zeit   | fortlaufend | (100)400199RBRBG1AU..
   .    |       .       |      .      |  .
   .    |       .       |      .      |  .
   .    |       .       |      .      |  .
________________

In der Spalte Sender ist die sendende Steuerung eingetragen, von derer 4 (01 - 04) existieren
In der Spalte Zeitstempel ist der Zeitstempel des Telegramm-Empfangs enthalten (01.01.2001 12:12:12)
In der Spalte Sequence ist eine fortlaufende Nummer hinterlegt, mit jedem Telegramm wird diese automatisch um eins inkrementiert
In der Spalte Telegramm sind die Nutzdaten der einzelnen Telegramme eingetragen, diese gliedern sich wie folgt:

Code:

(100) aa bb cc RB dddd eeee

(100) - keine Ahnung, steht vor jedem Telegramm
aa - eine fortlaufende Nummerierung
bb - die eindeutige Nummer der sendenden Steuerung
cc - die eindeutige Nummer der empfangenden Steuerung
RB - ist die Telegrammart, RB -> Betriebsartenwechsel, z.B. von Handeingriff hin zu Automatik
dddd - die eindeutige Nummer/Bezeichnung eines Teilbereichs der kompletten Anlage
eeee - entweder 'AU..' für Automatik oder '00S.' für Störung


Kurze Skizzierung des gewünschten Ergebnises:
Im Grunde sollte am Ende eine Tabelle herauskommen in der in der ersten Spalte die Nummer/Bezeichnung des Teilbereichs steht, in der zweiten Spalte die Startzeit der Störung und in der dritten Spalte die gesammte Dauer der Störung, in etwa so:

Code:

Bez.|         Von          |  Dauer
--------------------------------------
RBG1 | 28.11.2008 14:42:23 | 00:23:42
AKL1 | 28.11.2008 16:23:42 | 00:42:23
  .  |          .          |     .
  .  |          .          |     .
  .  |          .          |     .

Also wirklich nichts wildes. :wink:


Tja, soviel zur Beschreibung meines Problems.


Mit einer "richtigen" Programmiersprache wäre das ja im Großen und Ganzen ja auch kein großes Problem:

Einfach die Tabelle vom Anfang bis zum Ende durchlaufen.

Code:

1.)Wenn ein RB-Telegramm gefunden dann:
|-> 1.) wenn "00S" (Störung) im Telegramm enthalten
|   |-> 1.1) in einer temp. Liste die Bezeichnung des Teilbereichs und die Zeit eintragen
|-> 2.) wenn "AU.." (Automatik) im Telegramm enthalten
    |-> 2.1) in der temp. Liste gucken, ob der Teilbereich enthalten ist
        |-> 2.2) wenn enthalten, Zeitdiffrenz errechnen, Teilbereich, Startzeit und Dauer in die Zielliste eintragen, Eintrag aus der temp. Liste löschen

Das Schema bis zum Ende der Tabelle durchlaufen, Problem gelöst. 8)


Aber wie, zum Belzebub, mache ich das mit SQL?! Bietet mir SQL entsprechende Möglichkeiten und Werkzeuge um sowas zu realisieren? Gibt es womöglich eine SQL-freundlichere Angehensweise für mein Problem? Ist das überhaupt so ohne weiteres möglich?

Über Hinweise und Anregungen (gerne auch einen kompletten SQL-Code :zwinker: ) bin ich Euch jetzt schon dankbar!

Grüße
Michael

[omata]

SQL-Code:

SELECT SUBSTR(telegramm, 14, 4) AS bez, 
       zeitstempel, 
       zeitstempel - (SELECT MIN(zeitstempel)
                      FROM tabelle
                      WHERE SUBSTR(telegramm, 12, 2) = 'RB'
                        AND SUBSTR(telegramm, 18, 2) = 'AU'
                        AND SUBSTR(telegramm,  8, 4) = SUBSTR(t.telegramm, 8, 4)
                        AND zeitstempel > t.zeitstempel) AS dauer
FROM tabelle t
WHERE SUBSTR(telegramm, 12, 2) = 'RB'
  AND SUBSTR(telegramm, 18, 3) = '00S'

oder so...

SQL-Code:

SELECT SUBSTR(a.telegramm, 14, 4) AS bez, 
       a.zeitstempel, 
       a.zeitstempel - MIN(b.zeitstempel) AS dauer
FROM tabelle a
LEFT JOIN tabelle b
  ON     SUBSTR(b.telegramm, 12, 2) = 'RB'
     AND SUBSTR(b.telegramm, 18, 2) = 'AU'
     AND SUBSTR(a.telegramm,  8, 4) = SUBSTR(b.telegramm, 8, 4)
     AND a.zeitstempel < b.zeitstempel
WHERE SUBSTR(a.telegramm, 12, 2) = 'RB'
  AND SUBSTR(a.telegramm, 18, 3) = '00S'
GROUP BY SUBSTR(a.telegramm, 14, 4), a.zeitstempel

[Dunkel]

omata, Du scheinst ein Gott des heiligen SQLs zu sein... :zwinker:

Ich werde Deinen SQL-Code ausprobieren und ihn als Inspirationsquelle verwenden. Vielen, vielen Dank!