berechnen einer Zeitdifferenz

[geimist]

Hallo,

in einer Verleihdatenbank möchte ich den Zeitraum zwischen 2 Verleihungen ermitteln. Es ist ja einfach, z.B. den Zeitraum zu ermitteln, der bereits verstrichen ist, da nur die Daten eines einzelnen Datensatzen nötig sind. Das mache ich so:

Code: Alles auswählen

DATEDIFF( 'mm', "Ausgabe", CURDATE( ) ) - CASEWHEN( DAYOFMONTH( "Ausgabe" ) > DAYOFMONTH( CURDATE( ) ), 1, 0 )

Ich möchte noch etwas ausholen, um mein Problem zu erläutern:
Die einzelnen Artikel verwalte ich in einem Formular (eine Tabelle). Da gebe ich ein, wann der Artikel an wen ausgegeben wurde sowie, wann ich ihn wieder zurück erhalten habe. Diese Herangehensweise hat den Nachteil, dass man keinen Changelog (oder History) hat. Aus diesen Grund lese ich per Makro bei jeder Formularänderung alle Daten aus und lasse sie in eine LOG-Tabelle schreiben. Aus dieser LOG-Tabelle möchte ich auslesen, welche Zeit zwischen 2 Ausgaben verstrichen ist. Das Problem liegt für mich darin, dass man die Berechnung immer auf die Identische Produkt-ID anwendet aber auch darin, das die verschiedenen Daten der Ausgabe immer in unterschiedlichen Datensätzen liegen. Hier fehlt es mir einfach an Erfahrung.

Für eure Hilfe bin ich wirklich sehr dankbar.

[Barlee]

Hallo Stephan,

ich gehe davon aus, dass Du auf die HSQLDB zurückgreifst?
Diese bietet zwar beschränkte Möglichkeiten, jedoch kannst Du trotzdem zum Ziel gelangen.

Folgende Idee:
1.) Erstelle einen View, der eine nach ProductID und Datum sortierte Ergebnistabelle bereitstellt. Auf diesen greifst Du in den nachfolgenden Schritten zu
2) Erstelle mittels SELF JOIN eine laufende Nummer, gruppiert über die ProductID
3) Über die laufende Nummer greifst Du anschließend auf den Vorgängerdatensatz zu und ermittelst so die Differenz zwischen dem Ausleihdatum der aktuellen und der Vorgängerzeile. Diese Differenz wird in einer separaten Spalte ausgegeben
4) zum Schluss lässt Du Dir nur die Daten mit dem letzten Ausleihdatum ausgeben.

Genug der Theorie. Jetzt mit Beispiel.
Annahme: Tabelle Logtab mit den Spalten ProductID / Datum

Logtab.png (3.83 KiB) 3777 mal betrachtet

Grundlage für den View "Log_V":

Code: Alles auswählen

SELECT  
"Logtab"."ID",
"Logtab"."ProductID",
"Logtab"."Datum"  FROM "Logtab" 
ORDER BY "ProductID", "Datum"

Nun wird der View abgefragt:
-Erzeugung der laufenden Nummerierung und Ermittlung der Monats- / Tagesdifferenz zum Vorgängerdatensatz

Code: Alles auswählen

SELECT 
(SELECT COUNT(*) FROM Log_V AS X 
WHERE Log_V.Datum > X.Datum AND Log_V.ProductID = X.ProductID)+1 AS LFD,
Log_V.*,
DATEDIFF('mm',(SELECT MAX(Datum) FROM Log_V AS X 
WHERE Log_V.Datum > X.Datum AND Log_V.ProductID = X.ProductID), Datum) AS DIFF_MON,
DATEDIFF('dd',(SELECT MAX(Datum) FROM Log_V AS X 
WHERE Log_V.Datum > X.Datum AND Log_V.ProductID = X.ProductID), Datum) AS DIFF_TAG
FROM Log_V 

lfd_Nr.png (7.29 KiB) 3777 mal betrachtet

..und letzten Endes die Einschränkung auf den aktuellsten Datensatz:

Code: Alles auswählen

SELECT ProductID, Datum, DIFF_MON, DIFF_TAG
FROM
(
SELECT 
(SELECT COUNT(*) FROM Log_V AS X 
WHERE Log_V.Datum > X.Datum AND Log_V.ProductID = X.ProductID)+1 AS LFD,
Log_V.*,
DATEDIFF('mm',(SELECT MAX(Datum) FROM Log_V AS X 
WHERE Log_V.Datum > X.Datum AND Log_V.ProductID = X.ProductID), Datum) AS DIFF_MON,
DATEDIFF('dd',(SELECT MAX(Datum) FROM Log_V AS X 
WHERE Log_V.Datum > X.Datum AND Log_V.ProductID = X.ProductID), Datum) AS DIFF_TAG
FROM Log_V 
) Tab_A,
(
SELECT ProductID, MAX(Datum) AS Datum FROM Log_V
GROUP BY ProductID
) Tab_B
WHERE Tab_A.ProductID = Tab_B.ProductID 
AND Tab_A.Datum = Tab_B.Datum 

