Office: (Office 2016) Zusammenhängende Zeitspannen ermitteln

Helfe beim Thema Zusammenhängende Zeitspannen ermitteln in Microsoft Access Hilfe um das Problem gemeinsam zu lösen; Hallo in die Runde, ich bin dabei eine Baustellenplanung zu erstellen, leider hänge ich im Moment und komme einfach nicht weiter. Mein Problem ist... Dieses Thema im Forum "Microsoft Access Hilfe" wurde erstellt von Guido S, 5. August 2018.

Seite 1 von 2

GUIDO S

Guido S

Zusammenhängende Zeitspannen ermitteln

Hallo in die Runde,

ich bin dabei eine Baustellenplanung zu erstellen, leider hänge ich im Moment und komme einfach nicht weiter.
Mein Problem ist folgendes:

Ich habe eine Tabelle mit folgenden Einträgen:

Pers_Nr Baustelle Datum
101 Baustelle1 01.02.2018
101 Baustelle1 02.02.2018
101 Baustelle1 03.02.2018
101 Baustelle2 04.02.2018
101 Baustelle2 05.02.2018
101 Baustelle2 06.02.2018
101 Baustelle2 07.02.2018
101 Baustelle2 08.02.2018
101 Baustelle2 09.02.2018
201 BaustelleX 05.02.2018
201 BaustelleX 06.02.2018
201 BaustelleX 07.02.2018
201 BaustelleX 08.02.2018
201 BaustelleX 09.02.2018
201 BaustelleX 10.02.2018
201 BaustelleX 11.02.2018
101 Baustelle3 02.05.2018
101 Baustelle3 03.05.2018
101 Baustelle3 04.05.2018
101 Baustelle3 05.05.2018
101 Baustelle3 06.05.2018
usw.

haben möchte ich:
Pers_Nr Baustelle Anfangsdatum Enddatum
101 Baustelle1 01.02.2018 03.02.2018
101 Baustelle2 04.02.2018 09.02.2018
101 Baustelle3 02.05.2018 06.05.2018
201 BaustelleX 05.02.2018 11.02.2018

Ich hoffe ihr erkennt worum es mir geht. Ich kriegs einfach nicht hin.
Ob über Sql oder vba ist egal.

Gruß Guido

:)

Guido S, 5. August 2018

#1
EBS17

ebs17

Code:

SELECT
T.Pers_Nr ,
T.Baustelle,
T.Datumswert AS Anfangsdatum,
(
SELECT
MIN(U.Ende) AS Ende
FROM
(
SELECT
T1.Datumswert AS Ende
FROM
EineTabelle AS T1
WHERE
NOT EXISTS
(
SELECT
NULL
FROM
EineTabelle AS X
WHERE
X.Datumswert = T1.Datumswert + 1
AND
X.Pers_Nr = T1.Pers_Nr
AND
X.Baustelle = T1.Baustelle
)
) AS U
WHERE
U.Ende >= T.Datumswert) AS Enddatum
FROM
EineTabelle AS T
WHERE
NOT EXISTS
(
SELECT
NULL
FROM
EineTabelle AS X
WHERE
X.Datumswert = T.Datumswert - 1
AND
X.Pers_Nr = T.Pers_Nr
AND
X.Baustelle = T.Baustelle
)
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

ebs17, 7. August 2018

#2
JOSEF P.

Josef P.

Hallo!

Als Ergänzung die obige SQL-Anweisung ohne Subselect im Select-Teil:
Code:

SELECT
T.Pers_Nr, T.Baustelle, T.Datum AS Von, Min(Ed.Datum) AS Bis
FROM
Pers_Baustelle AS T
INNER JOIN
(
select
Pers_Nr, Baustelle, Datum
from
Pers_Baustelle E
where
NOT EXISTS (select 1 from Pers_Baustelle Xe WHERE Xe.Pers_nr = E.Pers_Nr and Xe.Baustelle = E.Baustelle and Xe.Datum = E.Datum + 1)
) AS Ed ON (T.Baustelle = Ed.Baustelle AND T.Pers_Nr = Ed.Pers_nr AND Ed.Datum >= T.Datum)
WHERE
NOT Exists (select 1 from Pers_Baustelle Xs WHERE Xs.Pers_nr = T.Pers_Nr and Xs.Baustelle = T.Baustelle and Xs.Datum = T.Datum - 1)
GROUP BY T.Pers_Nr, T.Baustelle, T.Datum
ORDER BY T.Pers_Nr, T.Datum
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

mfg
Josef

Josef P., 7. August 2018

#3
EBS17

ebs17

Zusammenhängende Zeitspannen ermitteln

ohne Subselect im Select-Teil
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Sehr schön. Das gucke ich mir ab.

