Mysql: jüngsten Datensatz finden

[Sprint]

Hallo zusammen,

ich habe hier eine Tabelle kunden mit der Spalte kdnr, und eine Tabelle pruefung mit einem Datum. Für eine Statistik bräuchte ich jetzt die Kundennummern und Namen der Firmen, deren letzte Prüfung mindestens 14 Tage her ist. Dabei ist die Nummer der Prüfung zusammengesetzt aus Kundennummer und laufender Nummer. Das könnte z.B. so aussehen:

Tabelle kunden:
kdnr    name
50012    Firma A
56681    Firma B
68812    Firma C

Tabelle Pruefung:
pid            zeit
50012232    2022-11-17 12:10:52
5668112     2022-11-29 17:15:10
50012231    2022-10-13 09:26:11
68812882    2022-11-20 11:11:37
5668111     2022-11-13 14:25:10

In dem Fall müßte als [Ausgabe 50012, Firma A] und [68812, Firma C] kommen, da deren letzte Einträge in der Prüfungstabelle schon älter als 14 Tage sind.

Ich habe zu dem Thema auch schon einiges gefunden, aber entweder ist die Abfrage bis zum Timeout gelaufen oder es kamen alle Datensätze, die älter als zwei Wochen waren, oder einfach nur Blödsinn. Kann mir da jemand helfen?

 

[Zvoni]

Autsch.
Hast du ne Chance in Pruefung das PID auf zwei spalten aufzudröseln (KDNR und LfdNr)?
Weil die einzige Möglichkeit die ich sehe, ist PID auszuparsen nach Länge der KDNR. Sieht nicht schön aus.
getestet in SQLite

SELECT
    K.KDNR,
    K.KDName,
    P.Zeit
FROM tbl_client AS K INNER JOIN tbl_pruefung AS P
ON K.KDNR=SUBSTR(P.PID,1,Length(K.KDNR)) AND Julianday('now','localtime')-julianday(P.Zeit,'localtime')>14

In MySQL gibts aber DateDiff --> MySQL DATEDIFF() Function
Müsste dann so aussehen (ungetestet)

SELECT
    K.KDNR,
    K.KDName,
    P.Zeit
FROM tbl_client AS K INNER JOIN tbl_pruefung AS P
ON K.KDNR=SUBSTR(P.PID,1,Length(K.KDNR)) AND DateDiff(CURRENT_TIMESTAMP(), P.Zeit)>14

Übrigens: Von deinen Beispiel-Daten bekomme ich drei Ergbnisse: 2 mal Firma A und 1 x Firma C
(aber auch weil ich P.Zeit in der Ausgabe habe. Wenn du nur die KDNR/Kunden wissen willst, einfach das Feld weglassen und ein SELECT DISTINCT oder Min bzw. Max anwenden)

SELECT
    K.KDNR,
    K.KDName,
    MAX(P.Zeit) AS MaxZeit,
    MIN(P.Zeit) AS MinZeit,
    COUNT(P.Zeit) AS AnzahlTermine
FROM tbl_client AS K INNER JOIN tbl_pruefung AS P
ON K.KDNR=SUBSTR(P.PID,1,Length(K.KDNR)) AND DateDiff(CURRENT_TIMESTAMP(), P.Zeit)>14
GROUP BY K.KDNR, K.KDName

Nachtrag: KDNR und PID sind bei mir als Text (VarChar) angelegt. Sollten das Integer bei dir sein, musste erst hin und her casten (glaub ich)

Nachtrag2: Also in SQLite hat es auch mit Integern funktioniert. Keine Ahnung für MySQL

 

[Sprint]

Ja, ich hatte mir das auch einfacher vorgestellt.

So eine ähnliche Lösung wie deine zweite hatte ich auch schon. Und auch mit dem selben Problem. Die Zeitangabe brauche ich aber, weil ich ja in der Liste angeben muß, wie lange der letzte Auftrag schon her ist.

