Wie macht man: 1 Datensatz mit 3 Tagen Dauer => 3 Datensätze mit 1 Tag Dauer

Question

Ich brauche dringend und schnell Hilfe bei einer Umwandlung von Datensätzen. Ob dies mit einer Tabellenwertfunktion, einer besonderen gespeicherten Prozedur, o.a. funktioniert ist dabei egal. Bitte sendet auch halbfertige Antworten, da die Zeit so drängt.

Folgendes Problem: Es soll umgewandelt werden (A) zu (B).

(A)

Art Mitarbeiter Start Ende

Urlaub Maier 07.01.2014 00:00 09.01.2014 00:00

Urlaub Schmidt 27.02.2014 00:00 02.03.2014 00:00

krank Herbst 11.01.2014 13:00 13.01.2014 00:00

zu (B)

Art Mitarbeiter Start Ende

Urlaub Maier 07.01.2014 00:00 07.01.2014 00:00

Urlaub Maier 08.01.2014 00:00 08.01.2014 00:00

Urlaub Maier 09.01.2014 00:00 09.01.2014 00:00

Urlaub Schmidt 27.02.2014 00:00 01.03.2014 00:00

Urlaub Schmidt 28.02.2014 00:00 28.02.2014 00:00

Urlaub Schmidt 01.03.2014 00:00 01.03.2014 00:00

Urlaub Schmidt 02.03.2014 00:00 02.03.2014 00:00

krank Herbst 11.01.2014 13:00 13.01.2014 00:00

krank Herbst 11.01.2014 00:00 13.01.2014 00:00

krank Herbst 11.01.2014 00:00 13.01.2014 00:00

Wie erstellen ich also aus einem Eintrag zu 3 Tagen Urlaub die entsprechenden 3 Einträge zu je 1 Tag Urlaub (mit korrektem Datum).

Die Datumsangaben sind datetime-Typen, können aber natürlich im Rahmen einer Sicht o.ä. auch konvertiert werden. Nur die krank-Einträge können eine Uhrzeit enthalten – und dies nur für den Anfangstag. Weitere Spalten zur Auslagerung von Zeit sind natürlich machbar. Ausgewertet wird später in Excel per SVERWEIS, daher werden einzelne Tageseinträge gebraucht. (Neben den Urlaubs- und Krank-Einträgen existieren sehr viele Tageseinträge für Arbeit oder auch Überstundenabbau ..)

Falls dies in NICHT möglich ist mit SQL, aber jemandem eine Lösung in Access oder Excel einfällt, wäre das Problem auch gelöst.

Bitte sendet auch halbfertige Antworten, da die Zeit so drängt.

Answer 1

Hallo Hubert,

naja, sinnvoller wäre die Auswertung gleich mit SQL zu machen, anstatt EXCEL zu quälen, aber sei es drum:

DECLARE @Mitarbeiter TABLE (
	Art varchar(20) NOT NULL,
	Mitarbeiter varchar(40) NOT NULL,
	Start datetime NOT NULL,
	Ende datetime NOT NULL);
	
INSERT INTO @Mitarbeiter(Art, Mitarbeiter, Start, Ende)
VALUES ('Urlaub', 'Maier', '07.01.2014 00:00', '09.01.2014 00:00'),
	('Urlaub', 'Schmidt', '27.02.2014 00:00', '02.03.2014 00:00'),
	('krank', 'Herbst', '11.01.2014 13:00', '13.01.2014 00:00'),
	('krank', 'Schulze', '18.01.2014 13:00', '18.01.2014 00:00');

WITH MitarbeiterDatum AS 
(
	SELECT Art, Mitarbeiter, Start, Ende, 0 AS Tag  FROM @Mitarbeiter
	UNION ALL
	SELECT Art, Mitarbeiter, CAST(DATEADD(dd, 1, CAST(Start AS date)) AS datetime), Ende, Tag + 1 FROM MitarbeiterDatum
	WHERE DATEADD(dd, 1, CAST(Start AS date)) <= CAST(Ende AS date)
)
SELECT * FROM MitarbeiterDatum 
ORDER BY Mitarbeiter, Art, Start, Ende
OPTION (MAXRECURSION 0); -- wenn mehr als 100 Tage möglich
GO

