c0nsp1r4cy
Goto Top

MS SQL - Zeiten(TabA - Anwesenheit) mit Zeiten(TabB - Auftragszeit) verrechnen und chronologisch darstellen

Hallo allerseits,

ich habe eine knifflige Angelegenheit in MS SQL zu lösen:

Anwesenheits(Tabelle A)- und Auftragszeiten (Tabelle B) sollen ineinander verwoben werden.

Tabelle A:
- PersNr
- Datum/Zeit von (bspw. 17.11.2017 07:15:00)
- Datum/Zeit bis (bspw. 17.11.2017 15:45:00)

Tabelle B:
- PersNr
- Datum/Zeit von
- Datum/Zeit bis
- AuftragsNr

Kombinieren wir nun alle Datensätze beider Tabellen für einen Tag und Mitarbeiter und stellen diese dar, sieht das folgendermaßen aus:
________________________________________________________________________________________________________
PersNr | Datum/Zeit von | Datum/Zeit bis | AuftragsNr
MA1 | 17.11.2017 07:15:00 | 17.11.2017 15:45:00 | NULL (das ist der Anwesenheitseintrag von Tab A)
MA1 | 17.11.2017 08:20:00 | 17.11.2017 11:45:00 | 1122 (Auftragsbearbeitungszeiten aus Tab B)
MA1 | 17.11.2017 11:20:00 | 17.11.2017 13:30:00 | 2233 (Auftragsbearbeitungszeiten aus Tab B)
MA1 | 17.11.2017 13:30:00 | 17.11.2017 15:15:00 | 3344 (Auftragsbearbeitungszeiten aus Tab B)
________________________________________________________________________________________________________

Die ich möchte die Ergebnisse allerdings folgendermaßen aufarbeiten:
________________________________________________________________________________________________________
PersNr | Datum/Zeit von | Datum/Zeit bis | AuftragsNr
MA1 | 17.11.2017 07:15:00 | 17.11.2017 08:20:00 | NULL (Eintrag bis zur Startzeit des ersten Auftrages)
MA1 | 17.11.2017 08:20:00 | 17.11.2017 11:45:00 | 1122 (Auftragsbearbeitungszeiten aus Tab B)
MA1 | 17.11.2017 11:20:00 | 17.11.2017 13:30:00 | 2233
MA1 | 17.11.2017 13:30:00 | 17.11.2017 15:15:00 | 3344
MA1 | 17.11.2017 15:15:00 | 17.11.2017 15:45:00 | NULL (zusätzlicher Eintrag)
________________________________________________________________________________________________________

Ich möchte also für jeden Zeitraum einen chronologisch eingereihten Datensatz. Die Liste soll sehr übersichtlich präsentieren, wann an Aufträgen gearbeitet wird und wann nicht.

Diese Daten präsentiere ich dann in SAP B1. Aus Performancegründen möchte ich diese Verarbeitung bereits in SQL vornehmen.

Vielleicht hat ja jemand eine Idee face-smile

Vielen Dank vorab!

Content-Key: 357400

Url: https://administrator.de/contentid/357400

Ausgedruckt am: 28.03.2024 um 08:03 Uhr

Mitglied: ukulele-7
ukulele-7 07.12.2017 um 12:07:47 Uhr
Goto Top
Ist das Absicht bzw. kann das vorkommen das sich die Auftragsbearbeitungszeiten überschneiden wie in deinem Beispiel zwischen 11:20 Uhr und 11:45 Uhr?
Mitglied: Grinskeks
Grinskeks 07.12.2017 um 12:19:42 Uhr
Goto Top
Moin,

prinizpiell stehen bereits alle Infos in Tabelle B bis auf den Beginn und das Ende der Arbeitsaufnahme.

Also benötigst du nur eine Query, mit der jeweils ein Datensatz vom Beginn der Arbeitsaufnahme bis zum Beginn des ersten Auftrages und vom Ende des letzten Auftrags bis zum Ende der Arbeitsaufnahme mit angelegt wird (sofern vorhanden und es sich nicht überschneidet.)

Konzept: Über SQL ein UNION ALL der Tabelle B mit einem Join aus Tabelle A und Tabelle B verbinden.
Oder sequentiell in einer Stored Procedure.

Das Endergebnis zu präsentieren, würde den Lerneffekt drastisch reduzieren.
Bei konkreten Fragen stehe ich gerne zur Verfügung face-wink


Gruss
Grinskeks
Mitglied: ukulele-7
ukulele-7 07.12.2017 um 12:32:11 Uhr
Goto Top
Es sei denn er will auch Zeiträume innerhalb der Anwesenheit aber zwischen den Auftragsbearbeitungszeiten entsprechend ausgeben.
Mitglied: c0nsp1r4cy
c0nsp1r4cy 07.12.2017 um 12:49:29 Uhr
Goto Top
Upps, das war ein Versehen. Dieser Fall kann nicht vorkommen.
Mitglied: c0nsp1r4cy
c0nsp1r4cy 07.12.2017 um 13:02:37 Uhr
Goto Top
Zitat von @Grinskeks:
prinizpiell stehen bereits alle Infos in Tabelle B bis auf den Beginn und das Ende der Arbeitsaufnahme.

