Top-Themen

Aktuelle Themen (A bis Z)

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

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

Frage Entwicklung Datenbanken

Mitglied: c0nsp1r4cy

c0nsp1r4cy (Level 1) - Jetzt verbinden

07.12.2017 um 11:55 Uhr, 156 Aufrufe, 9 Kommentare

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

Vielen Dank vorab!
Mitglied: ukulele-7
07.12.2017 um 12:07 Uhr
Ist das Absicht bzw. kann das vorkommen das sich die Auftragsbearbeitungszeiten überschneiden wie in deinem Beispiel zwischen 11:20 Uhr und 11:45 Uhr?
Bitte warten ..
Mitglied: Grinskeks
07.12.2017 um 12:19 Uhr
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


Gruss
Grinskeks
Bitte warten ..
Mitglied: ukulele-7
07.12.2017 um 12:32 Uhr
Es sei denn er will auch Zeiträume innerhalb der Anwesenheit aber zwischen den Auftragsbearbeitungszeiten entsprechend ausgeben.
Bitte warten ..
Mitglied: c0nsp1r4cy
07.12.2017 um 12:49 Uhr
Upps, das war ein Versehen. Dieser Fall kann nicht vorkommen.
Bitte warten ..
Mitglied: c0nsp1r4cy
07.12.2017 um 13:02 Uhr
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.
Bitte warten ..
Mitglied: ukulele-7
LÖSUNG 07.12.2017 um 14:44 Uhr
01.
WITH anwesenheit(PersNr,von,bis) AS ( 
02.
SELECT 'MA1','2017-11-17 07:15:00.000','2017-11-17 15:45:00.000' 
03.
), auftragszeiten(PersNr,von,bis,AuftragsNr) AS ( 
04.
SELECT 'MA1','2017-11-17 08:20:00.000','2017-11-17 11:30:00.000',1122 UNION ALL 
05.
SELECT 'MA1','2017-11-17 11:45:00.000','2017-11-17 13:15:00.000',2233 UNION ALL 
06.
SELECT 'MA1','2017-11-17 13:30:00.000','2017-11-17 15:15:00.000',3344 
07.
), az2(zeile,zeileinvers,PersNr,von,bis,AuftragsNr) AS ( 
08.
SELECT	ROW_NUMBER() OVER (PARTITION BY PersNr ORDER BY von), 
09.
		ROW_NUMBER() OVER (PARTITION BY PersNr ORDER BY von DESC), 
10.
		PersNr,von,bis,AuftragsNr 
11.
FROM	auftragszeiten 
12.
13.
SELECT	t.* 
14.
FROM	( 
15.
 
16.
-- Erste Leerlaufzeit 
17.
SELECT	anwesenheit.PersNr, 
18.
		anwesenheit.von, 
19.
		az2.von AS bis, 
20.
		NULL AS AuftragsNr 
21.
FROM	anwesenheit 
22.
INNER JOIN az2 
23.
ON		anwesenheit.PersNr = az2.PersNr 
24.
AND		convert(DATE,anwesenheit.von) = convert(DATE,az2.von) 
25.
AND		az2.zeile = 1 
26.
WHERE	anwesenheit.von < az2.von 
27.
UNION ALL 
28.
-- Letzte Leerlaufzeit 
29.
SELECT	anwesenheit.PersNr, 
30.
		az2.bis AS von, 
31.
		anwesenheit.bis, 
32.
		NULL AS AuftragsNr 
33.
FROM	anwesenheit 
34.
INNER JOIN az2 
35.
ON		anwesenheit.PersNr = az2.PersNr 
36.
AND		convert(DATE,anwesenheit.von) = convert(DATE,az2.von) 
37.
AND		az2.zeileinvers = 1 
38.
WHERE	az2.bis < anwesenheit.bis 
39.
UNION ALL 
40.
-- Übrige Leerlaufzeit(en) 
41.
SELECT	az3.PersNr, 
42.
		az3.bis AS von, 
43.
		az4.von AS bis, 
44.
		NULL AS AuftragsNr 
45.
FROM	az2 az3 
46.
INNER JOIN az2 az4 
47.
ON		az3.zeile + 1 = az4.zeile 
48.
WHERE	az3.bis < az4.von 
49.
UNION ALL 
50.
SELECT	PersNr,von,bis,AuftragsNr 
51.
FROM	auftragszeiten 
52.
 
53.
		) t 
54.
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.
Bitte warten ..
Mitglied: c0nsp1r4cy
07.12.2017 um 15:10 Uhr
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
Bitte warten ..
Mitglied: c0nsp1r4cy
08.12.2017 um 07:23 Uhr
Guten Morgen,

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

Vielen Dank!
Bitte warten ..
Mitglied: ukulele-7
08.12.2017 um 08:49 Uhr
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.
Bitte warten ..
Ähnliche Inhalte
Entwicklung
SQL Abfrage Zeit: JETZT - 30 Sekunden
gelöst Frage von Andinistrator1Entwicklung17 Kommentare

Hallo, kann mir jemand bitte gerade auf die Sprünge helfen, am Beispiel. Auftragstabelle: Welche Aufträge sind in den letzten ...

Windows Server
Neuer MS-SQL-Server
gelöst Frage von tingelWindows Server1 Kommentar

Hallo, Ich bin gerade dabei, mir Gedanken zu machen, wie ich am besten unseren SQL-Server 2008 SP3 auf eine ...

Windows Server
MS SQL Login Problem
gelöst Frage von theoberlinWindows Server26 Kommentare

Hallo zusammen, Umgebung ist 2012r2 Domäne und MS SQL Express 2014 auf VM. ich habe folgende 2 Probleme: 1. ...

Switche und Hubs
Abfragetimeout MS SQL Server
gelöst Frage von docteurbushSwitche und Hubs3 Kommentare

Ich habe mal eine Frage: Folgendes Szenario -> WebApplikation (sowohl in PHP als auch ASP Classic), virtueller WebServer, physischer ...

Neue Wissensbeiträge
Verschlüsselung & Zertifikate

Die Hölle friert ein weiteres Stück zu: Microsoft integriert OpenSSH in Windows

Information von ticuta1 vor 2 StundenVerschlüsselung & Zertifikate

Interessant SSH-Kommando in CMD.exe und PowerShell

Apple

IOS 11.2.1 stopft HomeKit-Remote-Lücke

Tipp von BassFishFox vor 1 TagApple

Das Update für iPhone, iPad und Apple TV soll die Fernsteuerung von Smart-Home-Geräten wieder in vollem Umfang ermöglichen. Apple ...

Windows 10

Windows 10 v1709 EN murkst bei den Regionseinstellungen

Tipp von DerWoWusste vor 1 TagWindows 10

Dieser kurze Tipp richtet sich an den kleinen Personenkreis, der Win10 v1709 EN-US frisch installiert und dabei die englische ...

Webbrowser

Kein Ton bei Firefox Quantum über RDP

Tipp von Moddry vor 1 TagWebbrowser

Hallo Kollegen! Hatte das Problem, dass der neue Firefox bei mir auf der Kiste keinen Ton hat, wenn ich ...

Heiß diskutierte Inhalte
Windows Server
RODC kann nicht aus Domäne entfernt werden
Frage von NilsvLehnWindows Server19 Kommentare

HAllo, ich arbeite in einem Universitätsnetzwerk mit 3 Standorten. Die Standorte haben alle ein ESXi Cluster und auf diesen ...

Hardware
Kein Bild mit nur einer bestimmten Grafikkarten - Mainboard Konfiguration
gelöst Frage von bestelittHardware18 Kommentare

Hallo zusammen, ich hatte schon einmal eine ähnliche Frage gestellt. Damals hatte ich genau das gleiche Problem. Allerdings lies ...

Netzwerkmanagement
Mehrere Netzwerkadapter in einem PC zu einem Switch zusammenfügen
Frage von prodriveNetzwerkmanagement17 Kommentare

Hallo zusammen Vorweg, ich konnte schon einige IT-Probleme mit Hilfe dieses Forums lösen. Wirklich klasse hier! Doch für das ...

Hardware
Links klick bei Maus funktioniert nicht
gelöst Frage von Pablu23Hardware16 Kommentare

Hallo erstmal. Ich habe ein Problem mit meiner relativ alten maus jedoch denke ich nicht das es an der ...