Die dritte Variante kommt dem gewünschten Ergebnis schon näher. Allerdings werden da auch Firmen aufgelistet, die Einträge haben, die älter als 14 Tage sind. Ich hab die Daten im ersten Post deshalb nochmal ergänzt. Es wird also auch die 56681 ausgegeben, weil sie einen älteren Datensatz hat. Die darf aber nicht erscheinen, weil ihr letzter Datensatz noch keine zwei Wochen alt ist.

Die Länge der Kundennummer ist immer 5. Ein einfaches substr() reicht da.

 

[Yaslaw]

Jepp, die pid ist sehr schlecht.
In MySQL kann man diese aber noch ein wenig eleganter aufdröseln als das Mr. SqlLite getan hat.

left(pid, 5) as p_kdnr, substr(pid, 6) as p_id

Ergibt

select k.kdnr, k.name, max(p.zeit) as last_run
from pruefung p, kunden k
where p.zeit <= subdate(now(), interval 14 day)
and left(p.pid, 5) = k.kdnr
group by k.kdnr, k.name;

Nachtrag: Hab mein SQL mit der Zeit ergänzt

 

[Sprint]

Daß da einige Teile nicht der Weisheit letzter Schluß sind, gebe ich gerne zu. Das ganze hat sich nur über die letzten 12 Jahre so vergrößert, daß da an einen Umbau nicht zu denken ist.

Der Vorschlag von Yaslaw funktioniert leider garnicht. Die Abfrage läuft da leider ins Timeout.

 

[Zvoni]

Für eine Statistik bräuchte ich jetzt die Kundennummern und Namen der Firmen, deren letzte Prüfung mindestens 14 Tage her ist.

Die dritte Variante kommt dem gewünschten Ergebnis schon näher. Allerdings werden da auch Firmen aufgelistet, die Einträge haben, die älter als 14 Tage sind.

Jetzt versteh ich gar nix mehr....
"mindestens 14 Tage her" übersetze ich im Kopf zu "ist älter als 14 Tage".
Oder meinst du von allen Terminen eines Kunden dessen "höchster" Termin 14 Tage oder älter ist?

Anyway: Ich glaube mich zu erinnern, dass Spaltenaliase auch in Having-Klauseln genutzt werden können

für SQLite

SELECT
    K.KDNR,
    K.KDName,
    MAX(P.Zeit) AS MaxZeit
FROM tbl_client AS K INNER JOIN tbl_pruefung AS P
ON K.KDNR=SUBSTR(P.PID,1,Length(K.KDNR))
GROUP BY K.KDNR, K.KDName
HAVING Julianday('now','localtime')-julianday(MaxZeit,'localtime')>=14

Ergibt für (korrigierte) Daten aus Post 1:

KDNR	KDName	MaxZeit
50012	Firma A	2022-11-17 12:10:52
68812	Firma C	2022-11-20 11:11:37

Müsste für MySQL sein (ungetestet)

SELECT
    K.KDNR,
    K.KDName,
    MAX(P.Zeit) AS MaxZeit
FROM tbl_client AS K INNER JOIN tbl_pruefung AS P
ON K.KDNR=SUBSTR(P.PID,1,Length(K.KDNR))  /*bzw. LEFT(P.PID, 5)*/
GROUP BY K.KDNR, K.KDName
/*HAVING DateDiff(CURRENT_TIMESTAMP(), MaxZeit)>=14*/
HAVING MaxZeit <= subdate(now(), interval 14 day)

EDIT: Ich trottel.... "mindestens 14 Tage" ist inkl. des 14. Tages...
also grösser-gleich 14
und ich hab Yaslaw's Vergleich genommen
DateDiff berücksichtigt nur volle Tage

 

[Sprint]

Jetzt versteh ich gar nix mehr....
"mindestens 14 Tage her" übersetze ich im Kopf zu "ist älter als 14 Tage"

Ja, da hab ich einen Fehler drin. Ich wollte eigentlich jünger schreiben. Wichtig ist aber auch der Teil "letzte Prüfung".

Aber davon unabhängig funktioniert dein letzter Vorschlag scheinbar richtig. Ich habe jetzt nur mal ein paar Stichproben gemacht und die haben gestimmt. Ich werde das morgen nochmal testen, aber ich gehe mal davon aus, daß das auch bei den anderen paßt. Vielen Dank für deine Hilfe!