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”.
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.
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:
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
