Zwei Tabellen zusammenführen - nicht UNION

Wolf-Tilmann · 25 März 2014

Hallo zusammen,

ich hätte wieder einmal ein Problem bei dem ich Eure Hife benötige:

Ich habe zwei Tabellen. Eine mit Plan- und eine mit Ist-daten.

In der Tabelle plandaten sind logischwerweise die Planungen; in der Tabelle istdaten die tatsächlich entstandenen Daten. Da wir das etwas großzügig handhaben müssen stimmen die Zeiten nicht immer überein. Es gibt Vorauszahlungen, dann ist der Zeitpunkt in plandaten.beginn vor dem Zeitpunkt istdaten.periode; es gibt Nachzahlungen (plandaten.beginn nach istdaten.periode); meist erfolgen die Zahlungen aber pünktlich (plandaten.beginn = istdaten.periode).

Meine Aufgabe ist es jetzt die Plan- und Ist-daten je Person in einem View einander gegenüberzustellen.
Bei Vorauszahlungen beginnt die Auflisttung mit istdaten.periode; bei Regel- und Nachzahlungen mit dem Wert aus plandaten.beginn.

Aus den gegebenen Umständen heraus können die Tabellen lediglich verknüpft werden über
plandaten.namegp = istdaten.namegp.

Ich habe ein ganz einfaches Beispiel hier als SQL beigefügt.
(Die Tabellen sind so natürlich nicht vollständig)

Die Tabelle vergleich soll ein View werden.

Code:

CREATE TABLE istdaten (
ID INTEGER PRIMARY KEY  NOT NULL,
name TEXT,
vorname TEXT,
namegp TEXT,
periode TEXT,
betrag REAL
);

INSERT INTO "istdaten" VALUES(1,'Max','Mustermann','Max Mustermann','2013-12-01',380);
INSERT INTO "istdaten" VALUES(2,'Max','Mustermann','Max Mustermann','2014-01-01',350);
INSERT INTO "istdaten" VALUES(3,'Max','Mustermann','Max Mustermann','2014-02-01',200);
INSERT INTO "istdaten" VALUES(4,'Alias','Mussgeheim','Alias Mussgeheim','2014-01-01',325);
INSERT INTO "istdaten" VALUES(5,'Alias','Mussgeheim','Alias Mussgeheim','2014-02-01',325);
INSERT INTO "istdaten" VALUES(6,'Eva','Musterfrau','Eva Musterfrau','2014-02-01',200);
INSERT INTO "istdaten" VALUES(7,'Eva','Musterfrau','Eva Musterfrau','2014-03-01',200);

CREATE TABLE plandaten (
ID INTEGER PRIMARY KEY  NOT NULL,
namegp TEXT,
beginn TEXT,
ende TEXT,
betrag REAL
);

INSERT INTO "plandaten" VALUES(1,'Max Mustermann','2013-11-01','2014-02-28',200);
INSERT INTO "plandaten" VALUES(2,'Alias Mussgeheim','2014-01-01','2014-02-28',325);
INSERT INTO "plandaten" VALUES(3,'Eva Musterfrau','2014-01-01','2014-02-28',250);

CREATE TABLE vergleich (
name TEXT,
vorname TEXT,
namegp TEXT,
periode TEXT,
planzahl REAL,
istzahl REAL
);

INSERT INTO "vergleich" VALUES('Mustermann','Max','Max Mustermann','2013-11-01',200,NULL);
INSERT INTO "vergleich" VALUES('Mustermann','Max','Max Mustermann','2013-12-01',200,380);
INSERT INTO "vergleich" VALUES('Mustermann','Max','Max Mustermann','2014-01-01',200,350);
INSERT INTO "vergleich" VALUES('Mussgeheim','Alias','Alias Mussgeheim','2014-01-01',325,325);
INSERT INTO "vergleich" VALUES('Mustermann','Max','Max Mustermann','2014-02-01',200,200);
INSERT INTO "vergleich" VALUES('Musterfrau','Eva','Eva Musterfrau','2014-01-01','',100);
INSERT INTO "vergleich" VALUES('Mussgeheim','Alias','Alias Mussgeheim','2014-02-01',325,325);
INSERT INTO "vergleich" VALUES('Musterfrau','Eva','Eva Musterfrau','2014-02-01',250,200);
INSERT INTO "vergleich" VALUES('Musterfrau','Eva','Eva Musterfrau','2014-03-01',250,200);

Vielen Dank schon einmal für jegliche Hilfe

Wolf-Tilmann

Ach ja: Datenbank: SQLite

akretschmer · 25 März 2014

Wolf-Tilmann schrieb:
Hallo zusammen,

