SQL-Statement: "zusammenpassende" Datensätze in EINER Zeile ausgeben

[juergen]

Hallo zusammen,

ich häng grad an einer SQL-Abfrage fest.
Folgende Tabelle existiert:

ProjektNr	Datum	Buchungsart	Uhrzeit	Zeit
4711	10.05.2019	B	7:00
4711	10.05.2019	E	10:00	3:00
4712	10.05.2019	B	10:00
4712	10.05.2019	E	12:00	2:00
4711	10.05.2019	B	12:00
4711	10.05.2019	E	17:00	5:00

Jetzt muss ich für jedes "Buchungspärchen" eines Projektes (B= Projekt-Beginn, E = Projekt-Ende) EINE Zeile ausgeben.

Für o.g. Tabelle müsste das Ergebnis so ausschauen:

ProjektNr	Datum	Projekt-Beginn	Projekt-Ende	Zeit
4711	10.05.2019	7:00	10:00	3:00
4712	10.05.2019	10:00	12:00	2:00
4711	10.05.2019	12:00	17:00	5:00

Die Buchungen sind in der Tabelle NICHT chronologisch oder nach Projekten sortiert.
Die Kunst ist es jetzt die richtigen Buchungspärchen zu finden und in EINE Ausgabezeile zusammen zubringen.

Für Hilfestellung schon mal vielen Dank vorab!

[Delphi.Narium]

Poste bitte noch das SQL, mit dem Du die bisherige Ausgabe erstellst.

Ggfls. kann man das dann entsprechend umbauen, so wird das doch sehr abstrakt und eher eine Raterei, auch wenn eine Lösung ggfls. sehr einfach möglich sein könnte.

Grob in etwa sowas:

SQL-Code:

select ProjektNr, Datum, Buchungsart, Min(Uhrzeit) as Projekt-Beginn, Max(Uhrzeit) as Projekt-Ende, Max(Zeit) as Zeit from (
  -- Das SQL Deiner Abfrage für das bisherige Ergebnis hier einfügen
  select ProjektNr, Datum, Buchungsart, Uhrzeit, Zeit from tabelle where ...
)
group by ProjektNr, Datum, Buchungsart
order by ProjektNr, Datum, Buchungsart

[Billa]

Ich kenne MSSQL nicht. Würde aber in Firebird sowas machen:

select
A.prj, A.dat, A.tim,
B.tim, b.dur
from ttable A
left join ttable b on
(a.prj = b.prj) and (b.typ = 'E') and (B.tim > a.tim)
left join ttable c on
(c.prj = a.prj) and (c.typ = 'E') and (C.tim > a.tim) and (c.tim < b.tim)
where
a.typ = 'B' and
c.prj is null
order by
a.TIM

Es gibt vermutlich Effizienteres, aber da kannst Du schon mal ansetzen

[Rainbow6]

Hallo,

also solche Späße habe ich mit SQL Server kürzlich erst gemacht - hier mal der etwas simplifizierte SQL Code.

Im Prinzip musst du die Tabelle mit sich selbst JOINen - ich mache das der Übersichtlichkeit immer mit WITH.

Code:

WITH 
B AS (SELECT * FROM Tabelle WHERE Buchungsart = 'B'),
E AS (SELECT * FROM Tabelle WHERE Buchungsart = 'E')

SELECT
   B.ProjektNr,
   B.Datum,
   B.Uhrzeit,
   E.Uhrzeit,
   E.Zeit,

FROM B

LEFT JOIN E
   ON B.ProjektNr = E.ProjektNr 
   AND DATEADD(hour, E.Zeiteit * (-1), (CONVERT(datetime, E.Datum) + CONVERT(datetime, E.Uhrzeit)))
       = (CONVERT(datetime, B.Datum) + CONVERT(datetime, B.Uhrzeit))

