Datum aus voriger Zeile zum berechnen

[concept2015]

Hallo Spezialisten,
jetzt stehe ich vor dem nächsten Problem, zu dem ich keine Lösung finde.
Die Datenbank: Stammdaten
Die Tabelle : Zwischen

! gekürzt !
CREATE TABLE [zwischen](
[SatzNr] INTEGER PRIMARY KEY ASC AUTOINCREMENT,
[QEchtDat] DATE,
[WER] CHAR(30),
[Nummer] INTEGER);

Die Abfrage:

Select QechtDat,wer,Nummer
from Zwischen
where jahr="2019" and nummer = "139" order by QechtDat

bringt folgendes Ergebnis:
QEchtDat WER Nummer
2019-01-10 Demag 139
2019-01-19 Demag 139
2019-01-25 Demag 139
2019-02-01 Demag 139
2019-02-08 Demag 139
2019-02-13 Demag 139
2019-02-27 Demag 139
2019-03-08 Demag 139
2019-03-15 Demag 139

Jetzt muss ich für jede Zeile die vergangenen Tage zur vorigen Zeile berechnen:
Beispiel:
QEchtDat WER Nummer Tage
2019-01-10 Demag 139 0
2019-01-19 Demag 139 9
2019-01-25 Demag 139 6

Zeile 1 ist die erste Zeile und deshalb 0 Tage

Wie bekomme ich es hin,
(dass ich das Datum aus der Zeile zuvor mitnehme in die nächste Zeile)
(oder in der aktuellen Zeile auf das Datum der vorigen Zeile zugreife)
um daraus die Tage zu berechnen.
[SatzNr] ist in der Abfrage NICHT fortlaufend !

Wenn irgendwie möglich Abfrage mit --Erklärung
denn diese Nuss ist mir zu hart.

Danke im voraus für Eure Mühe.

[timog]

In Sqlite kannst Du mit einer Unterabfrage arbeiten. Auf Stackoverflow gibt es da ein Beispiel.

[jobo]

Seit Ende 2018 (Version 3.25) gibt es Windows Functions in SQLite.
Damit nimmt man z.B. im Fall hier für Zugriff auf vorige oder folgende Datensätze lag() und lead() und benötigt keine Unterabfragen dazu. Der Zugriff auf andere Datensätze (vorige, nächste) ist damit sehr vergleichbar zu Tabellenkalkulationsprogrammen.
Da die Unterabfragen wegfallen und eine spezifische Implementierung dieser Funktionen vorgenommen wurde, ist es idR auch viel schneller.

Die Implementierung würde ich schon recht umfangreich nennen.
https://www.sqlite.org/windowfunctions.html



Hier ein Tutorial:
http://www.sqlitetutorial.net/sqlite...ns/sqlite-lag/

[concept2015]

Danke Timo und Jo !
Durch den Hinweis (Link) von Timo konnte ich mir die Lösung zusammenbauen.
Aus:

SELECT
day
,price
,price - (SELECT t2.price
FROM mytable t2
WHERE
t2.item = t1.item AND
t2.day < t1.day
ORDER BY t2.day DESC
LIMIT 1
) AS change
FROM mytable t1

wurde:
SELECT Qechtdat,wer,nummer,
JulianDay(Qechtdat) -
(SELECT
JulianDay(Qechtdat) As Integer
FROM Zwischen t2
WHERE t2.nummer = t1.nummer AND
JulianDay(t2.Qechtdat) < JulianDay(t1.Qechtdat)
ORDER BY
JulianDay(t2.Qechtdat)
DESC LIMIT 1) AS Tage FROM Zwischen t1
where Jahr = "2019"
and nummer = "139"

mit dem Ergebnis:
QEchtDat WER Nummer Tage
2019-01-10 Demag 139 20
2019-01-19 Demag 139 9
2019-01-25 Demag 139 6
2019-02-01 Demag 139 7

Zeile 1 hat aber einen Fehler - hier ist kein Bezug auf ein Datum - also müsste er 0 sein.

Wie ich den ersten Eintrag auf 0 bekomme ist mir aber noch schleierhaft.

Hat jemand eine Idee ? (Lösung)

[Schokohase]

Mal etwas verkürzt

Mit dieser Datenbasis

SQL-Code:

CREATE TABLE [zwischen](
[SatzNr] INTEGER PRIMARY KEY ASC AUTOINCREMENT, 
[QEchtDat] DATE, 
[WER] CHAR(30),
[Nummer] INTEGER);

INSERT INTO [zwischen] (QEchtDat,WER,Nummer) VALUES ('2019-01-10','Demag',139);
INSERT INTO [zwischen] (QEchtDat,WER,Nummer) VALUES ('2019-01-19','Demag',139);
INSERT INTO [zwischen] (QEchtDat,WER,Nummer) VALUES ('2019-01-25','Demag',139);
INSERT INTO [zwischen] (QEchtDat,WER,Nummer) VALUES ('2019-02-01','Demag',139);
INSERT INTO [zwischen] (QEchtDat,WER,Nummer) VALUES ('2019-02-08','Demag',139);

liefert diese Abfrage

SQL-Code:

