(SQL) Anzahl Tage ohne Buchung

[Nersgatt]

Moin!

Mich hat meine SQL-Muse verlassen.

Ich habe eine Tabelle mit Personen und dazu eine Buchungstabelle mit einem Timestamp:

Personen:

ID	Nachname	Vorname
1	Müller	Max
2	Schulze	Fritz
3	Mustermann	Erika

Buchungen:

ID	person_id	zeitpunkt	weitere Daten
1	1	18.03.2015 12:03	....
1	1	18.03.2015 15:12	....
1	1	20.03.2015 12:03	....
1	2	17.03.2015 15:00	....

Also eine 1:n Verknüpfung über buchungen.person_id <-> personen.id
Es kann pro Tag für jede Person mehr als eine Buchung geben. Es kann aber auch Tage geben, an denen es keine Buchung für eine Person gibt.

Nun möchte ich für einen gegebenen Zeitraum die Anzahl der Tage ermitteln, an denen es KEINE Buchung für die Person gibt.

Also wenn ich als Zeitraum 17.03. bis 21.03. abfragen, wünsche ich mir folgendes Ergebnis:

person_id	nachname	vorname	Anzahl
1	Müller	Max	3
2	Schulze	Fritz	4
3	Mustermann	Erika	5

Wie könnte man sowas anstellen? Wichtig ist mir auch, dass das möglichst Performant abläuft. Ich werde so ca. 1000 Personendatensätze haben und im Schnitt 4 Buchungsdatensätze pro Person und Tag. Der Abfragezeitraum kann auch mal ein ganzes Jahr sein. Somit also knapp 1500 Datensätze pro Person(!) im Abfragezeitraum.

Vielen Dank!

[mkinzler]

Unabhängig vom Wochentag?

[Nersgatt]

Zitat von mkinzler:

Unabhängig vom Wochentag?

Wochentag ist egal.

[mkinzler]

Am Einfachsten würde das mit einem Join auf eine (temporäre) Tagestabelle funktionieren

[Nersgatt]

Kannst Du mal ein Beispiel skizzieren, wie Du das lösen würdest?

[p80286]

Typische "finde was es nicht gibt" - Aufgabenstellung.
Ich würde eine Hilfstabelle mit einem Eintrag pro Person und Tag erstellen und dann

SQL-Code:

select Nachname, Count(*) 
from Personen join Hilfstabelle on (Personen.id=Hilfstabelle.id)
            left join Buchungen on (Personen.id=Buchungen.id)
where Buchungen.id is null
Group by Personen.id

Gruß
K-H

wieder mal nicht schnell genug *schnief*

[himitsu]

Eine Sequenz aller betreffender Tage generieren/laden (Anfang bis Ende) und per WHERE die ausschließen, wo es eine Buchung gibt-

Sequenz: Entweder aus einem Generator, einer StoredProc mit Schleife oder aus einer Tabelle, wo alle Tage des Jahres (vieler Jahre) drin stehen.

[mkinzler]

Die Personen müssen da gar nicht drinstehen. Es reicht alle Tage im Intervall oder alle tage im Gesamtbereich ( wenn Tabelle nicht temporär). Diese könnte man in einer einfachen Whileschleife anlegen lassen ( z.B. miz Hilfe von date_add())

[Nersgatt]

Ok, jetzt lichtet sich der Nebel. Ich danke euch!

[jobo]

Da es nicht um explizite Darstellung der Tage geht, sondern nur um die Anzahl, sollte eine Berechnung ohne Hilfdaten möglich sein.

Vorschlag ('2015-03-17' und '2015-03-21' sind durch die Range Parameter zu ersetzen)

Code:

select distinct na.pid,
                date('2015-03-21') - date('2015-03-17') -
                ifNULL(count_, 0) as DaysWithoutOrder
  from torder /* besser tperson*/ na
  left join (select pid, count(*) as count_
               from (select distinct pid, date(o.ord_datetime)
                       from torder o
                      where o.ord_datetime between '2015-03-17'
                                               and '2015-03-21') y
              group by pid) x
    on na.pid = x.pid

• Selektiere alle pid per Tagesanteil der Order im gewünschten Zeitraum,
• Gruppiere das Ergebnis erneut nach pid und zähle die vorhanden Tage mit Bestellung,
• Joine das Ergebnis mit der Liste aller Personen (um ganz fehlende personen mit einzubeziehen) und verrechne die Tage mit der Anzahl der Tage im Wunschintervall.