Den Einwand von Ukulele, sofern ich diesen richtig verstehe, trifft es.

Es könnte nun sein, dass ein Mitarbeiter während seiner Arbeitszeit, zwischen der Bearbeitung zweier Aufträge, einen Leerlauf hat.

Ich bin kein versierter SQL-Künstler, sondern eher gut darin mich durchzubeißen. D.h. es ist nicht so, dass ich keine Ideen hätte, aber vielleicht kann ich mir ja ausnahmsweise stundenlanges Rumprobieren sparen.

Im Prinzip muss ich TabA und TabB mit Union kombinieren und dann die chronologischen Ergebnisse Stück für Stück miteinander vergleichen und ggf. einen Anfang und Endzeitpunkt berechnen... irgendwie in diese Richtung. Aber in meinem Kopf ergeben sich direkt schon einige Baustellen, die ich nicht ohne Weiteres lösen kann.
Mitglied: ukulele-7
Lösung ukulele-7 07.12.2017 um 14:44:24 Uhr
Goto Top
WITH anwesenheit(PersNr,von,bis) AS (
SELECT 'MA1','2017-11-17 07:15:00.000','2017-11-17 15:45:00.000'  
), auftragszeiten(PersNr,von,bis,AuftragsNr) AS (
SELECT 'MA1','2017-11-17 08:20:00.000','2017-11-17 11:30:00.000',1122 UNION ALL  
SELECT 'MA1','2017-11-17 11:45:00.000','2017-11-17 13:15:00.000',2233 UNION ALL  
SELECT 'MA1','2017-11-17 13:30:00.000','2017-11-17 15:15:00.000',3344  
), az2(zeile,zeileinvers,PersNr,von,bis,AuftragsNr) AS (
SELECT	ROW_NUMBER() OVER (PARTITION BY PersNr ORDER BY von),
		ROW_NUMBER() OVER (PARTITION BY PersNr ORDER BY von DESC),
		PersNr,von,bis,AuftragsNr
FROM	auftragszeiten
)
SELECT	t.*
FROM	(

-- Erste Leerlaufzeit
SELECT	anwesenheit.PersNr,
		anwesenheit.von,
		az2.von AS bis,
		NULL AS AuftragsNr
FROM	anwesenheit
INNER JOIN az2
ON		anwesenheit.PersNr = az2.PersNr
AND		convert(DATE,anwesenheit.von) = convert(DATE,az2.von)
AND		az2.zeile = 1
WHERE	anwesenheit.von < az2.von
UNION ALL
-- Letzte Leerlaufzeit
SELECT	anwesenheit.PersNr,
		az2.bis AS von,
		anwesenheit.bis,
		NULL AS AuftragsNr
FROM	anwesenheit
INNER JOIN az2
ON		anwesenheit.PersNr = az2.PersNr
AND		convert(DATE,anwesenheit.von) = convert(DATE,az2.von)
AND		az2.zeileinvers = 1
WHERE	az2.bis < anwesenheit.bis
UNION ALL
-- Übrige Leerlaufzeit(en)
SELECT	az3.PersNr,
		az3.bis AS von,
		az4.von AS bis,
		NULL AS AuftragsNr
FROM	az2 az3
INNER JOIN az2 az4
ON		az3.zeile + 1 = az4.zeile
WHERE	az3.bis < az4.von
UNION ALL
SELECT	PersNr,von,bis,AuftragsNr
FROM	auftragszeiten

		) t
ORDER BY 1,2,3,4
Das wäre eine Lösung mit UNION und wenn wirklich sichergestellt ist das es keine Überschneidungen geben kann wüsste ich nicht das dagegen sprechen sollte.

Es gäbe sicherlich noch die Möglichkeit rekursiv mit CTE das zu durchlaufen aber es gibt meistens mehrere Wege.
Mitglied: c0nsp1r4cy
c0nsp1r4cy 07.12.2017 um 15:10:25 Uhr
Goto Top
Heiliger Bimm Bamm ;),

vielen Dank vorab - ich brauch ein wenig Zeit um diese Lösung zu verstehen.

Hab nicht erwartet, dass so viel Code hierbei herauskommt face-smile
Mitglied: c0nsp1r4cy
c0nsp1r4cy 08.12.2017 um 07:23:20 Uhr
Goto Top
Guten Morgen,

klappt wunderbar und gibt mir eine Inspiration, die in eine für mich völlig neue Richtung zeigt.

Vielen Dank!
Mitglied: ukulele-7
ukulele-7 08.12.2017 um 08:49:08 Uhr
Goto Top
Die ersten beiden Tabellen im WITH sind natürlich nur für meinen Test und die Tabelle t nur für die Sortierung, die kannst du in einer Sicht für SAP auch ganz raus lassen. Dann ist es eigentlich nur noch eine Tabelle mit der Sortierung / Nummerierung und vier Selects.