SQL-Abfrage - Daten pro Stunde (Uhrzeit) aus Zeitraum extrahieren

[ckoch1979]

Hallo Profis,

ich habe folgendes Problem.

Ausgehend von der folgenden Tabelle möchte ich eine Abfrage schreiben, die
mir die Mitarbeiterstunden für eine bestimmte Stunde (Uhrzeit) angibt.

Ausgangsdaten

Mitarbeiter Uhrzeit von Uhrzeit bis
Axel 17:00 19:00
Felix 17:30 19:30

Gewünschte Abfrage

Stunde Anzahl

17:00-18:00 1,5
18:00-19:00 2
19:00-20:00 0,5

Wie kann ich das realisieren? Scheint etwas tricky zu sein.

[Andreas Kosch]

Hallo,

wenn der MS SQL Server 2005 genutzt wird, könnte man zu dem folgenden Konstrukt greifen:

A) Vorbereitung

USE tempdb
GO

CREATE TABLE mitarbeiterstunden
(
  ma  VARCHAR(9) NOT NULL,
  von DATETIME NOT NULL,
  bis DATETIME NOT NULL
)
GO
INSERT INTO dbo.mitarbeiterstunden (ma,von,bis) VALUES ('Klaus','06.10.2007 16:00:00','06.10.2007 19:00:00')
INSERT INTO dbo.mitarbeiterstunden (ma,von,bis) VALUES ('Axel','06.10.2007 17:00:00','06.10.2007 19:00:00')
INSERT INTO dbo.mitarbeiterstunden (ma,von,bis) VALUES ('Felix','06.10.2007 17:30:00','06.10.2007 19:30:00')
INSERT INTO dbo.mitarbeiterstunden (ma,von,bis) VALUES ('Uwe','06.10.2007 17:10:00','06.10.2007 17:50:00')
GO

B) CTE-Abfrage

-- Common Table Expression stellt die Minuten je Stunde zur Verfügung
WITH CTEStunde AS
(
SELECT 
    ma,DATEPART(hh, von) AS Stunde, 
    DATEDIFF(mi, von, DATEADD(hour,DATEDIFF(hour, 0, von) +1,0)) AS Minuten, 
    0 AS FehlendeMinuten 
  FROM dbo.mitarbeiterstunden
  WHERE (von >= DATEADD(hour,DATEDIFF(hour, 0, von),0) and 
        (von <= DATEADD(hour,DATEDIFF(hour, 0, von) +1,0))) 
UNION ALL
SELECT 
    ma,DATEPART(hh, von) AS Stunde,
    0 AS Minuten,
    CASE 
      WHEN DATEDIFF(mi, bis, DATEADD(hour,DATEDIFF(hour, 0, von) +1,0)) > 0 THEN DATEDIFF(mi, bis, DATEADD(hour,DATEDIFF(hour, 0, von) +1,0))
      ELSE 0
    END AS FehlendeMinuten 
  FROM dbo.mitarbeiterstunden
  WHERE (von >= DATEADD(hour,DATEDIFF(hour, 0, von),0)
    AND (von <= DATEADD(hour,DATEDIFF(hour, 0, von) +1,0))) 
)
SELECT 
  CAST(Stunde AS VARCHAR) + ' bis ' + CAST(Stunde + 1 AS VARCHAR) + ' Uhr' AS Zeitraum, 
  SUM(Minuten) - SUM(FehlendeMinuten) AS Mitarbeiterminuten
FROM CTEStunde
GROUP BY Stunde;

Allerdings gibt es da einen Haken bei der Sache. In der Ergebnismenge tauch nur dann ein Stundenbereich auf, wenn ein auch mindestenen einen von-Datensatz zu dieser Stunde gibt.