Der spannende Teil ist der letzte AND im LEFT JOIN E - dort rechne ich quasi von der Ende-Zeit her die Anfangszeit aus, und JOINe die passende Zeile dazu. Wichtig ist, dass du dir im Prinzip aber aus Uhrzeit und Datum ein DateTime machen must, denn sonst klappt das nicht richtig mit DATEADD. Stell dir vor du hast eine B-Zeile um 23:00 Uhr Gestern, und eine E-Zeile dazu um 1:00 Uhr Heute.

Ich hoffe das hilft dir ein wenig. Hab das jetzt ungetestet zusammengehackt, weil ich deine Tabelle nicht habe, aber so in der Art läuft das bei mir bei einem sehr ähnlichen Anwendungsfall.

Grüße
Daniel

[juergen]

Vielen Dank !!
Ich konnte meine Anforderung mit euren Denkansätzen nun erfolgreich umsetzen UND habe wieder was gelernt!

[jobo]

Die Lösung von Rainbow gefällt mir.

Da ich den Ursprung der Daten und Deine Detailanforderungen nicht kenne noch ein allgemeiner Tipp:
Prüfe doch mal, ob die Pärchen zusammenpassen. Z.B. sind es im Ergebnis halb soviel Datensätze wie in der Ausgangstabelle? Oder mehr? oder weniger?
Sind es wirklich alles Pärchen?

Es gibt ja wahrscheinlich 1 Millionen Zeiterfassungssysteme und dergleichen und ich würde den allermeisten nicht über den Weg trauen.

Mein persönlicher Favorit ist bis jetzt eines, das auch in Monaten mit 31 Tagen, nur 30 Tage angeboten hat.

[Rainbow6]

Danke

Tatsächlich sind in dieser Konstellation die B-Zeilen eigentlich unnötig.

Man kann die Beginn-Zeit ja aus der Ende-Zeit und der Dauer ganz einfach errechnen.

Damit würde man sogar den LEFT JOIN sparen - angefangene und nicht beendete Arbeitszyklen an einem Projekt entfallen dann halt ersatzlos. Aber nur aus der Anfangszeit lässt sich sowieso nichts ernitteln.

Diese angefangenen aber nicht beendeten Zyklen könnte man in einer Checkliste mit NOT IN selektieren. Die Fehler dann entsprechend korrigieren und alles ist gut.

Zeiterfassung ist ein heikles Thema - aber nach dem EU-Urteil wird das viele Programmierer in den nächsten Jahren einholen, weil jeder 2. Kunde da was nachholen muss, wenn tatsächlich eine „maschinelle Zeiterfassung“ per Gesetz vorgeschrieben wird.

Grüße aus Franken
Daniel

[Delphi.Narium]

Zitat von jobo:

Mein persönlicher Favorit ist bis jetzt eines, das auch in Monaten mit 31 Tagen, nur 30 Tage angeboten hat.

Arbeitnehmerfreundlich, sieben Urlaubstage extra im Jahr

[jobo]

Zitat von Rainbow6:

Zeiterfassung ist ein heikles Thema - aber nach dem EU-Urteil wird das viele Programmierer in den nächsten Jahren einholen, weil jeder 2. Kunde da was nachholen muss, wenn tatsächlich eine „maschinelle Zeiterfassung“ per Gesetz vorgeschrieben wird.

Naja, ist ja opffen wie das alles umzusetzen ist. Der Herr Minister hat ja gerade noch gesagt, da macht er erstmal kein Gesetz draus.

@Arbeitnehmerfreundlich:
Könnte man meinen. Es wurden zum Ausgleich der Schwächen von einzelnen Tools insgesamt 4 Zeiterfassungssysteme (Dokumentation, nicht automatisiert) eingesetzt. Was da an Tipparbeit reinging, möchte ich nicht wissen. Diese volle Wucht der Datenerfassung betraf zum Glück nur einige Dutzend Freelancer.

[Delphi.Narium]

Das Arbeitnehmerfreundlich wahr ironisch gemeint, eine derart unkorrekte Berechnung ist ein absolutes NoGo.