Durchlaufzeit

[Eberold]

Hallo zusammen,

auf der Suche nach einem guten deutschsprachigen Forum für SQL bin ich nun auf dieses gestoßen.
Und gleich hätte ich eine "größere" Frage.

Ich versuche soeben die Durchlaufzeit einzelner Postfächer zu ermitteln.

Alle Daten werden in eine Roh-Tabelle geschrieben.

Aufbau der Tabelle:
ID | Createddate(Zeitstempel) | Bearbeitungsnummer | Status | Postfach | Bearbeiter | Erstellt_am | Bearbeitet_am
99 | 2017-06-30 23:45:18.0000 | TE1541247 | NEU | Vermittlung | NULL | 2017-06-30 23:45:18.0000 | NULL
100 | 2017-07-15 12:00:15.0000 | TE1578947 | NEU | Vermittlung | NULL | 2017-07-15 12:00:15.0000 | NULL
101 | 2017-07-15 12:03:49.0000 | TE1594748 | Bearbeitet | Buchhaltung | Hans | 2017-07-04 14:12:36.0000 | 2017-07-15 12:03:49.0000
102 | 2017-07-15 13:05:12.0000 | TE1600147 | NEU | Lager | NULL | 2017-07-15 13:05:12.0000 | NULL
103 | 2017-07-15 13:07:31.0000 | TE1578947 | Weitergeleitet | Personal | Markus | 2017-07-15 12:00:15.0000 | NULL
104 | 2017-07-15 13:08:48.0000 | TE1541247 | Warten | Personal | Hans | 2017-06-30 23:45:18.0000 | NULL
105 | 2017-07-15 14:49:55.0000 | TE1541247 | Weitergeleitet | Buchhaltung | Sven | 2017-06-30 23:45:18.0000 | NULL
106 | 2017-07-16 08:01:27.0000 | TE1610149 | NEU | Vermittlung | NULL| 2017-07-16 08:01:27.0000 | NULL
107 | 2017-07-16 09:23:17.0000 | TE1578947 | Weitergeleitet | Buchhaltung | Sven | 2017-07-15 12:00:15.0000 | NULL
108 | 2017-07-16 14:09:13.0000 | TE1611794 | NEU | Buchhaltung | NULL | 2017-07-16 14:09:13.0000 | NULL
109 | 2017-07-17 03:05:42.0000 | TE1600147 | Weitergeleitet | Personal | Niki | 2017-07-15 13:05:12.0000 | NULL
110 | 2017-07-18 09:31:02.0000 | TE1604731 | Warten | Personal | Hans | 2017-06-12 14:12:32.0000 | NULL
111 | 2017-07-18 15:15:49.0000 | TE1578947 | Bearbeitet | Buchhaltung | Hans | 2017-07-15 12:00:15.0000 | 2017-07-18 15:15:49.0000
112 | 2017-07-18 16:18:53.0000 | TE1541247 | Warten | Buchhaltung | Markus | 2017-06-30 23:45:18.0000 | NULL
113 | 2017-07-18 17:54:19.0000 | TE1541247 | Warten | Buchhaltung | Hans | 2017-06-30 23:45:18.0000 | NULL
114 | 2017-07-18 19:03:44.0000 | TE1541247 | Bearbeitet | Buchhaltung | Markus | 2017-06-30 23:45:18.0000 | 2017-07-18 19:03:44.0000
115 | 2017-07-18 20:14:37.0000 | TE1612616 | NEU | Buchhaltung | Hans | 2017-07-18 20:14:37.0000 | NULL

Die Herausforderung die ich gerade habe ist die Durchlaufzeit, wenn Fall 1 in Postfach A erstellt wird, von dort nach Postfach B, in diesem ggf. die Bearbeitung auf Warten gestellt wird
und dann weiter zu Postfach C geleitet wird.

Im Grunde interessiert mich nun die Durchlaufzeit in Postfach B.
leider kann es vorkommen, dass der Status "Warten" zu einem Fall mehrfach im selben Postfach vorkommt (mit unterschiedlichen Createdate-Zeiten)