ich hätte wieder einmal ein Problem bei dem ich Eure Hife benötige:

Ich habe zwei Tabellen. Eine mit Plan- und eine mit Ist-daten.

In der Tabelle plandaten sind logischwerweise die Planungen; in der Tabelle istdaten die tatsächlich entstandenen Daten. Da wir das etwas großzügig handhaben müssen stimmen die Zeiten nicht immer überein. Es gibt Vorauszahlungen, dann ist der Zeitpunkt in plandaten.beginn vor dem Zeitpunkt istdaten.periode; es gibt Nachzahlungen (plandaten.beginn nach istdaten.periode); meist erfolgen die Zahlungen aber pünktlich (plandaten.beginn = istdaten.periode).

Meine Aufgabe ist es jetzt die Plan- und Ist-daten je Person in einem View einander gegenüberzustellen.

Ich denke, das läuft auf einen JOIN on namegp sowie einer Aufdröslung der plandaten auf die einzelnen Monate hin. Das könnte so erfolgen:

Code:

test=*# select * from (select ('2013-11-01'::date + s * '1 month'::interval)::date monat from generate_Series(0,5)s) monate left join plandaten p on monate.monat between p.beginn::date and p.ende::date ;
  monat  | id |  namegp  |  beginn  |  ende  | betrag
------------+----+------------------+------------+------------+--------
 2013-11-01 |  1 | Max Mustermann  | 2013-11-01 | 2014-02-28 |  200
 2013-12-01 |  1 | Max Mustermann  | 2013-11-01 | 2014-02-28 |  200
 2014-01-01 |  1 | Max Mustermann  | 2013-11-01 | 2014-02-28 |  200
 2014-01-01 |  2 | Alias Mussgeheim | 2014-01-01 | 2014-02-28 |  325
 2014-01-01 |  3 | Eva Musterfrau  | 2014-01-01 | 2014-02-28 |  250
 2014-02-01 |  1 | Max Mustermann  | 2013-11-01 | 2014-02-28 |  200
 2014-02-01 |  2 | Alias Mussgeheim | 2014-01-01 | 2014-02-28 |  325
 2014-02-01 |  3 | Eva Musterfrau  | 2014-01-01 | 2014-02-28 |  250
 2014-03-01 |  |  |  |  |
 2014-04-01 |  |  |  |  |
(10 rows)

Da hast nun die Spalten monat und namegp, mit denen Du Deine Istdaten nun joinen kannst -> das überlasse ich Dir zu Übung.

PostgreSQL, ob sqllite ein generate_Series() hat weiß ich nicht. Notfals den @Hony% fragen ;-)

Hony% · 25 März 2014

akretschmer schrieb:
ob sqllite ein generate_Series() hat weiß ich nicht. Notfals den @Hony% fragen ;-)

Zucker gibt's in SQLite nicht.

Mit der aktuellen Version von SQLite ist aber das hier möglich:

Code:

WITH RECURSIVE Kalender (Tag)
AS (
SELECT '2014-01-01' AS Tag
UNION ALL
SELECT date(Tag,'+1 month')
FROM Kalender
WHERE Tag < '2014-12-01'
)
SELECT * FROM Kalender;

Code:

sqlite> WITH RECURSIVE Kalender (Tag)
   ...> AS (
   ...> SELECT '2014-01-01' AS Tag
   ...> UNION ALL
   ...> SELECT date(Tag,'+1 month')
   ...> FROM Kalender
   ...> WHERE Tag < '2014-12-01'
   ...> )
   ...> SELECT * FROM Kalender;
2014-01-01
2014-02-01
2014-03-01
2014-04-01
2014-05-01
2014-06-01
2014-07-01
2014-08-01
2014-09-01
2014-10-01
2014-11-01
2014-12-01

Der Rest sollte sich leicht umformen lassen.

Wolf-Tilmann · 25 März 2014

Kapiere ich nicht.

Liest sich wie eine Antwort auf eine völlig andere Fragestellung?!?

Hony% · 25 März 2014

Wolf-Tilmann schrieb:
Liest sich wie eine Antwort auf eine völlig andere Fragestellung?!?

Dann solltest du die Fragestellung am besten konkretisieren. Wie soll denn das Ergebnis aussehen?

Wolf-Tilmann · 25 März 2014

Du schreibst etwas von Kalender und verwendest ein festes Datum (2014-01-01)

Aus den Tabellen

- plandaten
- istdaten

soll ein VIEW wie der beigefügte Screenshot generiert werden.

Schönen Abend
Wolf-Tilmann

akretschmer · 25 März 2014

