Durchschittlliche Dauer von Zeitspannen ermitteln

[CoderboyPB]

Hallo

In meiner Tabelle gibt es zwei Spalten: start und ende, beide jeweils vom Typ Datetime.

Jede paar (start/ende) repräsentiert also eine Zeitspanne.

Von diesen gilt es dir DURCHSNITTSdauer zu berechnen:

Ich habe folgenden Ansatz gewählt:

SELECT convert(datetime,AVG(datediff(day,[start],[ende]))) as Durchschnittsdauer
  FROM Database

Meine Testdaten sind:

01.05.2008 00:00:00 03.05.2008 18:00:00
17.05.2008 10:00:00 21.05.2008 19:30:00
08.05.2008 09:00:00 28.05.2008 20:00:00

wobei die erste Spalte start und die zweite ende ist.

Als Durchschnitt bekomme ich dann
1900-01-09 00:00:00.000

Was ca. 1 Tag und 9 Stunden entspricht, was auch (grob überschlagen) hinkommen sollte.

Aber tatsächlich hätte ich lieber folgendes Ausgabeformat:

D:H:M:S also: 1:09:00:00

Wie kann ich das hinbekommen ?

Irgendjemand ne Idee ?

[Andreas Kosch]

Hallo,

Was ca. 1 Tag und 9 Stunden entspricht, was auch (grob überschlagen) hinkommen sollte

Das glaube ich nicht ;-)

Das folgende Beispiel demonstriert, dass die Rechnung mit Stunden zu einem bessen Ergebnis führt:

[highlight=SQL]
USE tempdb
GO

CREATE TABLE dbo.ZeitDemo
(
id INT NOT NULL IDENTITY PRIMARY KEY,
start DATETIME NOT NULL,
ende DATETIME NOT NULL
)
GO
INSERT INTO dbo.ZeitDemo (start,ende) VALUES ('01.05.2008 00:00:00','03.05.2008 18:00:00');
INSERT INTO dbo.ZeitDemo (start,ende) VALUES ('17.05.2008 10:00:00','21.05.2008 19:30:00');
INSERT INTO dbo.ZeitDemo (start,ende) VALUES ('08.05.2008 09:00:00','28.05.2008 20:00:00');
GO

-- Tagesdifferenz: 2,4,20 = Durchschnitt von 8,66 Tage
SELECT DATEDIFF(DAY,start,ende) AS Dauer
FROM dbo.ZeitDemo
GO

-- Ergebnis: 8 Tage (richtig wären 8,66)
SELECT
AVG(DATEDIFF(DAY,start,ende)) AS Tage
FROM
dbo.ZeitDemo
GO

-- Umrechnung in Stunden: 220 Stunden = 9 Tage
WITH cte AS
(
SELECT
AVG(DATEDIFF(HOUR,start,ende)) AS Stunden
FROM
dbo.ZeitDemo
)
SELECT Stunden, Stunden / 24 AS Tage FROM cte
GO
[/highlight]

Noch genauer wird das Ergebnis, wenn durch Durchschnitt in der Genauigkeit von Sekunden ermittelt wird:
[highlight=SQL]
-- Durchschnitt in Sekunden ermitteln: 220,8 Stunden oder 9,201 Tage
WITH cte AS
(
SELECT AVG(DATEDIFF(ss,start,ende)) AS Sekunden
FROM dbo.ZeitDemo
)
SELECT
Sekunden,
CAST(Sekunden AS DECIMAL(10,2)) / (60 * 60) AS Stunden,
CAST(Sekunden AS DECIMAL(10,2)) / (24 * 60 * 60) AS Tage
FROM cte
GO
[/highlight]

[CoderboyPB]

Hallo Andreas :-)

Danke für deine Hilfe.
Ich versteh das zwar jetzt auf Anhieb noch nicht, aber ich werde später mal ne SQL Referenz zu Rate ziehen, dann werd ich es wohl hinbekommen :-)