Die generelle Durchlaufzeit zwischen Erstellung des Anliegen und finaler Bearbeitung stellt keine Herausfordung dar.

SELECT CONVERT(date, s.Createddate) Datum
, s.Bearbeitungsnummer
, s.Postfach
, Count(distinct case when s.STATUS = 'Bearbeitet' and convert(date, s.Bearbeitet_am) = @vondatum then s.Bearbeitungsnummer else Null end) Abschluss
, isnull((DATEDIFF(SECOND, min(distinct case when s.STATUS = 'Bearbeitet'
and convert(date, s.Bearbeitet_am) = @vondatum then s.Erstellt_am else Null end),
min(distinct case when s.STATUS = 'Bearbeitet'
and convert(date, s.Bearbeitet_am) = @vondatum then s.Bearbeitet_am else Null end))),0) DLZ_Gesamt
INTO #dlz_gesamt
FROM [sachbearbeitung].[dbo].[postfaecher] s
INNER JOIN abt_personal.dbo.personal_post pf on s.Postfach = pf.Postfaecher
WHERE convert(date, s.Createddate) = @vondatum
and s.Bearbeiter not like ('Auto%')
and s.STATUS = 'Bearbeitet'
and pf.Abteilung != 'NICHT RELEVANT'
GROUP BY CONVERT(date, s.Createddate), s.Postfach, s.Bearbeitungsnummer
ORDER BY CONVERT(date, s.Createddate), s.Postfach ASC

Hat jemand eine gute Idee für mich?

Besten Dank schonmal.
Eberold

 

[ukulele]

Die Frage ist erstmal wie dynamisch ist das ganze. Ist der Ablauf immer
Postfach A (ein Eintrag Status=NEU) ->
B (1 bis n Einträge Status=Waten) ->
B (ein Eintrag Status=Weitergeleitet) ->
C (ein Eintrag Status=Bearbeitet)

Oder kann das auch mal direkt von A nach C oder wieder von C nach B laufen?

 

[Eberold]

Hallo ukulele,

es ist durchaus möglich, dass ein Fall bei A eingeht dann weiter zu C,
dann vielleicht wieder zu A oder gar zu D und irgendwann zu B.

Im Grunde geht es mir darum die Durchlaufzeit von den Postfächern zu ermitteln, also wie lange liegt ein Fall im jeweiligen Postfach.
Postfach B ist hier nur beispielhaft. Im Grunde muss ich Zeit von jedem Postfach ermitteln.
Also wie lang liegt der Fall in A, dann in B usw.

Das ganze ist leider sehr dynamisch und eine Anpassung der Tabellenstruktur ist nicht möglich.

 

[akretschmer]

klingt alles nach einem Fall für Window-Funktionen, insbesondere lag(). Damit kannst Du auf den vorhergehenden Datensatz zugreifen und z.B. Differenzen zwischen einzelnen Zeiten berechnen.

 

[ukulele]

Das könnte so aussehen:

WITH tabelle(id,createdate,bearbeitungsnummer,[status],postfach,bearbeiter,erstellt_am,bearbeitet_am) AS (
   SELECT 99,'2017-06-30 23:45:18.000','TE1541247','NEU','Vermittlung',NULL,'2017-06-30 23:45:18.000',NULL UNION ALL
   SELECT 100,'2017-07-15 12:00:15.000','TE1578947','NEU','Vermittlung',NULL,'2017-07-15 12:00:15.000',NULL UNION ALL
   SELECT 101,'2017-07-15 12:03:49.000','TE1594748','Bearbeitet','Buchhaltung','Hans','2017-07-04 14:12:36.000','2017-07-15 12:03:49.000' UNION ALL
   SELECT 102,'2017-07-15 13:05:12.000','TE1600147','NEU','Lager',NULL,'2017-07-15 13:05:12.000',NULL UNION ALL
   SELECT 103,'2017-07-15 13:07:31.000','TE1578947','Weitergeleitet','Personal','Markus','2017-07-15 12:00:15.000',NULL UNION ALL
   SELECT 104,'2017-07-15 13:08:48.000','TE1541247','Warten','Personal','Hans','2017-06-30 23:45:18.000',NULL UNION ALL
   SELECT 105,'2017-07-15 14:49:55.000','TE1541247','Weitergeleitet','Buchhaltung','Sven','2017-06-30 23:45:18.000',NULL UNION ALL
   SELECT 106,'2017-07-16 08:01:27.000','TE1610149','NEU','Vermittlung',NULL,'2017-07-16 08:01:27.000',NULL UNION ALL
   SELECT 107,'2017-07-16 09:23:17.000','TE1578947','Weitergeleitet','Buchhaltung','Sven','2017-07-15 12:00:15.000',NULL UNION ALL
   SELECT 108,'2017-07-16 14:09:13.000','TE1611794','NEU','Buchhaltung',NULL,'2017-07-16 14:09:13.000',NULL UNION ALL
   SELECT 109,'2017-07-17 03:05:42.000','TE1600147','Weitergeleitet','Personal','Niki','2017-07-15 13:05:12.000',NULL UNION ALL
   SELECT 110,'2017-07-18 09:31:02.000','TE1604731','Warten','Personal','Hans','2017-06-12 14:12:32.000',NULL UNION ALL
   SELECT 111,'2017-07-18 15:15:49.000','TE1578947','Bearbeitet','Buchhaltung','Hans','2017-07-15 12:00:15.000','2017-07-18 15:15:49.000' UNION ALL
   SELECT 112,'2017-07-18 16:18:53.000','TE1541247','Warten','Buchhaltung','Markus','2017-06-30 23:45:18.000',NULL UNION ALL
   SELECT 113,'2017-07-18 17:54:19.000','TE1541247','Warten','Buchhaltung','Hans','2017-06-30 23:45:18.000',NULL UNION ALL
   SELECT 114,'2017-07-18 19:03:44.000','TE1541247','Bearbeitet','Buchhaltung','Markus','2017-06-30 23:45:18.000','2017-07-18 19:03:44.000' UNION ALL
   SELECT 115,'2017-07-18 20:14:37.000','TE1612616','NEU','Buchhaltung','Hans','2017-07-18 20:14:37.000',NULL
   ), t1(id,createdate,bearbeitungsnummer,[status],postfach,bearbeiter,erstellt_am,bearbeitet_am,laufzeit) AS (
   SELECT   id,
       convert(DATETIME,createdate,121) AS createdate,
       bearbeitungsnummer,
       [status],
       postfach,
       bearbeiter,
       convert(DATETIME,erstellt_am,121) AS erstellt_am,
       convert(DATETIME,bearbeitet_am,121) AS bearbeitet_am,
       (   CASE
         WHEN    postfach = lag(postfach) OVER (PARTITION BY bearbeitungsnummer ORDER BY convert(DATETIME,createdate,121) ASC)
         THEN   datediff(minute,
             lag(createdate) OVER (PARTITION BY bearbeitungsnummer ORDER BY convert(DATETIME,createdate,121) ASC),
             createdate)
         ELSE   0
         END )
   FROM   tabelle
   )
SELECT   min(createdate) AS start,bearbeitungsnummer,postfach,sum(laufzeit) AS laufzeit
FROM   t1
GROUP BY bearbeitungsnummer,postfach
ORDER BY min(createdate),bearbeitungsnummer

Wobei der oberse Teil (tabelle) nur deine Daten darstellt und convert(DATETIME,spalte,121) bei dir wohl auch nicht nötig sein wird (bei mir hätte er es sonst als Zeichenkette behandelt).

Die Tabelle t1 arbeitet mit lag() und wenn die Abteilung gleich bleibt zwischen zwei Einträgen wird die Laufzeit ermittelt. Was zwischen den Abteilungen passiert weiß ich nicht, daran kann man noch feilen. t1 fasse ich dann zusammen.