Durchschnittliche Bearbeitungszeit eines Datensatzes in der Arbeitszeit

Question

Hallo zusammen,

Folgendes tricky szenario.

Die Tabelle "system" hat eine Spalte "registrationtime" sowie eine Tabelle "closingtime".

Jeder Datensatz hat nun also ein erstelll- sowie ein schließungszeitpunkt (im datetime format).

Nun benötige ich die durchschnittliche Bearbeitungszeit aller Datensätze pro Monat.

Das an sich ist nicht das Problem, jedoch werden die Zeiten völlig verfälscht, wenn ein Wochenende vorrüber ist.

Jeder Datensatz, der Freitags noch nicht mit einer "closingtime" versehen wurde, erhält automatisch fast 61,5 Stunden dazu.

Gibt es nun eine Möglichkeit, Den Durschnitt zu berechnen und zumindest das Wochenende herauszubekommen?

Wenn es möglich ist, die Zeit lediglich auf die Arbeitszeit von Mo-Fr. 07:30-18:00 zu beschränken, wäre das natürlich noch besser.

Gruß,

Philipp

Answer 1

Kannst du bitte etwas SQL-Code posten, so dass man dein Problem nachvollziehen kann? Zusammen mit dem gewünschten Endergebnis?

---

-- Frank Kalis Microsoft SQL Server MVP Webmaster: http://www.insidesql.org

Answer 2

Hallo Frank,

Hier etwas Code:

CREATE TABLE #Temp (
    caseid   int,
    creationtime DATETIME,
	closingtime DATETIME
)
INSERT INTO #Temp
VALUES(1, '2013-01-01 08:00:00','2013-02-01 12:00:00')  /*	4 Std in Arbeitszeit			*/
INSERT INTO #Temp
VALUES(2, '2013-02-01 08:00:00','2013-02-01 13:00:00')  /*	5 Std							*/
INSERT INTO #Temp
VALUES(3, '2013-03-01 08:00:00','2013-03-01 14:00:00')  /*	6 Std							*/
INSERT INTO #Temp
VALUES(4, '2013-04-01 08:00:00','2013-07-01 11:00:00')  /*	75 Std (13,5 in Arbeitszeit)	*/
INSERT INTO #Temp
VALUES(5, '2013-05-01 08:00:00','2013-07-01 13:00:00')  /*	53 Std (5,5 Std in Arbeitszeit)	*/
INSERT INTO #Temp
VALUES(6, '2013-06-01 08:00:00','2013-07-01 14:00:00')	/*	30 Std (6,5 Std in Arbeitszeit)	*/

Herauskommen soll jetzt für die entsprechende Abfrage:

Durchschnittliche Bearbeitungszeit: 6 Stunden 40 Minuten 0 Sekunden.

AVG(4 + 5 + 6 + 13,5 + 5,5 + 6,5) = 6,75 Stunden.

nachtrag: Arbeitszeit wäre Mo-Fr. 07:30-18:00

Danke schonmal im Vorraus.

Gruß,

Philipp

Answer 3

Danke! Deine Beispieldaten stimmen nicht mit der jeweiligen Arbeitszeit im Kommentar überein, oder? :-)

Ein guter Freund von mir, Jeff Moden, hat mal einen ziemlich coolen Weg beschrieben, um die Anzahl an Arbeitstagen zwischen 2 Daten zu ermitteln: Calculating Work Days. Das habe ich genutzt, um dieses Biest zu schreiben:

IF OBJECT_ID('tempDB..#Temp') IS NOT NULL
    DROP TABLE #Temp;
GO
CREATE TABLE #Temp (
    caseid int,
    creationtime datetime,
    closingtime datetime)
INSERT  INTO #Temp
VALUES
        (1, '2013-01-01 08:00:00', '2013-01-01 12:00:00')
  /*	4 Std in Arbeitszeit			*/
INSERT  INTO #Temp
VALUES
        (2, '2013-01-02 08:00:00', '2013-01-02 13:00:00')
  /*	5 Std							*/
