SQL-Abfrage über mehrere Tabellen

[buyden]

hi,

folgende Situation:
Ich hab ne Datenbankanwendung für Reparaturaufträge. Tabelle "auftrag" beinhaltet sämtliche Daten zu einer Reparatur, "statistik" beinhaltet auftragsnummer, statuscode, techniker und datum der letzten Bearbeitung. in der Statistiktabelle wird bei jeder Statusänderung im Auftrag ein Eintrag erstellt oder wenn zu dem jeweiligen Auftrag an aktuellen Tag schon eine Statusänderung vorgenommen wurde, wird dieser Aktualisiert.
Wenn jetzt ein Kunde einen Kostenvoranschlag erhält, muss er innerhalb von 2 Wochen darauf reagieren, tut er das nicht, bekommt er eine Erinnerung für die er 7 Tage Frist hat. Erfolgt keine Reaktion wird das Gerät unrepariert verschickt. Soviel dazu.

Ich möchte jetzt eine Erinnerungsfunktion einbauen, damit der Techniker beim Programmstart sieht, bei welchen Aufträgen die Frist überschritten ist. Es soll also angezeigt werden, Auftragsnummer, Status, Datum der letzten Statusänderung, Anforderung (tabelle Auftrag) und seit wieviel Tagen die Letzte Änderung vergangen ist.

Mein Problem ist zum einen, wie ich die Daten aus den verschiedenen Tabellen zusammenfasse und zum anderen, dass ich keine Idee hab, wie ich die unterschiedlichen Fristen (14 und 7 Tage) zu den unterschiedlichen Statuscodes zusammen in einem DBGrid anzeigen kann.

Welcher Statuscode welche Frist hat hab ich in einer INI-Datei stehen.

Hat jemand ne Idee?

[mkinzler]

Poste mal die Metadaten (Tabellendeklarationen).

[buyden]

wie meinen?! :oops:

[mquadrat]

Er meint den Aufbau der Tabellen (Feldname, Feldtypen, ...) ;)

[buyden]

ach so :oops:

siehe screenshots.

[omata]

Kannst du uns vielleicht noch verraten, über welche Spalten eine Verbindung zwischen den Tabellen hergestellt werden kann. Kann man irgendwie die AuftragID mit der Statisik-Tabelle verbinden. Was ist auftrag in der Statistik-Tabelle?
Und warum wird der Statuscode in einer INI-Datei gehalten? Du hast doch eine Datenbank, dann erstellt doch auch dafür eine Tabelle.

Gruss
Thorsten

[mkinzler]

Hallo buyden.

Könntest du die DDL-Skripte der Tabellen posten, in der grfaik ist nur ersichtlich das es einen Fremdschlüssel gibt aber nicht welches feld es ist und auf welchen Primärschlüssel er verweist.

[buyden]

Sorry, dass das so lang gedauert hat, ich lag ein paar Tage flach.

@omata
ich hab ne Tabelle für die Statuscodes aber bisher die Erinnerungsfristen nicht mit drin, weil das ne neue Funktion wird aber du hast recht, praktischer wären die sicher als neue Spalte in der Staustabelle. Werd ich ändern.

@mkinzler
statistik.auftrag hat nen Fremdschlüssel zu auftrag.nummer, was wenn ich mir das grad überleg ziemlich sinnlos ist weil auftrag.nummer kein Primärschlüssel ist, zwar auch eindeutig aber eben kein key.

Ich glaub ich muss meine DB mal noch ein bisschen umbauen.

Gehen wir also davon aus dass statistik.auftrag der Fremdschlüssel zu auftrag.auftragid ist und somit ne n:1-Beziehnung besteht.
Ansonsten ist die Statistik-Tabelle nicht verknüpft.

[Jelly]

Ein

SQL-Code:

Select s.techniker, max(datum), a.statuscode /* usw */
from statistik s join auftrag a on s.auftrag=a.auftragid
group by s.techniker, a.statuscode /* usw, allo in group rein welche keine max Funktion ist */
where s.techniker = 'Hans Müller' and max(datum) <= date

Damit solltest Du schon mal alle Einträge kriegen, wo der letzte Störfall heute ist.

Willst Du einen Zeitbereich von 14 Tagen berücksichtigen, so kuck Dir mal dazu die Datumsfunktionen von MySQL an, da gibts dann einige.

