Swego czasu spotkałem się z problemem analizy czasu poświęconego na realizację zgłoszenia oraz ślemieniem zmian zgłoszenia. Oczywiście jest możliwość sprawdzenia jak długo trwała jego realizacja w samej aplikacji, ale co wtedy, gdy mamy takich zgłoszeń do przeanalizowania ponad 100? Najlepiej jest przygotować odpowiedni raport, który takie dane będzie wyświetlał.
Od czego zacząć
Specyfika raportu bazuje na danych z historii zgłoszenia. Dlatego też musimy przeszukiwanie zacząć od tabeli WorkOrderHistory. W tej tabeli znajduje się kolumna WorkOrderID, dzięki czemu możemy odwołać się do danego zgłoszenia (np. wyświetlić numer zgłoszenia, temat itp.). Problem pojawia się w momencie wyliczenia czasu. Dokładniej, jak odwołać się do wartości komórki z poprzedniego wiersza. I tu z pomocą przychodzi funkcja w języku T-SQL o nazwie LAG, wprowadzona w Microsoft SQL Server 2012. Zacznijmy więc od początku.
No to dzieła!
select woh.workorderid "Nr zgłoszenia", woh.historyid "ID historii" from WorkOrderHistory woh where woh.Operation = 'CREATE' or woh.Operation='RESOLVED' or woh.operation='STOPTIMER' or woh.operation='STARTTIMER' or woh.Operation='CLOSE' or woh.Operation='UPDATE'
Powyższe zapytanie pobiera wszystkie id historii z tabeli WorkOrderHistory, gdzie kolumna Operation zawiera wartości CREATE, UPDATE, RESOLVED, CLOSE, STOPTIMER oraz STARTTIMER. Samo to zapytanie nic na szczególnego nie pokaże prócz numerów zgłoszeń i klucza głównego historii (historyid). Musimy doprecyzować jakie dokładnie informacje o zmianach będą nam potrzebne w raporcie. W moim przykładzie będą zawarte zmiany dotyczące aktualizacji serwisanta, grupy, dodanie rozwiązania, zatrzymania i uruchomienia timera, utworzenie, realizacja oraz zamknięcie zgłoszenia. Wszelkie zmiany wprowadzone w zgłoszeniu przechowywane są w tabeli WorkOrderHistoryDiff. Dlatego też musimy ją dołączyć do wyników. Zmodyfikujmy więc nasze zapytanie o kolejne informacje opierając się na nazwy operacji wykonywanych na zgłoszeniu.
(CASE WHEN woh.OPERATION = 'CREATE' THEN 'Utworzenie zgłoszenia' WHEN (woh.OPERATION = 'UPDATE' AND wohd.Columnname='OWNERID') THEN 'Zmiana serwisanta' WHEN (woh.Operation='UPDATE' AND wohd.Columnname='QUEUEID') THEN 'Zmiana grupy' WHEN (woh.Operation='UPDATE' AND wohd.Columnname='REOPENED') THEN 'Ponowne otworzenie' WHEN woh.OPERATION = 'RESOLVED' THEN 'Rozwiązanie zgłoszenia' WHEN woh.OPERATION = 'Close' THEN 'Zamknięcie zgłoszenia' WHEN woh.OPERATION = 'ASSIGN' THEN 'Przypisanie serwisanta' WHEN woh.OPERATION = 'RESOLUTION ADD' THEN 'Dodanie rozwiązania' WHEN woh.OPERATION = 'REQ_CONVER' THEN 'Dodanie konwersacji' END) "Operacja",
W tym momencie zapytanie zakończy się błędem, ponieważ nie ma dołączonych odpowiednich tabel – ale o tym później. Jak widać zastosowano wyrażenie CASE WHEN. Takie raporty przygotowywane są dla osób kierujących biurami/działami i niekoniecznie muszą się znać na języku technicznym. Ponadto tak przygotowany raport jest bardziej estetyczny i czytelny dla potencjalnego odbiorcy nie-IT-owego.
Można powiedzieć, że CASE jest swego rodzaju instrukcją warunkową IF – mam tu na myśli sposób działania, a mianowicie jeśli coś, to coś. Powyższy przykład odpowiada następująco:
- jeśli operacja (woh.operation) jest CREATE, to zamień wyświetlany tekst na Utworzenie zgłoszenia
- jeśli operacja (woh.operation) jest UPDATE oraz nazwa kolumny (wohd.columnname) OWNERID, to zamień tekst wyświetlany na Zmiana serwisanta
- jeśli operacja jest UPDATE oraz nazwa kolumny QUEUEID, to zamień tekst na Zmiana grupy
- jeśli operacja jest UPDATE oraz nazwa kolumny REOPENED, to zgłoszenie zostało ponownie otwarte
Reszty chyba nie trzeba tłumaczyć 🙂
Teraz przyszła kolej na wyświetlenie zmian jakie zostały wprowadzone. Tym razem znów przychodzi nam z pomocą CASE, dzięki któremu wybierzemy interesujące nas wartości. Wybór danych jest zależny od wykonanej operacji, nie zawsze dana operacja zwracała jakiś wynik. I tak dla przykładu, jeśli została wykonana operacja CREATE (utworzenie zgłoszenia) wtedy nie było wartości poprzedniej dla pola Grupa. Nie wygląda to dobrze, gdy w raporcie nagle ni stąd ni zowąd pojawia się wielki brzydki napis Nieprzydzielony. Zastąpimy go myślnikiem lub słówkiem Brak w zależności czego dotyczyła zmiana 😉
(CASE WHEN woh.operation='CREATE' THEN ISNULL((SELECT qd.QUEUENAME from QueueDefinition qd where wohd.PREV_VALUE=qd.QUEUEID), '-') WHEN (woh.Operation='UPDATE' AND wohd.Columnname='QUEUEID') THEN ISNULL((SELECT qd.QUEUENAME from QueueDefinition qd where wohd.PREV_VALUE=qd.QUEUEID), 'Brak') WHEN (woh.Operation='UPDATE' AND wohd.Columnname='OWNERID') THEN ISNULL((SELECT usr.FIRST_NAME from AaaUser usr where wohd.PREV_VALUE=usr.USER_ID), 'Brak') WHEN (woh.Operation='ASSIGN' AND wohd.Columnname='OWNERID') THEN ISNULL((SELECT usr.FIRST_NAME from AaaUser usr where wohd.PREV_VALUE=usr.USER_ID), 'Brak') WHEN woh.operation='RESOLVED' THEN ISNULL((SELECT std.STATUSNAME from StatusDefinition std where wohd.PREV_VALUE=std.statusid), 'Brak zmiany') WHEN woh.operation='CLOSE' THEN ISNULL((SELECT std.STATUSNAME from StatusDefinition std where wohd.PREV_VALUE=std.statusid), 'Brak zmiany') WHEN (woh.operation='UPDATE' and wohd.columnname='REOPENED') THEN '-' WHEN woh.OPERATION = 'RESOLUTION ADD' THEN '-' END) "Zmiana z", (CASE WHEN woh.operation='CREATE' THEN ISNULL((SELECT qd.QUEUENAME from QueueDefinition qd where wohd.CURRENT_VALUE=qd.QUEUEID), '-') WHEN (woh.Operation='UPDATE' AND wohd.Columnname='QUEUEID') THEN ISNULL((SELECT qd.QUEUENAME from QueueDefinition qd where wohd.CURRENT_VALUE=qd.QUEUEID), 'Brak ') WHEN (woh.Operation='UPDATE' AND wohd.Columnname='OWNERID') THEN ISNULL((SELECT usr.FIRST_NAME from AaaUser usr where wohd.CURRENT_VALUE=usr.USER_ID), 'Brak') WHEN (woh.Operation='ASSIGN' AND wohd.Columnname='OWNERID') THEN ISNULL((SELECT usr.FIRST_NAME from AaaUser usr where wohd.CURRENT_VALUE=usr.USER_ID), 'Brak') WHEN woh.operation='RESOLVED' THEN ISNULL((SELECT std.STATUSNAME from StatusDefinition std where wohd.CURRENT_VALUE=std.statusid), 'Brak zmiany') WHEN woh.operation='CLOSE' THEN ISNULL((SELECT std.STATUSNAME from StatusDefinition std where wohd.CURRENT_VALUE=std.statusid), 'Brak zmiany') WHEN woh.operation='STOPTIMER' THEN (SELECT CONVERT(varchar(8),MIN(timer.HISTORYID)) FROM WORKORDERHISTORY timer where (timer.OPERATION='STOPTIMER' and timer.WORKORDERID=woh.WORKORDERID)) WHEN woh.operation='STARTTIMER' THEN (SELECT CONVERT(varchar(8),MIN(timer.HISTORYID)) FROM WORKORDERHISTORY timer where (timer.OPERATION='STARTTIMER' and timer.WORKORDERID=woh.WORKORDERID)) WHEN woh.OPERATION = 'RESOLUTION ADD' THEN '-' WHEN (woh.operation='UPDATE' and wohd.columnname='REOPENED') THEN '-' END) "Zmiana na",
Nie jest to takie skomplikowane prawda? Dobra, teraz czas na wyliczanie czasu. Jak już wspomniano wcześniej w tym celu użyjemy funkcji LAG, która odnosi się do poprzedniego wiersza. Tutaj będzie to troszeczkę skomplikowane, więc postaram się to przedstawić w najprostszy możliwy sposób. Najpierw słowo wyjaśnień.
Jako iż odwołujemy się do poprzedniego wiersza, wyniki będą ujemne na przeskokach między zgłoszeniami (pomiędzy operacją Close a Create). Aby to wyeliminować, musimy sprawdzić czy pierwszy znak jest minusem (funkcja CHARINDEX).
CHARINDEX('-', DATEDIFF(n, dateadd(s,datediff(s,getutcdate(),getdate())+((LAG(woh.operationtime) OVER (ORDER BY woh.workorderid,woh.historyid)) /1000),'1970-01-01 00:00:00'), dateadd(s,datediff(s,getutcdate(),getdate())+(woh.operationtime/1000),'1970-01-01 00:00:00')))=1
Jeżeli powyższy warunek zostanie spełniony, wtedy musimy zastąpić całe wyrażenie zerami, czyli 00:00:00 – puste linie celowo są wprowadzone dla lepszej czytelności (funkcja REPLACE)
REPLACE( DATEDIFF(n, dateadd(s,datediff(s,getutcdate(),getdate())+((LAG(woh.operationtime) OVER (ORDER BY woh.workorderid,woh.historyid)) /1000),'1970-01-01 00:00:00'), dateadd(s,datediff(s,getutcdate(),getdate())+(woh.operationtime/1000),'1970-01-01 00:00:00')), DATEDIFF(n, dateadd(s,datediff(s,getutcdate(),getdate())+((LAG(woh.operationtime) OVER (ORDER BY woh.workorderid,woh.historyid)) /1000),'1970-01-01 00:00:00'), dateadd(s,datediff(s,getutcdate(),getdate())+(woh.operationtime/1000),'1970-01-01 00:00:00')), '00:00:00')
Jeśli pozostawimy to w ten sposób, pozostałe wyliczenia czasu zostaną zastąpione wartościami pustymi (kochane NULL’e…). Dlatego też musimy opisać zachowanie, gdy wynik nie jest ujemny. W tym wypadku znów uratuje nas wyrażenie CASE. Całość powinna wyglądać w ten sposób
(CASE WHEN CHARINDEX('-', DATEDIFF(n, dateadd(s,datediff(s,getutcdate(),getdate())+((LAG(woh.operationtime) OVER (ORDER BY woh.workorderid,woh.historyid)) /1000),'1970-01-01 00:00:00'), dateadd(s,datediff(s,getutcdate(),getdate())+(woh.operationtime/1000),'1970-01-01 00:00:00')))=1 THEN REPLACE(DATEDIFF(n, dateadd(s,datediff(s,getutcdate(),getdate())+((LAG(woh.operationtime) OVER (ORDER BY woh.workorderid,woh.historyid)) /1000),'1970-01-01 00:00:00'), dateadd(s,datediff(s,getutcdate(),getdate())+(woh.operationtime/1000),'1970-01-01 00:00:00')),DATEDIFF(n, dateadd(s,datediff(s,getutcdate(),getdate())+((LAG(woh.operationtime) OVER (ORDER BY woh.workorderid,woh.historyid)) /1000),'1970-01-01 00:00:00'), dateadd(s,datediff(s,getutcdate(),getdate())+(woh.operationtime/1000),'1970-01-01 00:00:00')),'00:00:00')
ELSE
convert(varchar(8),dateadd(n,DATEDIFF(n, dateadd(s,datediff(s,getutcdate(),getdate())+((LAG(woh.operationtime) OVER (ORDER BY woh.workorderid,woh.historyid)) /1000),'1970-01-01 00:00:00'), dateadd(s,datediff(s,getutcdate(),getdate())+(woh.operationtime/1000),'1970-01-01 00:00:00')),0),114) END) AS "Czas od poprzedniej operacji",
Wreszcie najgorsze za nami… Teraz jeszcze delikatna kosmetyka pozostała jak dopisanie osoby, która daną zmianę wprowadzała – dopisujemy usr.FIRST_NAME „Wykonane przez” oraz FROM workorderhistory woh.
Przyszła kolej na dołączenie tabel potrzebnych do poprawnego wykonania zapytania, warunki jakie muszą zostać spełnione przy wykonaniu zapytania (mówiąc prościej – nie wczytywać hurtem danych, które nie będą potrzebne w raporcie) oraz sortowanie
LEFT JOIN workorderhistorydiff wohd ON wohd.Historyid=woh.Historyid LEFT JOIN Statusdefinition sd ON sd.Statusid=CAST(wohd.Current_value AS INT) LEFT JOIN workorder wo ON wo.workorderid = woh.workorderid LEFT JOIN AaaUser usr on woh.OPERATIONOWNERID=usr.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID WHERE ((woh.Operation='CREATE' AND wohd.Columnname IS NULL) OR (woh.Operation='RESOLVED') OR ((woh.operation='STOPTIMER') and (woh.historyid=(SELECT CONVERT(varchar(8),MIN(timer.HISTORYID)) FROM WORKORDERHISTORY timer where (timer.OPERATION='STOPTIMER' and timer.WORKORDERID=woh.WORKORDERID)))) OR (woh.operation='STARTTIMER') OR (woh.Operation='CLOSE') OR ((woh.Operation='UPDATE' AND wohd.Columnname='OWNERID') or (woh.Operation='UPDATE' AND wohd.Columnname='QUEUEID') or (woh.Operation='UPDATE' AND wohd.Columnname='REOPENED')) or (woh.operation='ASSIGN') or (woh.operation='RESOLUTION ADD')) AND dateadd(s,datediff(s,getutcdate(),getdate())+(wo.CREATEDTIME/1000),'1970-01-01 00:00:00')>=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) AND dateadd(s,datediff(s,getutcdate(),getdate())+(wo.CREATEDTIME/1000),'1970-01-01 00:00:00')<DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) ORDER BY woh.workorderid, woh.historyid
Podsumowanie
Z tak przygotowanym raportem możemy udać się do przełożonego i zademonstrować wyniki, celem przedłożenia ich do analizy. Należy jednak pamiętać, aby raport wykonywać w godzinach o mniejszym obciążeniu serwera aplikacji i bazy danych. Ze względu na to, że zapytanie to obejmuje duże ilości danych spełniających pewne warunki, może to doprowadzić do tymczasowego unieruchomienia aplikacji. W moim przykładzie wybrałem filtr daty na tydzień (mała ilość danych, coś koło 15-20 zgłoszeń w środowisku testowym), w przypadku uruchomienia na serwerze produkcyjnym, zawierających dużą ilość zgłoszeń, zalecam zastosować mniejszy zakres dni.
Dla ułatwienia zamieszczam cały raport poniżej
SELECT woh.workorderid "ID zgłoszenia",
(CASE WHEN woh.OPERATION = 'CREATE' THEN 'Utworzenie zgłoszenia'
WHEN (woh.OPERATION = 'Update' AND wohd.Columnname='OWNERID') THEN 'Zmiana serwisanta'
WHEN (woh.Operation='UPDATE' AND wohd.Columnname='QUEUEID') THEN 'Zmiana grupy'
WHEN (woh.Operation='UPDATE' AND wohd.Columnname='REOPENED') THEN 'Ponowne otworzenie'
WHEN woh.OPERATION = 'RESOLVED' THEN 'Rozwiązanie zgłoszenia'
WHEN woh.OPERATION = 'Close' THEN 'Zamknięcie zgłoszenia'
WHEN woh.OPERATION = 'ASSIGN' THEN 'Zmiana serwisanta'
WHEN woh.OPERATION = 'RESOLUTION ADD' THEN 'Dodanie rozwiązania'
WHEN woh.OPERATION = 'REQ_CONVER' THEN 'Dodanie konwersacji'
END) "Operacja",
(CASE WHEN woh.operation='CREATE' THEN ISNULL((SELECT qd.QUEUENAME from QueueDefinition qd where wohd.PREV_VALUE=qd.QUEUEID), '-')
WHEN (woh.Operation='UPDATE' AND wohd.Columnname='QUEUEID') THEN ISNULL((SELECT qd.QUEUENAME from QueueDefinition qd where wohd.PREV_VALUE=qd.QUEUEID), 'Brak')
WHEN (woh.Operation='UPDATE' AND wohd.Columnname='OWNERID') THEN ISNULL((SELECT usr.FIRST_NAME from AaaUser usr where wohd.PREV_VALUE=usr.USER_ID), 'Brak')
WHEN (woh.Operation='ASSIGN' AND wohd.Columnname='OWNERID') THEN ISNULL((SELECT usr.FIRST_NAME from AaaUser usr where wohd.PREV_VALUE=usr.USER_ID), 'Brak')
WHEN woh.operation='RESOLVED' THEN ISNULL((SELECT std.STATUSNAME from StatusDefinition std where wohd.PREV_VALUE=std.statusid), 'Brak zmiany')
WHEN woh.operation='CLOSE' THEN ISNULL((SELECT std.STATUSNAME from StatusDefinition std where wohd.PREV_VALUE=std.statusid), 'Brak zmiany')
WHEN (woh.operation='UPDATE' and wohd.columnname='REOPENED') THEN '-'
WHEN woh.OPERATION = 'RESOLUTION ADD' THEN '-'
END) "Zmiana z",
(CASE WHEN woh.operation='CREATE' THEN ISNULL((SELECT qd.QUEUENAME from QueueDefinition qd where wohd.CURRENT_VALUE=qd.QUEUEID), '-')
WHEN (woh.Operation='UPDATE' AND wohd.Columnname='QUEUEID') THEN ISNULL((SELECT qd.QUEUENAME from QueueDefinition qd where wohd.CURRENT_VALUE=qd.QUEUEID), 'Brak ')
WHEN (woh.Operation='UPDATE' AND wohd.Columnname='OWNERID') THEN ISNULL((SELECT usr.FIRST_NAME from AaaUser usr where wohd.CURRENT_VALUE=usr.USER_ID), 'Brak')
WHEN (woh.Operation='ASSIGN' AND wohd.Columnname='OWNERID') THEN ISNULL((SELECT usr.FIRST_NAME from AaaUser usr where wohd.CURRENT_VALUE=usr.USER_ID), 'Brak')
WHEN woh.operation='RESOLVED' THEN ISNULL((SELECT std.STATUSNAME from StatusDefinition std where wohd.CURRENT_VALUE=std.statusid), 'Brak zmiany')
WHEN woh.operation='CLOSE' THEN ISNULL((SELECT std.STATUSNAME from StatusDefinition std where wohd.CURRENT_VALUE=std.statusid), 'Brak zmiany')
WHEN woh.operation='STOPTIMER' THEN (SELECT CONVERT(varchar(8),MIN(timer.HISTORYID)) FROM WORKORDERHISTORY timer where (timer.OPERATION='STOPTIMER' and timer.WORKORDERID=woh.WORKORDERID))
WHEN woh.operation='STARTTIMER' THEN (SELECT CONVERT(varchar(8),MIN(timer.HISTORYID)) FROM WORKORDERHISTORY timer where (timer.OPERATION='STARTTIMER' and timer.WORKORDERID=woh.WORKORDERID))
WHEN woh.OPERATION = 'RESOLUTION ADD' THEN '-'
WHEN (woh.operation='UPDATE' and wohd.columnname='REOPENED') THEN '-'
END) "Zmiana na",
usr.FIRST_NAME "Wykonane przez",
isnull((CASE WHEN CHARINDEX('-', DATEDIFF(n, dateadd(s,datediff(s,getutcdate(),getdate())+((LAG(woh.operationtime) OVER (ORDER BY woh.workorderid,woh.historyid)) /1000),'1970-01-01 00:00:00'), dateadd(s,datediff(s,getutcdate(),getdate())+(woh.operationtime/1000),'1970-01-01 00:00:00')))=1 THEN REPLACE(DATEDIFF(n, dateadd(s,datediff(s,getutcdate(),getdate())+((LAG(woh.operationtime) OVER (ORDER BY woh.workorderid,woh.historyid)) /1000),'1970-01-01 00:00:00'), dateadd(s,datediff(s,getutcdate(),getdate())+(woh.operationtime/1000),'1970-01-01 00:00:00')),DATEDIFF(n, dateadd(s,datediff(s,getutcdate(),getdate())+((LAG(woh.operationtime) OVER (ORDER BY woh.workorderid,woh.historyid)) /1000),'1970-01-01 00:00:00'), dateadd(s,datediff(s,getutcdate(),getdate())+(woh.operationtime/1000),'1970-01-01 00:00:00')),'00:00:00') ELSE convert(varchar(8),dateadd(n,DATEDIFF(n, dateadd(s,datediff(s,getutcdate(),getdate())+((LAG(woh.operationtime) OVER (ORDER BY woh.workorderid,woh.historyid)) /1000),'1970-01-01 00:00:00'), dateadd(s,datediff(s,getutcdate(),getdate())+(woh.operationtime/1000),'1970-01-01 00:00:00')),0),114) END), '00:00:00') as "Czas od poprzedniej operacji" FROM workorderhistory woh
LEFT JOIN workorderhistorydiff wohd ON wohd.Historyid=woh.Historyid
LEFT JOIN Statusdefinition sd ON sd.Statusid=CAST(wohd.Current_value AS INT)
LEFT JOIN workorder wo ON wo.workorderid = woh.workorderid
LEFT JOIN AaaUser usr on woh.OPERATIONOWNERID=usr.USER_ID
LEFT JOIN "WorkOrder_Queue" "woq" ON "wo"."WORKORDERID"="woq"."WORKORDERID"
LEFT JOIN "QueueDefinition" "qd" ON "woq"."QUEUEID"="qd"."QUEUEID"
WHERE ((woh.Operation='CREATE' AND wohd.Columnname IS NULL) OR (woh.Operation='RESOLVED') OR ((woh.operation='STOPTIMER') and (woh.historyid=(SELECT CONVERT(varchar(8),MIN(timer.HISTORYID)) FROM WORKORDERHISTORY timer where (timer.OPERATION='STOPTIMER' and timer.WORKORDERID=woh.WORKORDERID)))) OR (woh.operation='STARTTIMER') OR (woh.Operation='CLOSE') OR ((woh.Operation='UPDATE' AND wohd.Columnname='OWNERID') or (woh.Operation='UPDATE' AND wohd.Columnname='QUEUEID') or (woh.Operation='UPDATE' AND wohd.Columnname='REOPENED')) or (woh.operation='ASSIGN') or (woh.operation='RESOLUTION ADD')) AND dateadd(s,datediff(s,getutcdate(),getdate())+(wo.CREATEDTIME/1000),'1970-01-01 00:00:00')>=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) AND dateadd(s,datediff(s,getutcdate(),getdate())+(wo.CREATEDTIME/1000),'1970-01-01 00:00:00')<DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)
ORDER BY woh.workorderid, woh.historyid