Spalte mit mehreren WHERE abfragen

[Wolf-Tilmann]

Hallo allerseits,

vielleicht könnt Ihr mir hier weiterhelfen?

Ich habe eine Tabelle (ca. 350o Datensätze) mit Personen.
Viele Personen sind doppelt- und dreifach erfasst.

Ich soll jetzt eine Übersicht zusammenstellen, aus welcher hervorgeht
1. Spalte: wer von Anbeginn an bis zum 30.09.2011 dabei war
2. Spalte: wer von Anbeginn an bis zum 30.09.2012 dabei war
3. Spalte: wer von Anbeginn an bis zum 30.09.2013 dabei war
4. Spalte: wer von Anbeginn an bis zum 30.09.2014 dabei war
5. Spalte: wer von Anbeginn an bis zum 30.09.2015 dabei war
6. Spalte: wer von Anbeginn an bis zum 30.09.2016 dabei war

(Ich weiß, 2015 und 2016 liegen in der Zukunft, werden aber später benötigt)

Zwar wäre das mit vielen SELECTTs und UNIONs möglich. Die Ergebnisse stünden aber untereinander, sollen aber nebeneinander stehen.

CREATE VIEW IF NOT EXISTS v_anzahlpersonen
AS
(SELECT COUNT(DISTINCT(name||vorname)) FROMpersonen WHERE beginn < '2011-09-30') AS bis_30_09_2011,

gibt mir korrekt einen View mit den Zahlen bis 30.09.2011 aus.

Meine ganzen Versuche die ganzen WHERE hintereinanderzubringen schlugen fehl.


z.B.:

CREATE VIEW IF NOT EXISTS v_anzahlpersonen
AS 
(SELECT count(distinct(name||vorname)) from personen where beginn < '2011-09-30') AS bis_30_09_2011, 
(SELECT count(distinct(name||vorname)) from personen where beginn < '2012-09-30') AS bis_30_09_2012, 
(SELECT count(distinct(name||vorname)) from personen where beginn < '2013-09-30') AS bis_30_09_2013, 
(SELECT count(distinct(name||vorname)) from personen where beginn < '2014-09-30') AS bis_30_09_2014, 
(SELECT count(distinct(name||vorname)) from personen where beginn < '2015-09-30') AS bis_30_09_2015, 
(SELECT count(distinct(name||vorname)) from personen where beginn < '2016-09-30') AS bis_30_09_2016;

Könnt Ihr mir weiterhelfen?

Danke Euch
Wolf-Tilmann

 

[akretschmer]

Zwar wäre das mit vielen SELECTTs und UNIONs möglich. Die Ergebnisse stünden aber untereinander, sollen aber nebeneinander stehen.

Das ginge prinzipiell via select ... case when ... then 1 else 0 end as ..., das machst Du für alle Deine Prüfungen. Diese Tabelle, die da rauskommt, dann noch mal aggregieren. Ich hab sowas hier schon mal genauer erklärt, suche mal nach 'zeilen zu spalten'

 

[Wolf-Tilmann]

Danke Dir.
Ich habe jetzt über Deine Antwort und meine Frage eine Nacht geschlafen und bin zu folgendem Ergebnis gekommen: meine Frage war hirnrissig!

Es ist sicherlich besser, ich hole etwas aus.

Wie sagte mein erster Chef immer über mich: "Der ist so faul, dass er sich anstrengt um nichts tun zu müssen!" Einige Arbeiten, welche ich hier machen muss, habe ich in eine SQLite-Datenbank umgesetzt. Das war zwar viel Arbeit (mit Eurer Unterstützung) hat mir aber, sobald es lief, auch viel Zeit geschaffen.
Ich verlasse demnächst diesen Job hier. Meine Nachfolgerin hat absolut nichts mit Datenbanken am Hut.
Damit sie aber nicht in der Luft hängt und immer wieder Daten per Hand aus zig Akten holen muss habe ich vor meine Datenbank um einige Ausgaben zu erweitern.
So muss ich unter anderem alljährlich mit Stichtag 30. September die Anzahl der bisher beteiligten Personen liefern.
Dank meiner Datenbank liefert mit folgende Abfrage das gewünschte Ergebnis

SELECT COUNT(DISTINCT(name||vorname)) FROM vertraege WHERE beginn <= '2014-09-30';

Ist ja eine Kleinigkeit.
Für meine Nachfolgerin habe ich mir gedacht, ich lege in der Datenbank eine Tabelle stichtage an

CREATE TABLE stichtage (
stichtag  DATETIME NOT NULL
);
INSERT INTO "stichtage" VALUES('2011-09-30');
INSERT INTO "stichtage" VALUES('2012-09-30');
INSERT INTO "stichtage" VALUES('2013-09-30');
INSERT INTO "stichtage" VALUES('2014-09-30');
INSERT INTO "stichtage" VALUES('2015-09-30');
INSERT INTO "stichtage" VALUES('2016-09-30');

