Office: (Office 2010) Zeiträume in zwei Tabellen vergleichen

Ich habe die Gedanken aus Beitrag #43 in ein Beispiel umgesetzt.

- Eine passende Kalendertabelle wird live erstellt.
- SQL lasse ich das tun, was es sehr gut kann. Die Schwächen von SQL werden über in Temp-Tabellen gespeicherte Zwischenergebnisse sowie einen über VBA gesteuerten Ablauf entschärft.

Auf die Auslagerung der Temp-Tabellen in ein zusätzliches temporäres Backend habe ich hier im Beispiel der Einfachheit halber verzichtet.

(Falls die Temp-Tabellen nach Ausführen der Startenprozedur noch nicht sichtbar sind, in den Navigationsbereich klicken und F5 zur Aktualisierung klicken.)

Falls der TE sich ein Testen zutraut, wäre ich über eine Aussage zur Performance recht dankbar.

 

Ich schliesse mich an, was meine geänderte Prozedur aus #44 angeht.

 

Anbei die DB im 2000-er Format.

 

Hallo an Alle,

ich habe jetzt im ersten Schritt die Version von Eberhardt getestet.

Ist natürlich fern ab von dem was ich am Ende wirklich technisch nachvollziehen kann im VBA-Code selbst.

Und am Ende hat es funktioniert *top

Leider musste ich meine Daten nochmal konsolidieren da mir nach 45 Minuten der PC sagte das nicht genügend Ressourcen zur Verfügung stehen und abgebrochen hat.

Ich habe dann nochmal den roten Stift die Hand genommen und Daten reduziert und dann hat es tatsächlich nach 30 Minuten geklappt.

Es waren 13000 Kunden mit 51000 Zeiträumen. der erste Tag an dem gerechnet werden musste, war dann der der 01.01.2010 welchen ich sozusagen dann auch als frühsten Tag eines Zeitraums eingegrenzt habe in TAB2.

Jetzt werde ich noch in der Stichprobe schauen, ob richtig gerechnet wurde.

Die Tabelle von Hans-Christian wird natürlich auch noch getestet *Smilie

Vielen Dank für die Hilfe

 

So habe jetzt ein paar Stichproben genommen und leider scheint die Berechnung teilweise nicht zu stimmen (sprich mal ist das Ergebnis korrekt und mal nicht).

Ich lade im Anhang mal die Datei mit meinen angepassten Werten/Ergebnissen hoch (leider nur abgespeckt da die maximale Dateigröße sonst überschritten wird).

Beispiele für Fehlberechnung:

Berechnung offene Zeiträume:
ID4 müsste als Zeitraum 18.08.2014 bis 31.08.2014 rauskommen

Berechnung erledigte ID:
ID33 , diese wird als erledigt angezeigt obwohl in TAB2 kein anderer Zeitraum existiert.

lg Sebastian

 

Moin Sebastian.
Ich hab Deine Tab1 und Tab2 in meine DB importiert (und umbenannt).
Bei mir ist es offensichtlich korrekt. (Ergebnisse in AV und TF).
Und dauert unter 5 Sekunden - fill_kal() braucht etwas länger als fehl_zeit().

 

Hallo Hans-Christian,

habe es probiert, leider bricht er nach einer Stunde ab wegen zu knapper Ressourcen.

Bis dahin hat er aber zumindest 2,5 Mio Datensätze in die Tabelle TAF geschrieben und 4 offene Zeiträume richtig angeben in TF.

Von der Performance scheint die Datei von Eberhardt besser zu laufen. Das Problem ist, ich weiß nicht ob das jetzt ggf. an der Berechnung liegt.

 

Das beste aus beiden Versionen wäre natürlich klasse *biggrin.gif*

Ich weiß , ich hab gut reden...aber ich kann das schlicht und ergreifend nicht *depressed

 

Das kann ich nicht nachvollziehen. ID4 beginnt am 01.01.2012 und hat kein Ende. Die Zeiträume aus Tab2 decken nur einen Teil des Zeitraumes ab und nicht den Zeitraum vollständig.

