Zustandsübergänge in SQL auswerten

Oftmals werden in SQL-Tabellen die Zustände oder Stati eines Objektes in chronologischer Reihenfolge eingetragen. Jedoch wie kann mittels SQL Statement eine Liste erstellt werden, in der als eine Spalte ein Wert des letzten Statusüberganges angezeigt werden soll.

Mein Beispiel arbeitet mit einer Ticketliste. Jedes Ticket ist durch eine TicketNR identifiziert. Die Stati die ein Ticket durchläuft sind “offen”, “in Kontrolle” und “erledigt”.

image

Ziel ist es nun ein Abfrageergebnis zu erhalten, in dem in einer Spalte das Datum der letzen Änderung steht. Dadurch kann z.B. die Dauer eines Schrittes berechnet werden.

image

Dieses Problem kann durch Common Table Expressions (CTE) gelöst werden. Hierbei wird zunächst im ersten Statement eine Nummerierung der Einträge je Ticket erzielt. Ich verwende das Eintragsdatum als Sortierungskriterium.

Mit dieser Abfrage bereite ich die Daten auf.

select 
ROW_NUMBER() OVER (order by v.ticketnr, v.datum asc) as Nummer,
v.ID, v.Datum, v.TicketNr, v.Status 
from Ticketverlauf v

und erhalte folgendes Ergebnis:

image

Dieses Statement verwende ich in meiner CTE nun als Basis um von jedem Element den Vorgänger zu ermitteln. Die CTE ist folgendermaßen definiert:

with erg (Nummer, ID, Datum, TicketNr, Status) 
as (
select ROW_NUMBER()
OVER (order by v.ticketnr, v.datum asc),
v.ID, v.Datum, v.TicketNr, 
v.Status from Ticketverlauf v
)

select *,
(Select top 1 Datum 
from erg e2 
    where e2.TicketNr = e1.TicketNr  
    and e2.Nummer = e1.Nummer -1
    ) as letzterEintrag
 from erg e1

Der zweite Teil der CTE sucht jeweils eine Übereinstimmung der TicketNr, aber immer die Zeile mit einer um 1 niedrigeren Nummer. Die Abfrage liefert nun das gewünschte Ergebnis

image

Kommentare (1) -

Kommentar schreiben