[buyden]

ich glaub das ist nicht ganz das was ich meine. ich denk das hauptproblem sind die unterschiedlichen Erinnerungsfristen je statuscode wobei nicht jeder Statuscode erinnert werden soll (Erinnerung=0).

nach dem Hinweis von Omata würden die Erinnerungsfristen ja mit in der Statustabelle stehen:

Status (Integer)*
bezeichnung (Varchar)
Erinnerung (Integer)

auftrag.status ist dementsprechend FK auf status.status.

Also wäre es notwendig, innerhalb der Abfrage außerdem noch die letzten Statistikeinträge nach Status zu überprüfen und wenn für den entstprechenden Status ne Erinnerungsfrist > 0 ist dann müssen die zu dem Statistikeintrag gehörenden Daten aus der Auftragstabelle in der Ergebnismenge auftauchen.

Ich denk das wird äußerst komplex falls das überhaupt machbar ist

[mkinzler]

Kann es zu einem Auftrag mehrere Stati geben?

[buyden]

Nein, der Status eines Auftrages wird immer wenn eine neue Aktion vorgenommen wird, geändert.
Allerdings wird in der Statistiktabelle ein Eintrag erstellt, wenn die letzte Statusänderung an einem anderen Tag war. Das heißt also wird ein Auftrag an einem Tag begonnen und abgeschlossen hat er nur einen Statistikeintrag, egal wie oft der Status geändert wurde. Wird er an einem anderen Tag abgeschlossen oder bearbeitet (Statusänderung) wird ein neuer Statistikeintrag erstellt.
Fazit:
In der Statistiktabelle kann es zu einem Auftrag mehrere Einträge mit unterschiedlichen Daten und Statuscodes geben.
Entscheidend für die Erinnerung ist aber immer nur der letzte Eintrag zu einem Auftrag.

[buyden]

ich hab schon ne Erinnerung drin mit einer festen Frist (Codes werden noch aus ner Datei ausgelesen und die Frist aus meiner config.ini) und hab das so gelöst:

Delphi-Quellcode:

