birdyb
May 07, 2017
view1522
view5
0
Goto Top

Laufende Aufträge nach Uhrzeit auswerten

GermanQuestionDatabasesDevelopment
Hallo zusammen,

ich bräuchte bitte eure Hilfe bei der Zusammenstellung einer SQL-Abfrage. Leider komme ich selbst nicht auf den passenden Kniff.
Gegeben ist eine Tabelle mit Aufträgen. Darin sind unter anderem jeweils als Feld der Auftragsbeginn (BEGINN_Z und BEGINN_D - Also Zeit und Datum) sowie Auftragsende (ENDE_Z und ENDE_D) angegeben.
Gewünscht ist nun eine Auswertung, die über einen bestimmten Zeitraum angibt, wieviele Aufträge zu welcher Uhrzeit liefen, aufgeschlüsselt in Stundenintervalle, also 0-1Uhr, 1-2Uhr, etc.
Da Aufträge auch über Mitternacht hinweg laufen, müsste hier auch noch der Datumssprung berücksichtigt werden.

Irgendwie fehlt mir gerade die Kreativität um das Problem zu lösen.

Hat jemand von euch eine Idee?

Danke für die Hilfe im Voraus!
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 337113

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

Printed on: April 24, 2024 at 11:04 o'clock

5 Comments
Latest comment
Goto Top
Member: em-pie
em-pie May 07, 2017 updated at 15:13:31 (UTC)
Goto Top
Moin,

ohne jetzt konkret ein fertiges Statement ausgearbeitet zu haben, schoss mir folgender Ansatz durch den Kopf:

Die Stundenintervalle bekämest du ja mit einem group by Hour (je nach verwendeter Datenbank entsprechende Syntax einsetzen)
Wenn ich dich richtig verstehe, willst du ja dann wissen, welche Aufträge noch nicht in dieser Stunde erledigt gewesen sind, richtig?

Hier wäre dann vermutlich ein where NOT Hour(Beginn_Z)=Hour(ENDE_Z)

Das Tagesdatum müsste glaube ich irrelevant sein...

€dit:
Es könnte so aussehen (ungetestet)
SELECT
	Beginn_D
	, HOUR(Beginn_Z) AS Stunde
FROM
	YourTable
WHERE
	NOT HOUR(Beginn_Z)=HOUR(Ende_Z)
	AND NOT Beginn_D = Ende_D
GROUP BY
	Beginn_D
	, HOUR(Beginn_Z)

Was hier u.U. aber ein Problem geben könnte ist, dass du in diesem Where-Konstrukt nur Aufträge siehst, die nicht am selben tag begonnen und gleichzeitig beendet wurden. Das müsste man ggf. etwas anders umschreiben; ggf. durch das Kmobinieren von Datum und Uhrzeit mittels eines neuen Feldes vom Typ Timestamp.

Ggf. haben andere ja noch eine elegantere Lösung hier...

Gruß
em-pie
Member: Biber
Biber May 07, 2017 updated at 21:51:07 (UTC)
Goto Top
Moin BirdyB,

ich verstehe die Anforderung etwas anders als em-pie, möchte aber sichergehen, bevor ich hier unnötig Aufwand reinstecke,

Mein Verständnis:
  • In der Tabelle "Auftrag" (oder wie immer die bei euch heisst), gibt es Aufträge mit IDs, Start+Ende-Zeiten
  • die Start- und Ende-Zeiten sind irrwitzigerweise nicht als Timestamps gespeichert, sondern jeweils als ein Feld Datum und ein Feld Uhrzeit

Wenn da nun 2 Aufträge drinstünden
Auftrag; Beginn_D; Beginn_z;Ende_D;Ende_Z
4711; 05.05.2017; 17:11;05.05.2017;20:14;
4712; 05.05;2017;22:56;06.05:2017;00:17