Da sie die Datenbank weiterführen muss, hat sie lediglich weitere Stichtage zu erfassen. Den Rest soll die Datenbank von alleine machen.
Hier noch eine etwas "überarbeitete" Tabelle

CREATE TABLE personen (
name TEXT NOT NULL,
vorname TEXT NOT NULL,
beginn DATETIME NOT NULL,
);
INSERT INTO "personen" VALUES('Müller','Max','2011-08-03');
INSERT INTO "personen" VALUES('Müller','Max','2013-08-03');
INSERT INTO "personen" VALUES('Meier','Anton','2013-09-03');
INSERT INTO "personen" VALUES('Huber','Maximilian','2014-02-01');
INSERT INTO "personen" VALUES('Schmid','Otto','2014-07-04');
INSERT INTO "personen" VALUES('Schmid','Otto','2015-01-04');

Als Ergebnis, d.h. als View, sollte eine Tabelle wie diese generiert werden:

CREATE TABLE ergebnis (
stichtag DATETIME NOT NULL,
anzahl INTEGER
);
INSERT INTO "ergebnis" VALUES('2011-09-30','1');
INSERT INTO "ergebnis" VALUES('2012-09-30','1');
INSERT INTO "ergebnis" VALUES('2013-09-30','3');
INSERT INTO "ergebnis" VALUES('2014-09-30','5');
INSERT INTO "ergebnis" VALUES('2015-09-30','6');
INSERT INTO "ergebnis" VALUES('2016-09-30','6');

Der View müsste nach meiner Ansicht über JOIN oder SUBSELECT generiert werden.

Leider wird bei meinen Versuchen nur eine Zahl als Count geliefert. Die Daten nach stichtag werden nicht abgearbeitet.

Beispielsversuche:

SELECT COUNT(DISTINCT(name||vorname)) FROM vertraege WHERE BEGINN <= (SELECT stichtag FROM stichtage);
SELECT stichtag, COUNT(DISTINCT(name||vorname)) FROM vertraege, stichtage GROUP BY stichtag HAVING beginn <= stichtag;

Könntet Ihr mir bitte noch einmal weiterhelfen?

Danke
Euer
Wolf-Tilmann

 

[Distrilec]

Erstmal danke für die DDLs
Und du suchst wahrscheinlich so etwas in der Art?

Select st.stichtag,
       count(*)
From   stichtage st
Left   Join personen pe
On     pe.beginn <= st.stichtag
Group  By st.stichtag
Order   By st.stichtag asc

Edit:
Hab noch eine aufsteigende Sortierung nach dem Stichtag hinzugefügt

 

[akretschmer]

Das ist aus Deinen Daten nicht ganz eindeutig, da Max und Otto je 2 mal vorkommen.

test=*# select * from stichtage ;
  stichtag
------------
2011-09-30
2012-09-30
2013-09-30
2014-09-30
2015-09-30
2016-09-30
(6 rows)

Time: 0,181 ms
test=*# select * from personen ;
  name  |  vorname  |  beginn
--------+------------+------------
Müller | Max  | 2011-08-03
Müller | Max  | 2013-08-03
Meier  | Anton  | 2013-09-03
Huber  | Maximilian | 2014-02-01
Schmid | Otto  | 2014-07-04
Schmid | Otto  | 2015-01-04
(6 rows)

Du kannst mit solchen Digen spielen:

test=*# select name, vorname, beginn, stichtag, row_number() over (partition by name, vorname order by stichtag - beginn asc) from (select * from personen cross join stichtage) foo where beginn < stichtag  ;  
  name  |  vorname  |  beginn  |  stichtag  | row_number  
--------+------------+------------+------------+------------  
Huber  | Maximilian | 2014-02-01 | 2014-09-30 |  1  
Huber  | Maximilian | 2014-02-01 | 2015-09-30 |  2  
Huber  | Maximilian | 2014-02-01 | 2016-09-30 |  3  
Meier  | Anton  | 2013-09-03 | 2013-09-30 |  1  
Meier  | Anton  | 2013-09-03 | 2014-09-30 |  2  
Meier  | Anton  | 2013-09-03 | 2015-09-30 |  3  
Meier  | Anton  | 2013-09-03 | 2016-09-30 |  4  
Müller | Max  | 2011-08-03 | 2011-09-30 |  1  
Müller | Max  | 2013-08-03 | 2013-09-30 |  2  
Müller | Max  | 2013-08-03 | 2014-09-30 |  3  
Müller | Max  | 2011-08-03 | 2012-09-30 |  4  
Müller | Max  | 2013-08-03 | 2015-09-30 |  5  
Müller | Max  | 2011-08-03 | 2013-09-30 |  6  
Müller | Max  | 2013-08-03 | 2016-09-30 |  7  
Müller | Max  | 2011-08-03 | 2014-09-30 |  8  
Müller | Max  | 2011-08-03 | 2015-09-30 |  9  
Müller | Max  | 2011-08-03 | 2016-09-30 |  10  
Schmid | Otto  | 2014-07-04 | 2014-09-30 |  1  
Schmid | Otto  | 2015-01-04 | 2015-09-30 |  2  
Schmid | Otto  | 2014-07-04 | 2015-09-30 |  3
Schmid | Otto  | 2015-01-04 | 2016-09-30 |  4
Schmid | Otto  | 2014-07-04 | 2016-09-30 |  5
(22 rows)