with frmdata.quremind do
      begin
        close;
        sql.Clear;
        sql.Add('SELECT * FROM auftrag LEFT OUTER JOIN geraete ON auftrag.geraet = geraete.geraeteid WHERE status IN('+remindstatus.commatext+') AND annahmedatum <= '''+formatdatetime('yyyy-mm-dd',date-config.ReadInteger('Grundeinstellungen','Reminddays',0)) +''' AND techniker= '+config.ReadString('Grundeinstellungen','Technikernummer','')+'; ');
        open;
        if recordcount>0 then
          begin
            if messagedlg('Es wurden '+ inttostr(recordcount)+ ' offene Aufträge gefunden, die älter als '+ inttostr(config.ReadInteger('Grundeinstellungen', 'Reminddays',14))+ ' Tage sind. Möchten Sie die Aufträge jetzt ansehen?', mtconfirmation, [mbyes,mbno],0)=mryes then
              begin
                frmremind.Show;
              end;
          end;
      end;

Aber wie gesagt: verschiedene Statuscodes mit verschiedenen Fristen ist das Ziel.

[omata]

Sorry, aber davon kann ich leider nicht ausgehen...

Zitat:

Zitat von buyden

Gehen wir also davon aus dass statistik.auftrag der Fremdschlüssel zu auftrag.auftragid ist und somit ne n:1-Beziehnung besteht.

weil statistik.auftrag vom Typ VARCHAR ist und auftrag.auftragid ein BIGINT ist.

Das geht so nicht, oder etwa doch? Ist ja MySQL, da weiss man nie.

Edit...
So habe mir nochmal Gedanken gemacht...

Warum ist bei dir die Auftragsnummer nicht in der Auftragstabelle? Die sollte sich doch nicht pro Status ändern können oder?

Folgende Tabellenstruktur würde ich vorschlagen:

SQL-Code:

CREATE TABLE `auftrag` (
  `auftragID` int(10) unsigned NOT NULL auto_increment,
  `auftragnummer` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`auftragID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `status` (
  `StatusID` int(10) unsigned NOT NULL auto_increment,
  `TagFrist` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`StatusID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `auftrag_status` (
  `StatistikID` int(10) unsigned NOT NULL auto_increment,
  `AuftragID` int(10) unsigned NOT NULL default '0',
  `StatusID` int(10) unsigned NOT NULL default '0',
  `Techniker` varchar(10) NOT NULL default '',
  `Datum` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`StatistikID`),
  KEY `FK_auftrag_status_1` (`AuftragID`),
  KEY `FK_auftrag_status_2` (`StatusID`),
  CONSTRAINT `FK_auftrag_status_2` FOREIGN KEY (`StatusID`) REFERENCES `status` (`StatusID`),
  CONSTRAINT `FK_auftrag_status_1` FOREIGN KEY (`AuftragID`) REFERENCES `auftrag` (`auftragID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Wenn du in das Datum als Default einen Timestamp machen lässt, wird automatisch das aktuelle Datum+Zeit in die Spalte Datum geschrieben, wenn ein Datensatz in die Datenbank eingetragen wird.
Dann brauchst du das nicht in deiner Anwendung beachten.

Mit folgender Anweisung kommst du dann an die letzte Auftragsbearbeitung, wenn die Frist, die sich auf den aktuellsten Statuscode bezieht, den aktuellen Tag überschritten hat. So hat man die ganze Geschichte eines Auftrags in der Datenbank, da brauch kein Eintrag geändert zu werden, wenn schon am selben Tag ein Datensatz besteht.

SQL-Code:

SELECT *
FROM auftrag a
INNER JOIN auftrag_status x
  ON a.auftragID = x.auftragID
INNER JOIN status s
  ON x.statusID = s.statusID
WHERE datum = (SELECT MAX(datum)
               FROM auftrag_status
               WHERE auftragID = a.auftragID)
  AND DATE(NOW()) > DATE(DATE_ADD(datum, INTERVAL tagfrist DAY))

Ich habe das mit MySQL5 getestet. Sollte aber auch mit 4.1 gehen.

Gruss
Thorsten

[buyden]

ich hab schon mitbekommen, dass ich meine DB noch ein bisschen umbauen muss.

Also die auftragsnummer gibt es in der Auftragstabelle (auftrag.nummer(varchar)), das ist im Prinzip der Name des Auftrags aber eben nicht der Primärschlüssel, der ist auftragid. In der Statistiktabelle muss ich dann richtigerweise noch das Feld ändern wenn die Tabellen verknüpft werden. Im Moment wird die Statistiktabelle von ner Prozedur befüllt und ich hab da die Auftragsnummer statt der auftragsid genommen um meine alte Abfrage einfacher zu machen.
Dann hab ich festgestellt, dass ich gar keine ForeignKeys anlegen kann weil ich MyISAM als Typ habe. Ich hab schon versucht, das umzuwandeln, funktioniert aber nicht. Gibt's da noch nen Trick oder muss ich die DB komplett neu anlegen und dann die Daten kopieren?

ich hab das ganze mal mit anpassungenan die unveränderte struktur im querybrowser getestet mit dem resultat das mir der ganze rechner eingeschlafen ist weil der mysqlserver mit 100% CPU-Last läuft.

[mkinzler]

Lasse dir die Datenbank am besten in ein SQL-Skript auslagern (inklusive Insert-Statements). Dieses Skript kannst du dann anpassen und dann eine neue DB anlegen.

[buyden]

Das ging schon mal. Die Tabellen sind jetzt alle InnoDB.

Allerdings kann ich keinerlei ForeignKeys anlegen.

beim Anlegen damit

SQL-Code:

ALTER TABLE `repdb`.`auftrag` ADD CONSTRAINT `FK_auftrag_1` FOREIGN KEY `FK_auftrag_1` (`Artikel`)
    REFERENCES `artikel` (`Artikelnummer`)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

kommt immer das:
Can't create table '.\repdb\#sql-130_5f.frm' (errno: 150)

Ich hab schon auf der MySQL-Seite nachgeschaut und die dort angegebenen Lösungsvorschläge mit zwei Test-Tabellen probiert, aber immer mit dem selben Fehler.

[buyden]

kann man da im Server was falsch konfigurieren, dass die FKs nicht angelegt werden können? InnoDB ist logischer Weise aktiviert.