Dann willst du eine Darstellung
05.05.2017; Von 00-01 ; 0
05.05.2017; Von 01-02 ; 0
05.05.2017; Von 17-18 ; 1 ( Summe 1, denn hier lief Auftrag 4711)
05.05.2017; Von 18-19 ; 1 ( Summe 1, denn hier lief Auftrag 4711)
05.05.2017; Von 19-20 ; 1 ( Summe 1, denn hier lief Auftrag 4711)
05.05.2017; Von 20-21 ; 1 ( Summe 1, denn hier lief Auftrag 4711)
05.05.2017; Von 21-22 ; 0
05.05.2017; Von 22-23 ; 1 ( Summe 1, denn hier lief Auftrag 4712)
05.05.2017; Von 23-00 ; 1 ( Summe 1, denn hier lief Auftrag 4712)
06.05.2017; Von 00-01 ; 1 ( Summe 1, denn hier lief Auftrag 4712)
06.05.2017 Von 01-02; 0 .....

Habe ich das so richtig interpretiert?

Wenn ja, dann ist erstmal eine lückenlose Timeline zu bauen (geht im SELECT durchaus, macht es aber nicht lesbarer).

Wobei die Frage wäre, was denn der "Zeitraum" ist, der maximal betrachtet werden soll - eher die letzten 2 Tage oder die letzten 30 oder das letzte Quartal...

Blöd ist nur die Timestamp-Speicherung als Datum und Uhrzeit in separaten Feldern... wer ist denn bei euch auf diesen Bolzen gekommen?

Gib doch mal ein paar (realitätsnahe) Beispielsätze rüber. Die "Auftrags-IDs" können gerne 4711, 4711 usw heissen.

Grüße
Biber
Member: ukulele-7
ukulele-7 May 08, 2017 at 07:16:36 (UTC)
Goto Top
Über welchen SQL Server reden wir denn?
Member: BirdyB
BirdyB May 08, 2017 at 08:50:38 (UTC)
Goto Top
Hallo zusammen,

@ukulele-7: Wir reden über SQL Anywhere 16

@Biber:

Zitat von @Biber:
Mein Verständnis:
  • In der Tabelle "Auftrag" (oder wie immer die bei euch heisst), gibt es Aufträge mit IDs, Start+Ende-Zeiten
  • die Start- und Ende-Zeiten sind irrwitzigerweise nicht als Timestamps gespeichert, sondern jeweils als ein Feld Datum und ein Feld Uhrzeit
Das ist so korrekt. Warum es zwei Felder sind, müsste man den Entwickler der Software dahinter fragen. Allerdings kann man die beiden Felder einfach aufaddieren, dann erhält man den Timestamp...

Wenn da nun 2 Aufträge drinstünden
Auftrag; Beginn_D; Beginn_z;Ende_D;Ende_Z
4711; 05.05.2017; 17:11;05.05.2017;20:14;
4712; 05.05;2017;22:56;06.05:2017;00:17

Dann willst du eine Darstellung
05.05.2017; Von 00-01 ; 0
05.05.2017; Von 01-02 ; 0
05.05.2017; Von 17-18 ; 1 ( Summe 1, denn hier lief Auftrag 4711)
05.05.2017; Von 18-19 ; 1 ( Summe 1, denn hier lief Auftrag 4711)
05.05.2017; Von 19-20 ; 1 ( Summe 1, denn hier lief Auftrag 4711)
05.05.2017; Von 20-21 ; 1 ( Summe 1, denn hier lief Auftrag 4711)
05.05.2017; Von 21-22 ; 0
05.05.2017; Von 22-23 ; 1 ( Summe 1, denn hier lief Auftrag 4712)
05.05.2017; Von 23-00 ; 1 ( Summe 1, denn hier lief Auftrag 4712)
06.05.2017; Von 00-01 ; 1 ( Summe 1, denn hier lief Auftrag 4712)
06.05.2017 Von 01-02; 0 .....

Habe ich das so richtig interpretiert?

Im Prinzip ja... Zusätzlich bräuchte ich das allerdings über den gesamten Zeitraum aggregiert.
Will sagen: Ich filtere den View über einen Zeitraum, z.B. 01.04.-31.04. und dann bräuchte ich die Ausgabe:
0-1 Uhr: 25
1-2 Uhr: 20,
...
Wobei ich das dann auch direkt in Crystal Reports zusammenführen kann(denke ich).