Oder sollen nur Restzeiträume ermittelt werden? Dann hatte ich die Aufgabe nicht verstanden und daher mit etwas anderer Zielsetzung gelöst.
Ich kann das umschreiben.

Das ist klar. Hier ist also ein zusätzlicher Abgleich mit Tab2 notwendig.
(Eine sofort gelieferte vollständige Tabelle hätte das offenbahrt.)

Code:

Was genau ist nicht verständlich?

 

Hallo Eberhardt,

ja die Aufgabe bestand im Auswerten der Teilzeiträume, wenn du das umschreiben könntest wäre das klasse *top

Bezüglich des VBA - Code meinte ich, dass zwar einzelne Sachen daraus für mich ableiten kann, aber niemals auch nur Ansatzweise so auf den Weg hätte bringen können, das am Ende so ein Ergebnis zur Folge hat.

lg Sebastian

 

Nun, wenn es nur um die Resttage und daraus gebildete Zeiträume geht, wird es einfacher und direkter und sollte auch ein Stück schneller sein.

Hinweise zur Demo:

- Zwischenergebnisse aus Abfragen werden nicht unmittelbar in aufbauenden Abfragen weiterverwendet, sondern in Temp-Tabellen gespeichert. In den Temp-Tabellen werden per DDL Felder nachindiziert und teilweise nachträglich mit einem Autowertfeld versehen.
Temp-Tabellen werden zu Beginn des Ablaufes komplett gelöscht und erst im Ablauf der Berechnung neu erstellt. Somit beziehen sich Berechnungsergebnisse auf die aktuellen Inhalte der Ursprungstabellen, Datenanomalien werden praktisch ausgeschlossen.

- In der verwendeten Kalendertabelle gibt es neben dem laufenden Datum als zusätzliche Felder den Vortag sowie den Folgetag zum Datum. Das bietet folgende Effekte:
Die Berechnung auf Vor- bzw. Folgetag findet nur genau einmal statt und kann dann nachgeschlagen werden. In Abfragen würde das ja mehrfach z.B. pro ID erfolgen.
Man kann unmittelbar im Datensatz verknüpfen, und über Tabellenfelder kann auch ein Index genutzt werden.

- Ein offengelassener bis-Wert (NULL) wird nicht per Nz abgefangen, sondern mit der Bedingung Datumswert Is Null . Damit laufen die Tage bis ans Ende der bereitgehaltenen Kalendertabelle. Die Kalendertabelle selber wird aber in der Abfrage auf MaxDate() + 1 begrenzt, wobei MaxDate() das höchste bis-Datum aus beiden Tabellen ist. Der Folgetag dazu ist also ein typischer Wert für eine Fortsetzung und somit geeignet, am Ende der Auswertung wieder in NULL rückverwandelt zu werden.

- Man kann nachvollziehen, dass eigene Möglichkeiten steigen, wenn man Tabellenstrukturen nicht nur per Assistenten zusammenklicken, sondern auch per Data Definition Language (DDL) erstellen und ändern kann.

 

Das ist ein guter Trick.
Darauf muss man erst mal kommen.
Hochachtung.
Werd ich mir merken.

 

Hallo Eberhardt,

das hat jetzt funktionier! *dance *top

Ich hatte allerdings ein paar komische Effekte beim erstmaligen Ausführen (habe das jetzt zweimal getestet und immer das gleiche Ergebnis).

Wenn ich erstmalig das Script auf meine frisch importierten Daten laufen lasse, rechnet er die offenen Zeiträume nicht richtig. Die abgedeckten Zeiträume stimmten bis jetzt jedoch. Ich habe dann einfach noch einmal das Script drüber laufen lassen und dann wurden auch die richtigen Ergebnisse angezeigt.

Eine schlüssige Erklärung hierzu habe nicht *confused.gif*

lg Sebastian

 

Beginne und Enden von offenen Zeiträumen werden getrennt ermittelt und in Temp-Tabellen abgelegt. Stimmen diese?

Nachfolgend werden immer erster Beginn und erstes Ende usf. zusammengeführt. Vielleicht gibt es da Probleme.