Hallo Walo,
weiter aufteilen kann man es, in dem man den Datumsanteil mit einer Kalendertabelle verknüpft. Ähnliches kann man auch mit den Stunden machen.
Eine angefangene Lösung:
-- eine (partielle) Kalendertabelle
CREATE TABLE #Kalender (Datum Date NOT NULL CONSTRAINT PK_Kalender PRIMARY KEY);
INSERT INTO #Kalender(Datum)
SELECT DATEADD(dd, number, '2015-01-01')
FROM master..spt_values WHERE type = 'P';
-- für Stunden (ggf. weiter aufteilen nach Wochentage etc.)
CREATE TABLE #Stunden (Stunde int NOT NULL CONSTRAINT PK_Stunde PRIMARY KEY,
StundeText varchar(30) CONSTRAINT UK_Stunde_Text UNIQUE);
DECLARE @Stunde int = 0;
WHILE @Stunde < 24
BEGIN
INSERT INTO #Stunden (Stunde, StundeText) VALUES (@Stunde,
CAST(@Stunde AS varchar) + ':00-' + CAST(@Stunde + 1 AS varchar) + ':00');
SET @Stunde += 1;
END;
-- eine Belegung (partiell)
CREATE TABLE #Belegung (
BelegungId int NOT NULL,
Eingang Datetime2 NOT NULL,
Ausgang Datetime2 NOT NULL);
INSERT INTO #Belegung VALUES
(1, '2015-02-02 08:10:15', '2015-02-04 09:10:20'),
(2, '2015-02-03 18:00:15', '2015-02-04 12:15:20'),
(3, '2015-02-04 10:00:15', '2015-02-04 15:30:20');
-- liefert alle belegten Stunden
SELECT k.Datum, s.Stunde, COUNT(*)
FROM #Belegung AS b
INNER JOIN #Kalender AS k ON k.Datum >= CAST(b.Eingang AS date) AND k.Datum <= CAST(b.Ausgang AS date)
INNER JOIN #Stunden AS s ON (k.Datum = CAST(b.Eingang AS date) AND s.Stunde >= DATEPART(hour, b.Eingang))
OR (k.Datum = CAST(b.Ausgang AS date) AND s.Stunde <= DATEPART(hour, b.Ausgang))
OR (k.Datum > CAST(b.Eingang AS date) AND k.Datum < CAST(b.Ausgang AS date))
GROUP BY k.Datum, s.Stunde
ORDER BY k.Datum, s.Stunde;
-- erweitert um belegte und nicht belegte via CROSS APPLY
SELECT k.Datum, s.Stunde, SUM(s.Belegt)
FROM #Belegung AS b
INNER JOIN #Kalender AS k ON k.Datum >= CAST(b.Eingang AS date) AND k.Datum <= CAST(b.Ausgang AS date)
CROSS APPLY (SELECT Stunde, -- oder StundeText
CASE WHEN (k.Datum = CAST(b.Eingang AS date) AND s.Stunde >= DATEPART(hour, b.Eingang))
OR (k.Datum = CAST(b.Ausgang AS date) AND s.Stunde <= DATEPART(hour, b.Ausgang))
OR (k.Datum > CAST(b.Eingang AS date) AND k.Datum < CAST(b.Ausgang AS date))
THEN 1 ELSE 0 END AS Belegt
FROM #Stunden AS s) AS s(Stunde, Belegt)
GROUP BY k.Datum, s.Stunde
ORDER BY k.Datum, s.Stunde;
Den Teil mit PIVOT oder wie Du es gelöst hast über SUM(CASE...) habe ich mir vorerst geschenkt.
Gruß Elmar
Anzahl Pro Stunde und Tag