ebs17, 7. August 2018

#4
GUIDO S

Guido S

Vielen Dank für die schnelle Antwort.

Ich habe noch ein Problem,
die Tabelle hat für "nur" ein Jahr ca. 60.000 Datensätze.
Ich habe die Ausführung der Abfrage nach 20 Minuten abgebrochen.
Geht das über VBA vielleicht schneller?

Funktionieren tut es, das habe ich mit einer abgespeckten Tabelle ausprobiert.

Gruß Guido

Guido S, 7. August 2018

#5
EBS17

ebs17

Der Verzicht auf die Unterabfrage im SELECT-Teil vermeidet viel Aufwand und damit benötigte Zeit. Du verwendest also Josefs Vorschlag?

Sowie: Datenbankverarbeitung heißt regelmäßig Verarbeitung von größeren Datenmengen, das ist das Tagesgeschäft. Zum Einmaleins dazu gehört eine planmäßige Indizierung von Feldern, siehe Grundlagen - SQL ist leicht (8) - Index

Also alle drei Felder indizieren (einzeln).
Besser wäre hier noch ein zusammengesetzter Index über alle drei Felder, wenn man die Reihenfolge der Felder im Index in der Reihenfolge der Feldverwendung bei Vergleichen, Gruppierungen und Sortierungen wiederfindet. Das müsste noch punktuell nachgebessert werden:
Code:

SELECT
T.Pers_Nr, T.Baustelle, T.Datum AS Von, Min(Ed.Datum) AS Bis
FROM
Pers_Baustelle AS T
INNER JOIN
(
select
Pers_Nr, Baustelle, Datum
from
Pers_Baustelle E
where
NOT EXISTS (select 1 from Pers_Baustelle Xe WHERE Xe.Pers_nr = E.Pers_Nr and Xe.Baustelle = E.Baustelle and Xe.Datum = E.Datum + 1)
) AS Ed ON (T.Baustelle = Ed.Baustelle AND T.Pers_Nr = Ed.Pers_nr AND Ed.Datum >= T.Datum)
WHERE
NOT Exists (select 1 from Pers_Baustelle Xs WHERE Xs.Pers_nr = T.Pers_Nr and Xs.Baustelle = T.Baustelle and Xs.Datum = T.Datum - 1)
GROUP BY T.Pers_Nr, T.Baustelle, T.Datum
ORDER BY T.Pers_Nr, T.Datum
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

ebs17, 7. August 2018

#6
MARKUSXY

markusxy

\@Guido S
Schleifen rennen gegen Massenverarbeitung.
Wäre jetzt interessant herauszufinden, ob in diesem Fall Schleifen rennen besser ist.

Die Frage ist natürlich auch, ob du tatsächlich "Baustelle" jeweils als Text verwendest oder eine ID. Weil datenbanktechnisch sollte man mit einer ID arbeiten.
Sollte es ein Text sein, solltest du dringend das Design unter die Lupe nehmen.

Aber 60000 DS im richtigen Design sollten mit VBA in wenigen Sekunden abgearbeitet sein. Ziemlich sicher viel schneller wie mit SQL.
LG Markus

markusxy, 7. August 2018

#7
EBS17

ebs17

Zusammenhängende Zeitspannen ermitteln

Ziemlich sicher viel schneller wie mit SQL
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Aus einer ungeordneten Datenmenge heraus werden die Schleifen etwas langwieriger und aufwändiger - nur um mit den Gedanken von vorne zu beginnen.

ebs17, 7. August 2018

#8
GUIDO S

Guido S

Danke nochmals für die Hilfe, ich habe nun beide Vorschläge umgesetzt.
Indiziert und anstatt des Textes für die Baustelle eine Nummer vergeben.

Ich schätze mal 3 Sekunden brauchts noch.*top

Guido S, 8. August 2018

#9
MARKUSXY

markusxy

Na bitte, 3 Sekunden ist ja machbar.
Da sieht man was so eine Indexierung wert ist. *mrcool

markusxy, 8. August 2018

#10
EBS17

ebs17

Da sieht man was so eine Indexierung wert ist.
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

... insbesondere wenn die Anweisungen dazu auch diese Indizierung nutzen können (Abfragedesign).
Wenn das Auto in der Garage steht, kann es kein Rennen gewiinnen.

Beispiel? Performance total mies

ebs17, 8. August 2018

#11
EBS17

ebs17

Auch wenn das Thema durch ist, möchte ich noch eine weitere Variante anbieten:
Code:

