Zwei Tabellen zusammenführen - nicht UNION

Wolf-Tilmann

Aktiver Benutzer
Beiträge
35
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
 
Zuletzt bearbeitet:
Werbung:
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 ;-)
 
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.
 
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
 

Anhänge

  • Bildschirmfoto1.png
    Bildschirmfoto1.png
    25,9 KB · Aufrufe: 18
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.
 
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.
 
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?
 
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

Bildschirmfoto2.png

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);
 
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?
 
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.
 
Werbung:
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?
 
Zurück
Oben