Alexander Scholler schrieb:
Hallo,
ich möchte die "Aktivitäten" unseres Support-Teams auswerten. Hierzu greife ich mit nativem SQL auf die Tabellen * ticket_history (entspricht der Ticket-History im Web) * time_accounting (gespeicherte Bearbeitungszeiten) * article (gespeicherte Artikel) zu.
Zwischen alle 3 Tabellen lassen sich über ticket_id und article_id Beziehungen herstellen. Die Angabe von Zeitwerten ist bei unserem OTRS obligatorisch (wg. Ticket::Frontend::NeedAccountedTime = Ja), jedoch werden Zeitangaben "0" (werden hin und wieder im WebGUI gemacht und von diesem akzeptiert) nicht in der Tabelle time_accounting hinterlegt.
Nun möchte ich auswerten z.B. kalenderwochenweise * wieviele Tickets bearbeitet wurden (jedoch nicht zwingend abgeschlossen wurden) * wieviele Artikel (schriftl. Einzelaktivitäten) hierbei verfasst wurden * welche Arbeitszeiten sich aufsummiert haben.
Um dies zu erreichen kann ich die Tabelle 1) ticket_history verwenden, nämlich alle Einträge mit - create_by > 1 (der Adminaccount wird nicht von Agents genutzt) und - article_id IS NOT NULL (Artikel-Beziehung ist gegeben) - history_type_id IN (6, 7, 8, 11, 13, 14, 15) (alle schriftl. Aktivitaeten) oder 2) article verwenden
Optional kann ich bei beiden Abfragen einen LEFT JOIN zur Tabelle time_accounting durchführen um die zusätzliche Angabe der Bearbeitungszeit zu erhalten.
Problem ist nun:
Die Abfrage 1 liefert viel mehr Tickets und Articles/Aktivitaeten zurück als die Anfrage 2, auch sind die Bearbeitungszeiten größer.
Wo ist der Denkfehler? * Ist vielleicht history_type_id IN (6, 7, 8, 11, 13, 14, 15) zu groß gewählt? * Jedoch wird unter time_accounting ja nur bearbeitungszeit aufgrund von Aktivitäten der Agents eingetragen. D.h. doch dass bei der 2. Abfrage gewisse Agent-Aktivitäten fälschlicherweise nicht berücksichtigt werden.
Nun habe ich den Grund gefunden: Neue Telefonticket werden unter article mit article_sender_type_id = 3 = customer eingetragen, späterliegende Telefonate innerhalb des Tickets jedoch mit article_sender_type_id = 1 = agent. Darum werden in 2) mehr Tickets/Articles zurückgeliefert als in 1) Eine Änderung von
WHERE article_sender_type_id = 1 /* Artikel stammt von einem Agent */ in WHERE article_sender_type_id != 2 /* Artikel stammt nicht vom System selbst */
führt dazu dass * bei beiden Abfragen eine identische Stunden-Angabe erfolgt * dass bei 2 geringfügig mehr Tickets und Aktivitaeten ausgegeben werden als bei 1 Der Grund hierfür ist nun vermutlich die nicht ganz korrekte Selektion bei history_type_id IN (6, 7, 8, 11, 13, 14, 15)
Gruß, Alexander
===================
Nachfolgend die beiden Abfragen:
/* 1) --- schriftl. Aktivitaeten der IT-MA, gruppiert nach Jahr/KW (Analyse anhand Tickethistory) ---
Tickets == Anzahl der involvierten Tickets Aktivitaeten == Summe der durchgeführten schriftl. Aktivitaeten durch die IT-MA */
SELECT temp.Jahr, temp.KW, COUNT(ticket_id) AS Tickets, SUM(aktivitaet) AS Aktivitaeten, SUM(min)/60 AS Stunden FROM ( SELECT ticket_history.ticket_id AS ticket_id, COUNT(ticket_history.id) AS aktivitaet, SUM(time_unit) AS min, YEAR(ticket_history.create_time) AS Jahr, WEEKOFYEAR(ticket_history.create_time) AS KW FROM ticket_history LEFT JOIN time_accounting ON ticket_history.ticket_id = time_accounting.ticket_id AND ticket_history.article_id = time_accounting.article_id WHERE ticket_history.article_id IS NOT NULL AND history_type_id IN (6, 7, 8, 11, 13, 14, 15) AND ticket_history.create_by > 1 /* alle schriftlichen Aktivitaeten des Agents */ GROUP BY Jahr, KW, ticket_id ) AS temp GROUP BY temp.Jahr, temp.KW ORDER BY temp.Jahr, temp.KW;
/* 2) --- Ticket- und Aktivitaetensumme inkl. Bearbeitungszeit, gruppiert nach Jahr/KW --- Tickets == Anzahl der Tickets die in der KW behandelt (aber nicht zwingend abgeschlossen) wurden Aktivitaeten == Summe der hierbei durchgeführten schriftlichen Aktivitaeten der IT-MA Stunden == Summe der hierbei angefallenen Bearbeitungszeit */
SELECT Jahr, KW, COUNT(ticket_id) AS Tickets, SUM(artikel) AS Aktivitaeten, SUM(min)/60 AS Stunden FROM ( SELECT article.ticket_id, COUNT(article.id) AS artikel, SUM(time_unit) AS min, YEAR(article.create_time) AS Jahr, WEEKOFYEAR(article.create_time) AS KW FROM article LEFT JOIN time_accounting ON article.id = time_accounting.article_id AND article.ticket_id = time_accounting.ticket_id WHERE article_sender_type_id = 1 /* Artikel stammt von einem Agent */ GROUP BY Jahr, KW, ticket_id ) AS temp GROUP BY Jahr, KW;