Wenn ja, dann ist erstmal eine lückenlose Timeline zu bauen (geht im SELECT durchaus, macht es aber nicht lesbarer).

Wobei die Frage wäre, was denn der "Zeitraum" ist, der maximal betrachtet werden soll - eher die letzten 2 Tage oder die letzten 30 oder das letzte Quartal...
Es geht schon eher in Richtung des Quartals. Es geht konkret um die Personalvorhaltung nachts und am Wochenende.
Blöd ist nur die Timestamp-Speicherung als Datum und Uhrzeit in separaten Feldern... wer ist denn bei euch auf diesen Bolzen gekommen?
Bei uns keiner... Frag den Hersteller des ERP-Systems...
Gib doch mal ein paar (realitätsnahe) Beispielsätze rüber. Die "Auftrags-IDs" können gerne 4711, 4711 usw heissen.
Auftrags-ID; Beginn_D; Beginn_Z; Ende_D; Ende_Z;Auftragsart
4711; 2017-04-01;22:55:00;2017-04-02;00:45:00;RTW
4712; 2017-04-02;11:31:00;2017-04-02;12:48:00;ITW
4713; 2017-04-02;12:11:00;2017-04-02;13:21:00;RTW
4714; 2017-04-02;12:44:00;2017-04-02;14:39:00;ITW

So könnte es in etwa aussehen. In den Datensätzen steht noch viel mehr drin, aber das ist für die Fragestellung hier jetzt nicht relevant...
Member: ukulele-7
ukulele-7 May 08, 2017 at 14:24:30 (UTC)
Goto Top
So könnte es gehen (ich arbeite mit einer lokalen MSSQL DB):
WITH test(AuftragsID,Beginn_D,Beginn_Z,Ende_D,Ende_Z,Auftragsart) AS (
	SELECT 4711,'2017-04-01','22:55:00','2017-04-02','00:45:00','RTW' UNION ALL  
	SELECT 4712,'2017-04-02','11:31:00','2017-04-02','12:48:00','ITW' UNION ALL  
	SELECT 4713,'2017-04-02','12:11:00','2017-04-02','13:21:00','RTW' UNION ALL  
	SELECT 4714,'2017-04-02','12:44:00','2017-04-02','14:39:00','ITW'  
	), raster(datumzeit) AS (
	SELECT	convert(DATETIME,'2017-04-01 00:00:00.000',120)  
	UNION ALL
	SELECT	dateadd(hour,1,datumzeit)
	FROM	raster
	WHERE	dateadd(hour,1,datumzeit) < convert(DATETIME,'2017-05-01 00:00:00.000',120)  
	)
SELECT	r.datumzeit,
		sum(CASE WHEN t.AuftragsID IS NULL THEN 0 ELSE 1 END) AS anzahl
FROM	raster r
LEFT JOIN test t
ON		r.datumzeit BETWEEN convert(DATETIME,Beginn_D,120) + Beginn_Z AND convert(DATETIME,Ende_D,120) + Ende_Z
GROUP BY r.datumzeit
ORDER BY r.datumzeit
OPTION (MAXRECURSION 1000);
Das ist natürlich anpassungsfähig. Die Tabelle "test" kann aus dem WITH Teil gestrichen und durch richtige Daten ersetzt werden. Bei 24 Datensätzen pro Tag sollte man natürlich den Zeitraum nicht zu groß wählen. Vielleicht schließt man auch noch Wochenenden aus etc.
GermanQuestionDatabasesDevelopment
Hotly discussed
gleixnerdCheck of ZFW Firewallgleixnerd - 5 CommentsjstrickerWireguard VPN on UDM Pro behind Fritzbox - Handshake did not completejstricker - 3 CommentsAlexWishaHow to set up and configure a Linux GRE tunnelAlexWisha - 3 Comments