Ergebnis.png (4.69 KiB) 3777 mal betrachtet

Viel Erfolg!
Gruß Barlee

[geimist]

Hallo Barlee,

vielen Dank für deine ausführliche Antwort. Ich werde mich morgen damit beschäftigen und mich dann noch mal melden.

[geimist]

Hallo Barlee,

ohne Nachfrage - es hat alles wunderbar funktioniert. Vielen Dank für deine umfassende Hilfe.

[Barlee]

Hallo Stephan,
danke für die Rückmeldung
Freut mich, wenn's so funktioniert, wie Du es Dir vorgestellt hast.

Schönes WE, Gruß Barlee

[geimist]

Ach so, eins wäre da noch:
Jetzt haben wir ja berechnet, was die letzte Zeitdifferenz ist.
Ich würde jetzt noch gern eine weitere Abfrage erstellen, wo ich den Durchschnitt aller Differenzen ausgibt - natürlich nach Artikeln grupiert. Da liegt für mich auch das Problem: Die Grupierung in der Entwurfsansicht gibt ja nur einen Wert für alle Artikel aus, aber ich möchte es ja für jeden Artikel ersehen können.

Könntest du mir da noch einmal helfen, es in den letzten Code einzufügen?

Dank schon einmal.

[Barlee]

Am besten erstellst Du Dir eine weitere Untertabelle, die den Durchschnitt der bereits ermittelten Diffs bereitstellt. Diese joinst Du nur noch an die bestehende Ergebnistabelle. ACHTUNG: Hier kommt jetzt CAST (Typumwandlung) ins Spiel, um die Durchschnittswerte als Dezimalzahl anzeigen zu lassen. Daher muss "SQL direkt ausführen" aktiviert sein. Alle Tabellen, Spaltennamen usw. in Anführungsstriche setzen!

Gruß Barlee

Code: Alles auswählen

SELECT "ProductID", "Datum", "DIFF_MON", "DIFF_TAG", "AVG_DIFF_MON", "AVG_DIFF_TAG" 
FROM 
( 
SELECT ( SELECT COUNT( * ) FROM "Log_V" AS "X" 
WHERE "Log_V"."Datum" > "X"."Datum" AND "Log_V"."ProductID" = "X"."ProductID" ) + 1 AS "LFD",
"Log_V".*,
DATEDIFF( 'mm', ( SELECT MAX( "Datum" ) FROM "Log_V" AS "X" 
WHERE "Log_V"."Datum" > "X"."Datum" 
AND "Log_V"."ProductID" = "X"."ProductID" ), "Datum" ) AS "DIFF_MON", 
DATEDIFF( 'dd', ( SELECT MAX( "Datum" ) FROM "Log_V" AS "X" 
WHERE "Log_V"."Datum" > "X"."Datum" 
AND "Log_V"."ProductID" = "X"."ProductID" ), "Datum" ) AS "DIFF_TAG" FROM "Log_V" 
) "Tab_A",
( 
SELECT "ProductID", MAX( "Datum" ) AS "Datum" 
FROM "Log_V" 
GROUP BY "ProductID" 
) "Tab_B",
(
SELECT 
"ProductID", 
AVG("DIFF_MON") AS AVG_DIFF_MON, 
AVG("DIFF_TAG") AS AVG_DIFF_TAG
FROM 
(SELECT
"Log_V"."ProductID", 
CAST(DATEDIFF( 'mm', ( SELECT MAX( "Datum" ) FROM "Log_V" AS "X" 
WHERE "Log_V"."Datum" > "X"."Datum" AND "Log_V"."ProductID" = "X"."ProductID" ), "Datum" ) AS DOUBLE) AS "DIFF_MON", 
CAST(DATEDIFF( 'dd', ( SELECT MAX( "Datum" ) FROM "Log_V" AS "X" 
WHERE "Log_V"."Datum" > "X"."Datum" AND "Log_V"."ProductID" = "X"."ProductID" ), "Datum" ) AS DOUBLE) AS "DIFF_TAG" FROM "Log_V" ) "Tab_C"
GROUP BY "ProductID"
) "Tab_C"

WHERE "Tab_A"."ProductID" = "Tab_B"."ProductID" 
AND "Tab_A"."ProductID" = "Tab_C"."ProductID"
AND "Tab_A"."Datum" = "Tab_B"."Datum"

[geimist]

Vielen Dank Barlee,

jetzt bin ich wunschlos glücklich.

Lieben Gruß und noch ein schönes WE.