Gruß Elmar

Answer 2

SEHR Schön! Vielen Dank für die prompte Antwort. Ich habe dies gleich leicht abgewandelt ausprobiert - und es funktioniert prima. Nun ist nur noch die korrekte Übernahme der vielen Datensätze statt der Beispieldaten sowie eine Spaltenenerweiterung nötig - und dann sollte es gehen. Vielen Dank an Dich, Elmar.

Nachfrage: Was hat es mit der MAXRECURSION 0 für mehr als 100 Tage auf sich?

Answer 3

Hallo Hubert,

rekursive Ausdrücke sind im Standard auf 100 begrenzt, siehe Abfragehinweise.

Gruß Elmar

Answer 4

Bitte gib mir noch einen weiteren Hinweis. Ich versuche gerade aus der vorhanden Sicht "SQL_Urlaubszeiten" mit deiner Methode eine weitere Sicht zu erstellen, aber es klappt nicht. ich habe einige Felder mehr in meiner SQL_Urlaubszeiten, brauche aber nur die 4 besprochenen, doch irgendwas klappt nicht. Fehlermeldung: "Kann SQL nicht analysieren" bzw. "Fehler in der Nähe von GO". Ich vermute ein Problem mit der Anweisung "0 AS Tag  FROM dbo.SQL_UrlaubsZeiten" - aber auch in deinem funktionierenden Beispiel existiert TAG in der deklarierten Tabelle nicht.

folgendes habe ich probiert:

WITH MitarbeiterDatum AS
(
 SELECT Art, MA, Start, Ende, 0 AS Tag  FROM dbo.SQL_UrlaubsZeiten
 UNION ALL
 SELECT Art, MA, CAST(DATEADD(dd, 1, CAST(Start AS date)) AS datetime), Ende, Tag  + 1 FROM MitarbeiterDatum
 WHERE DATEADD(dd, 1, CAST(Start AS date)) <= CAST(Ende AS date)
)
SELECT * FROM MitarbeiterDatum
ORDER BY MA, Art, Start, Ende
OPTION (MAXRECURSION 0); -- wenn mehr als 100 Tage möglich
GO

Answer 5

Oder könnte ich es anders herum lösen: bei dem INSERT INTO-Values-Block deines Beispiels meine Werte über ein SELECT o.ä. einfügen, also wirklich eine neue Mitarbeitertabelle benutzen, die ich mit Werten meiner Sicht fülle?

Answer 6

Hallo Hubert,

Erstelle die Sicht direkt im SQL Fenster vom SSMS (oder anderem), Designer kommen oft mit CTEs (WITH...) nicht klar. Wenn Du die Abfrage als Sicht haben möchtest, so wäre das ORDER BY und OPTION (müsste man ggf. bei der endgültigen Abfrage einfügen) zuviel:

CREATE VIEW SQL_UrlaubsZeiten_Datum
AS
WITH MitarbeiterDatum AS 
(
	SELECT Art, MA, Start, Ende, 0 AS Tag FROM SQL_UrlaubsZeiten
	UNION ALL
	SELECT Art, MA, CAST(DATEADD(dd, 1, CAST(Start AS date)) AS datetime), Ende, Tag + 1 FROM MitarbeiterDatum
	WHERE DATEADD(dd, 1, CAST(Start AS date)) <= CAST(Ende AS date)
)
SELECT * FROM MitarbeiterDatum;

Die Spalte Tage habe ich nur zur Verdeutlichung eingefügt, willst Du sie nicht entferne sie einfach. Wenn möglich verzichte auf das Zwischenschalten von weiteren Sichten und greife direkt auf die Basis-Tabellen zurück.

Gruß Elmar

Answer 7

Hallo Hubert,

Hat Dir die Antwort von Elmar geholfen? Wenn Ja bitte markiere diese als Antwort.

Danke und Gruss,

Ionut

---

Ionut Duma, MICROSOFT   Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip„Entwickler helfen Entwickler“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.