Wolf-Tilmann schrieb:
Du schreibst etwas von Kalender und verwendest ein festes Datum (2014-01-01)

Du verwendest in Deinen Tabellen Datumsangaben. Diese definierst Du als TEXT. Du willst bestimmte Operationen drauf ausführen, z.B. prüfen, ob bestimmte Datumsangaben in Datumsbereichen enthalten sind.

Wie, denkst Du, soll das gehen? TEXT ist ein denkbar schlechter Datentyp. Damit geht es los.

Hony% · 25 März 2014

akretschmer schrieb:
Wie, denkst Du, soll das gehen? TEXT ist ein denkbar schlechter Datentyp. Damit geht es los.

Da muss ich dir jetzt widersprechen. SQLite kennt keinen DATE/TIME Datentyp und speichert ein ISO Datum in einem TEXT-Datentyp.

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").

REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.

INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

akretschmer · 25 März 2014

Hony% schrieb:
Da muss ich dir jetzt widersprechen. SQLite kennt keinen DATE/TIME Datentyp und speichert ein ISO Datum in einem TEXT-Datentyp.

Oh, wußte ich nicht.

Hony% · 26 März 2014

Wolf-Tilmann schrieb:
Es gibt Vorauszahlungen, dann ist der Zeitpunkt in plandaten.beginn vor dem Zeitpunkt istdaten.periode; es gibt Nachzahlungen (plandaten.beginn nach istdaten.periode); meist erfolgen die Zahlungen aber pünktlich (plandaten.beginn = istdaten.periode).

Meine Aufgabe ist es jetzt die Plan- und Ist-daten je Person in einem View einander gegenüberzustellen.
Bei Vorauszahlungen beginnt die Auflisttung mit istdaten.periode; bei Regel- und Nachzahlungen mit dem Wert aus plandaten.beginn.

Code:

INSERT INTO "istdaten" VALUES(1,'Max','Mustermann','Max Mustermann','2013-12-01',380);
INSERT INTO "istdaten" VALUES(6,'Eva','Musterfrau','Eva Musterfrau','2014-02-01',200);

INSERT INTO "plandaten" VALUES(1,'Max Mustermann','2013-11-01','2014-02-28',200);
INSERT INTO "plandaten" VALUES(3,'Eva Musterfrau','2014-01-01','2014-02-28',250);

Irgendwie widersprechen deine Beispieldaten dem gewünschten Ergebnis. Könntest du das bitte noch einmal überprüfen?

Wolf-Tilmann · 26 März 2014

Hony% schrieb:
Code:

INSERT INTO "istdaten" VALUES(1,'Max','Mustermann','Max Mustermann','2013-12-01',380); INSERT INTO "istdaten" VALUES(6,'Eva','Musterfrau','Eva Musterfrau','2014-02-01',200); INSERT INTO "plandaten" VALUES(1,'Max Mustermann','2013-11-01','2014-02-28',200); INSERT INTO "plandaten" VALUES(3,'Eva Musterfrau','2014-01-01','2014-02-28',250);

Irgendwie widersprechen deine Beispieldaten dem gewünschten Ergebnis. Könntest du das bitte noch einmal überprüfen?

Danke Dir.
Ich hatte in meinem Fragetext die Tabellennamen durcheinandergebracht.

So müsste es heißen:

Es gibt Vorauszahlungen, dann ist der Zeitpunkt in istdaten.periode vor dem Zeitpunkt plandaten.beginn;
es gibt Nachzahlungen (plandaten.beginn (eigentlich plandaten.ende aber Monat ist Monat) nach istdaten.periode);
meist erfolgen die Zahlungen aber pünktlich (plandaten.beginn = istdaten.periode).

Vorauszahlungen
plandaten.beginn ohne Wert
istdaten.periode mit Wert

Regelzahlung
plandaten.beginn mit Wert
istdaten.periode mit Wert

Nachzahlung
plandaten.beginn mit Wert
istdaten.periode ohne Wert

Der Begriff "Nachzahlung" ist etwas unglücklich - mir fällt aber momentan kein besserer ein.

P.S. kann man hier eine Tabelle eingeben?

INSERT INTO "plandaten" VALUES(1,'Max Mustermann','2013-11-01','2014-02-28',200);
INSERT INTO "plandaten" VALUES(3,'Eva Musterfrau','2014-01-01','2014-02-28',250);

Theoretisch könnte auch eine andere Tabelle dort stehen (baue ich mir über einen View zusammen). Die Tabelle plandaten umfasst den jeweils kompletten Zeitraum der Planung.

Code:

INSERT INTO "plandaten" VALUES (NULL,'Max Mustermann','2013-11-01',200);
INSERT INTO "plandaten" VALUES (NULL,'Max Mustermann','2013-12-01',200);
INSERT INTO "plandaten" VALUES (NULL,'Max Mustermann','2014-01-01',200);
INSERT INTO "plandaten" VALUES (NULL,'Max Mustermann','2014-02-01',200);

INSERT INTO "plandaten" VALUES(NULL,'Eva Musterfrau','2014-01-01',250);
INSERT INTO "plandaten" VALUES(NULL,'Eva Musterfrau','2014-02-01',250);

Hony% · 26 März 2014

Wolf-Tilmann schrieb:
P.S. kann man hier eine Tabelle eingeben?

Du kannst den Output der Konsolenanwendung sqlite3 hier in einem Codefenster eingeben.

Hony% · 26 März 2014

Wolf-Tilmann schrieb:

Code:

INSERT INTO "plandaten" VALUES (NULL,'Max Mustermann','2013-11-01',200);
INSERT INTO "plandaten" VALUES (NULL,'Max Mustermann','2013-12-01',200);
INSERT INTO "plandaten" VALUES (NULL,'Max Mustermann','2014-01-01',200);
INSERT INTO "plandaten" VALUES (NULL,'Max Mustermann','2014-02-01',200);

INSERT INTO "plandaten" VALUES(NULL,'Eva Musterfrau','2014-01-01',250);
INSERT INTO "plandaten" VALUES(NULL,'Eva Musterfrau','2014-02-01',250);

Die Datensätze passen jetzt nicht so ganz zu den vorherigen. Könntest du bitte noch einmal einen gesamten Dump der Beispieldatenbank machen?

Wolf-Tilmann · 27 März 2014

Hier der Dump:

Code:

BEGIN TRANSACTION;

CREATE TABLE plandaten (
ID INTEGER PRIMARY KEY  NOT NULL,
namegp TEXT,
beginn TEXT,
ende TEXT,
betrag REAL
);

INSERT INTO "plandaten" VALUES(1,'Max Mustermann','2013-11-01','2014-02-28',200.0);
INSERT INTO "plandaten" VALUES(2,'Alias Mussgeheim','2014-01-01','2014-02-28',325.0);
INSERT INTO "plandaten" VALUES(3,'Eva Musterfrau','2014-01-01','2014-02-28',250.0);

CREATE TABLE istdaten (
ID INTEGER PRIMARY KEY  NOT NULL,
name TEXT,
vorname TEXT,
namegp TEXT,
periode TEXT,
betrag REAL
);


INSERT INTO "istdaten" VALUES(1,'Max','Mustermann','Max Mustermann','2013-12-01',380.0);
INSERT INTO "istdaten" VALUES(2,'Max','Mustermann','Max Mustermann','2014-01-01',350.0);
INSERT INTO "istdaten" VALUES(3,'Max','Mustermann','Max Mustermann','2014-02-01',200.0);
INSERT INTO "istdaten" VALUES(4,'Alias','Mussgeheim','Alias Mussgeheim','2014-01-01',325.0);
INSERT INTO "istdaten" VALUES(5,'Alias','Mussgeheim','Alias Mussgeheim','2014-02-01',325.0);
INSERT INTO "istdaten" VALUES(6,'Eva','Musterfrau','Eva Musterfrau','2014-02-01',200.0);
INSERT INTO "istdaten" VALUES(7,'Eva','Musterfrau','Eva Musterfrau','2014-03-01',200.0);

COMMIT;

Als Ergebnis versuche ich einen View wie oben im Bildschirmfoto1.png dargestellt zu erreichen.

Hony% · 27 März 2014

Sowohl bei Max als auch Eva liegt plandaten.begin vor dem kleinsten wert in istdaten.periode.

Da kann sich das gewünschte Ergebnis nicht einstellen. Wie müssen deine Beispieldaten also korrekt aussehen?

Zwei Tabellen zusammenführen - nicht UNION

Wolf-Tilmann

Aktiver Benutzer

akretschmer

Datenbank-Guru

Hony%

Datenbank-Guru

Wolf-Tilmann

Aktiver Benutzer

Hony%

Datenbank-Guru

Wolf-Tilmann

Aktiver Benutzer

Anhänge

akretschmer

Datenbank-Guru

Hony%

Datenbank-Guru

akretschmer

Datenbank-Guru

Hony%

Datenbank-Guru

Wolf-Tilmann

Aktiver Benutzer

Hony%

Datenbank-Guru

Hony%

Datenbank-Guru

Wolf-Tilmann

Aktiver Benutzer

Hony%

Datenbank-Guru