SELECT
Pers_Nr,
Baustelle,
MIN(Datum) AS BeginnZeitraum,
MAX(Datum) AS EndeZeitraum
FROM
(
SELECT
Pers_Nr,
Baustelle,
Datum,
Datum - QueryIncrement(Datum, 1, Pers_Nr, Baustelle, Datum) AS Diff
FROM
Pers_Baustelle
ORDER BY
Pers_Nr,
Baustelle,
Datum
) AS X
GROUP BY
Pers_Nr,
Baustelle,
Diff
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

QueryIncrement ... erzeugt eine laufende Nummerierung, siehe Grundlagen - SQL ist leicht (10) - Nummerierung in Abfragen

Idee: Die Differenz aus einem fortlaufenden Datum und einer fortlaufenden Nummer ist immer gleich. Daher bildet jede Einzeldifferenz einen zusammenhängenden Zeitraum ab.

ebs17, 8. August 2018

#12
NOUBA

Nouba

Zusammenhängende Zeitspannen ermitteln

\@ebs,

kleine Anmerkung: ein ParamArray-Argument muss mindestens einen Wert aufweisen. Deshalb ist die Verwendung von IsMissing in der Funktion ziemlich sinnfrei.

Nouba, 8. August 2018

#13
EBS17

ebs17

Da möchte ich widersprechen. Ohne Element im ParamArray wird einfach von oben nach unten durchnummeriert, wie es in der Abfrage qryIncrement in der dort hinterlegten DB gelingt.

Nicht zulässig ist die Kombination von Optional und ParamArray in der Funktionsdeklaration, so sagt es auch der verlinkte MS-Text aus.

ebs17, 8. August 2018

#14
NOUBA

Nouba

Akzeptiert.*Smilie

Nouba, 8. August 2018

#15

(Du musst angemeldet oder registriert sein, um eine Antwort erstellen zu können.)

Seite 1 von 2

Thema:

Zusammenhängende Zeitspannen ermitteln

Die Seite wird geladen...

Zusammenhängende Zeitspannen ermitteln - Similar Threads - Zusammenhängende Zeitspannen ermitteln
Zusammenhängende Informationen aus einer Zeile in mehrere Spalten
in Microsoft Excel Hilfe
Zusammenhängende Informationen aus einer Zeile in mehrere Spalten: Guten Tag, hab ein kleines Problem - soll für die Arbeit eine Tabelle erstellen mit Kundendaten. Problem ist, dass die "Datenbank" die ich hierfür erhalten habe, alle Informationen zu allen Kunden...
3Grösste Werte aus nicht zusammenhängenden Bereich addieren
in Microsoft Excel Hilfe
3Grösste Werte aus nicht zusammenhängenden Bereich addieren: Hallo, ich benötige eine Formel, die mir die 3 größten Werte addiert, die aber nicht in einem zusammenhängenden Bereich, sondern an verschiedenen Stellen in einem Tabellenblatt stehen. Vielen Dank
Zusammenhängende Zeiträume in einer Spalte erkennen und Start- sowie Enddatum ermitteln
in Microsoft Excel Hilfe
Zusammenhängende Zeiträume in einer Spalte erkennen und Start- sowie Enddatum ermitteln: Hallo zusammen, irgendwie komme ich einfach mit meinem Problem nicht weiter und hoffe sehr auf Eure Hilfe! Ich habe eine Personaldatei, in der je Zeile ein Urlaubstag steht. Die ersten Spalten...
zusammenhängende leere Zellen zählen
in Microsoft Excel Hilfe
zusammenhängende leere Zellen zählen: Hallo zusammen, ich bin neu in diesem Forum. Ich habe intensiv nach einer Lösung für folgendes Problem gesucht - und versucht bestehende ähnliche Lösungen zu abstrahieren. Leider bisher ohne...
Text zusammenhängend, ohne Leerzeichen
in Microsoft Word Hilfe
Text zusammenhängend, ohne Leerzeichen: Word Text als PDF erhalten. Beim Kopieren und Einfügen wird der Text zusammengeschoben. Alles zusammengeschrieben ohne Abstand. Danke für einen Tipp.
zusammenhängende Daten kennzeichnen
in Microsoft Excel Hilfe
zusammenhängende Daten kennzeichnen: Guten Tag zusammen Trotz intensivem Ausprobieren habe ich für das unten geschilderte Problem keine Lösung bzw. Formel gefunden, die stimmig ist: In einer Liste von einzelnen Datensätzen pro...
Namensmanager Bezug auf NICHT zusammenhängende Daten?
in Microsoft Excel Hilfe
Namensmanager Bezug auf NICHT zusammenhängende Daten?: Hallo zusammen, wie es oben schon steht möchte ich einen Namen vergeben, wo der Bezug sich auf einen nicht zusammenhängenden Bereich bezieht. Name ist Liste 1 und dann schränke ich die...

Anmelden oder registrieren

Suche

Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.

Akzeptieren Weitere Informationen...

Information ausblenden