select QEchtDat,
julianday(QEchtDat) - julianday(lag(QEchtDat,1,QEchtDat) over (order by QEchtDat)) Tage
from zwischen

diese Daten

Code:

        QEchtDat                Tage
----------------  ------------------
      10.01.2019                   0
      19.01.2019                   9
      25.01.2019                   6
      01.02.2019                   7
      08.02.2019                   7

[concept2015]

WOW !!!

Danke Schokohase !!!

Volltreffer - bin sprachlos -

[concept2015]

In Sqlite Expert liefert diese Abfrage die RICHTIGEN Ergebnisse.

Aber in Delphi 10.2 mit:

QText:=QText+'select QEchtDat,wer,nummer, ';
QText:=QText+'julianday(QEchtDat) - ';
QText:=QText+'julianday(lag(QEchtDat,1,QEchtDat)
over (order by QEchtDat)) Tage ';
QText:=QText+'FROM zwischen ';
QText:=QText+'Where Jahr ="2019"';
QText:=QText+'and nummer = "139" ';
QText:=QText+'ORDER BY QEchtDat ';
ZeigDatenQ.Close;
ZeigDatenQ.sql.text:='';
ZeigDatenQ.sql.text:=(QText);
ZeigDatenQ.Open;

bekomme ich einen Fehler:

[FireDac][Phys][SQLite] ERROR: near "over" : syntax error.

den ich nicht finden kann.
(Die Zeile 3 von QText:= ist in einer Zeile geschrieben
wird hier aber getrennt)

[blawen]

Zitat von concept2015:

In Sqlite Expert liefert diese Abfrage die RICHTIGEN Ergebnisse.

Aber in Delphi 10.2 mit:

QText:=QText+'select QEchtDat,wer,nummer, ';
QText:=QText+'julianday(QEchtDat) - ';
QText:=QText+'julianday(lag(QEchtDat,1,QEchtDat)
over (order by QEchtDat)) Tage ';
QText:=QText+'FROM zwischen ';
QText:=QText+'Where Jahr ="2019"';
QText:=QText+'and nummer = "139" ';
QText:=QText+'ORDER BY QEchtDat ';
ZeigDatenQ.Close;
ZeigDatenQ.sql.text:='';
ZeigDatenQ.sql.text:=(QText);
ZeigDatenQ.Open;

bekomme ich einen Fehler:

[FireDac][Phys][SQLite] ERROR: near "over" : syntax error.

den ich nicht finden kann.
(Die Zeile 3 von QText:= ist in einer Zeile geschrieben
wird hier aber getrennt)

Die Meldung kommt nicht von Delphi. Vermutlich fehlt ein oder mehrere Leerzeichen.

Hier z.B. fehlt eines Jahr ="2019"';

[jobo]

Zitat von concept2015:

WOW !!!

Danke Schokohase !!!

Volltreffer - bin sprachlos -

Kein Grund zu staunen, ließ Dir die Links durch, die ich gepostet habe.
Windows Functions sind echt cool und sparen eine Menge Clientgerödel und ergeben sogar lesbare Statements. Dazu ähnlich gibt es "um die Ecke" einen langen Thread zum Thema Performance..
Ich sag einfach mal so, damit geht in SQL alles, was auch mit Excel geht. Und zwar prinzipiell ohne Größenbeschränkung und ohne ständig mit Workarounds arbeiten zu müssen*.

Für eine Delphianwendung bedeutet das, man kann nahe zu beliebige Datentransformationen, -Reports, .. anzeigen, ohne eine Zeile Programmcode zu schreiben. (Nur das SQL Statement und die Komponenten verknüpfen > RAD!)

Zitat von concept2015:

bekomme ich einen Fehler:

[FireDac][Phys][SQLite] ERROR: near "over" : syntax error.

den ich nicht finden kann.

Wie auch geschrieben, diese Funktionen gibt es in SQLite "erst" seit Ende 2018.
Wenn es kein Syntaxfehler ist, der durch das Aneinanderklöppeln von Statementhäppchen entstanden ist, dann ist es vielleicht ein Versionsproblem.

* dafür wäre ein weiteres Feature der DB noch Pivot/Unpivot

[concept2015]

Also, ich war heute mal an einem anderen Rechner:
Win 10 mit Delphi Rio. (Delphi 10.3 Version 26....)
SqlVersion 3.23.1

Die Abfrage direkt im FDQuery eingegeben !
Der gleiche Fehler in Delphi ! auch mit korrigierten Leerzeichen.

In SQLite Expert (Sql Version 3,2.9)
Liefert die Abfrage:

select QEchtDat,wer,nummer,
julianday(QEchtDat) -
julianday(lag(QEchtDat,1,QEchtDat) over (order by QEchtDat)) AS Tage
FROM zwischen
Where Jahr = "2019"
and nummer = "139"
ORDER BY QEchtDat

das:
QEchtDat WER Nummer Tage
2019-01-10 Demag 139 0
2019-01-19 Demag 139 9
2019-01-25 Demag 139 6
2019-02-01 Demag 139 7
2019-02-08 Demag 139 7
2019-02-13 Demag 139 5

läuft tadellos !!

Hat jemand noch eine Idee ?