INSERT  INTO #Temp
VALUES
        (3, '2013-01-03 08:00:00', '2013-01-03 14:00:00')
  /*	6 Std							*/
INSERT  INTO #Temp
VALUES
        (4, '2013-01-04 08:00:00', '2013-01-07 11:00:00')
  /*	75 Std (13,5 in Arbeitszeit)	*/
INSERT  INTO #Temp
VALUES
        (5, '2013-01-05 08:00:00', '2013-01-07 13:00:00')
  /*	53 Std (5,5 Std in Arbeitszeit)	*/
INSERT  INTO #Temp
VALUES
        (6, '2013-01-06 08:00:00', '2013-01-07 14:00:00')
	/*	30 Std (6,5 Std in Arbeitszeit)	*/
;
WITH    WorkingDays
          AS (SELECT
                T.caseid,
                (DATEDIFF(dd, creationtime, closingtime) + 1) - (DATEDIFF(wk,
                                                              creationtime,
                                                              closingtime) * 2) -
                (CASE WHEN DATENAME(dw, creationtime) = 'Sunday' THEN 1
                      ELSE 0
                 END) -
                (CASE WHEN DATENAME(dw, closingtime) = 'Saturday' THEN 1
                      ELSE 0
                 END) AS NoOfWorkingDays,
                DATEDIFF(dd, creationtime, closingtime) AS CalendarDays
              FROM
                #Temp T)
    SELECT
        AVG(WorkingTime) AS WorkingTime
    FROM
        (SELECT
            T.caseid,
            T.creationtime,
            T.closingtime,
            NoOfWorkingDays,
            CalendarDays,
            CASE NoOfWorkingDays
              WHEN 1 THEN creationtime
              ELSE DATEADD(DAY, DATEDIFF(DAY, creationtime, closingtime),
                           creationtime)
            END AS [1],
            CASE NoOfWorkingDays
              WHEN 1
              THEN CASE WHEN CalendarDays = 0
                        THEN DATEDIFF(MINUTE, creationtime, closingtime)
                        ELSE DATEDIFF(MINUTE,
                                      DATEADD(DAY, CalendarDays, creationtime),
                                      closingtime) + 30
                   END
              ELSE DATEDIFF(MINUTE, creationtime,
                            DATEADD(minute, 1080,
                                    DATEADD(DAY,
                                            DATEDIFF(DAY, 0, T.creationtime),
                                            0))) + DATEDIFF(MINUTE,
                                                            DATEADD(DAY,
                                                              CalendarDays,
                                                              creationtime),
                                                            closingtime) + 30
            END / 60. AS WorkingTime
         FROM
            WorkingDays WD
            JOIN #Temp T ON T.caseid = WD.caseid) x

Das ist sehr wahrscheinlich nicht perfekt und wird vielleicht sogar falsche Ergebnisse liefern bei grösseren Intervallen, aber zumindest sollte es dir den Hinweis geben, das SQL nicht das geeignete Tool ist für diese Art von Fragestellungen. :-)

Falls du damit spielen willst, findest du ein Skript hier

---

-- Frank Kalis Microsoft SQL Server MVP Webmaster: http://www.insidesql.org

Answer 4

Hallo PhiBu,

Ich gehe davon aus, dass die Antwort Dir weitergeholfen hat.
Solltest Du noch "Rückfragen" dazu haben, so gib uns bitte Bescheid.

Grüße,
Robert

---

Robert Breitenhofer, 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.

Answer 5

Hallo,

beim durchlesen dieser Frage fiel mir auf, dass meine Hilfstabelle "AlleTage" (siehe http://www.insidesql.org/blogs/klausobd/2013/03/29/mein-persoenlicher-ultimativer-datums-tipp-1 ) helfen könnte, die Abfrage einfacher und genauer zu machen.

mfg

Klaus Oberdalhoff

---

Klaus Oberdalhoff Germany