Wenn Du je Person den ersten Stichtag suchst:

test=*# select * from (select name, vorname, beginn, stichtag, row_number() over (partition by name, vorname order by stichtag - beginn asc) from (select * from personen cross join stichtage) foo where beginn < stichtag ) bla where row_number = 1 ;
  name  |  vorname  |  beginn  |  stichtag  | row_number
--------+------------+------------+------------+------------
Huber  | Maximilian | 2014-02-01 | 2014-09-30 |  1
Meier  | Anton  | 2013-09-03 | 2013-09-30 |  1
Müller | Max  | 2011-08-03 | 2011-09-30 |  1
Schmid | Otto  | 2014-07-04 | 2014-09-30 |  1
(4 rows)

Oder je Person die Stichtage nach Beginn:

test=*# select * from (select name, vorname, beginn, stichtag, row_number() over (partition by name, vorname order by stichtag - beginn desc) from (select * from personen cross join stichtage) foo where beginn < stichtag ) bla  ;
  name  |  vorname  |  beginn  |  stichtag  | row_number
--------+------------+------------+------------+------------
Huber  | Maximilian | 2014-02-01 | 2016-09-30 |  1
Huber  | Maximilian | 2014-02-01 | 2015-09-30 |  2
Huber  | Maximilian | 2014-02-01 | 2014-09-30 |  3
Meier  | Anton  | 2013-09-03 | 2016-09-30 |  1
Meier  | Anton  | 2013-09-03 | 2015-09-30 |  2
Meier  | Anton  | 2013-09-03 | 2014-09-30 |  3
Meier  | Anton  | 2013-09-03 | 2013-09-30 |  4
Müller | Max  | 2011-08-03 | 2016-09-30 |  1
Müller | Max  | 2011-08-03 | 2015-09-30 |  2
Müller | Max  | 2011-08-03 | 2014-09-30 |  3
Müller | Max  | 2013-08-03 | 2016-09-30 |  4
Müller | Max  | 2011-08-03 | 2013-09-30 |  5
Müller | Max  | 2013-08-03 | 2015-09-30 |  6
Müller | Max  | 2011-08-03 | 2012-09-30 |  7
Müller | Max  | 2013-08-03 | 2014-09-30 |  8
Müller | Max  | 2011-08-03 | 2011-09-30 |  9
Müller | Max  | 2013-08-03 | 2013-09-30 |  10
Schmid | Otto  | 2014-07-04 | 2016-09-30 |  1
Schmid | Otto  | 2015-01-04 | 2016-09-30 |  2
Schmid | Otto  | 2014-07-04 | 2015-09-30 |  3
Schmid | Otto  | 2015-01-04 | 2015-09-30 |  4
Schmid | Otto  | 2014-07-04 | 2014-09-30 |  5
(22 rows)

Wie gesagt, das ist nicht ganz eindeutig, da Du Personen mehrfach hast.

 

[Wolf-Tilmann]

Hallo Distrilec,

Danke, das funzt .
Ich schreibe es jetzt passend zu meiner Datenbank um.

@akretschmer:
Ja, Personen können öfter vorkommen. Sie kommen und gehen. Unabhängig davon darf eine Person nur einmal im Ergebnis erfasst sein.

Danke Euch
Einen schönen Fasching
Wolf-Tilmann

 

[Distrilec]

Das mit dem einmalig vorkommen ist ja auch nicht soooo schwer ^^

Select st.stichtag,
       count(distinct pe.vorname || pe.name)
From   fps_max_test_stich st
Left   Join fps_max_test_pers pe
On     pe.beginn <= st.stichtag
Group  By st.stichtag
Order   By st.stichtag asc

Problematisch wird es dann erst, wenn zwei Mitarbeiter den gleichen Namen haben... Deswegen würde ich generell immer über eine Personalnummer gehen

 

[Wolf-Tilmann]

Ja, so in etwa habe ich es auch zurechtgebastelt count+distinct.
und es funktioniert perfekt!

Aaaaaaaach, Du nennst einen Traum. Personalnummern wie schön wäge das.

Schönen Fasching
Wolf-Tilman