Erstellen von Gruppen von aufeinander folgenden Tagen, die ein bestimmtes Kriterium erfüllen

In dieser Antwort nehme ich an, dass das "id" -Feld die Zeilen fortlaufend nummeriert, wenn sie nach ansteigendem Datum sortiert werden, wie dies in den Beispieldaten der Fall ist. (Eine solche Spalte kann erstellt werden, wenn sie nicht existiert.)

Dies ist ein Beispiel für eine Technik beschrieben hier und hier .

1) Verknüpfen Sie die Tabelle mit sich selbst an benachbarten "ID" -Werten. Dies paart benachbarte Reihen. Wählen Sie Zeilen, in denen sich das Feld "Zuordnung" geändert hat. Speichern Sie das Ergebnis in einer temporären Tabelle und führen Sie dabei einen laufenden Index.

Dies gibt Ihnen eine Tabelle mit "dem Ende der vorherigen Periode", "dem Beginn der nächsten Periode" und "dem Wert der 'Zuteilung' in der vorherigen Periode" in jeder Zeile:

2) Wir brauchen den Anfang und das Ende jeder Periode in derselben Zeile, also müssen wir benachbarte Zeilen erneut kombinieren. Erstellen Sie dazu eine zweite temporäre Tabelle wie boundaries , aber ein idx Feld 1 größer:

Fügen Sie jetzt das idx -Feld hinzu und wir erhalten die Antwort:

** Beachten Sie, dass diese Antwort die "internen" Perioden korrekt erfasst, aber die zwei "Kanten" -Perioden, bei denen die Zuweisung = 0 zu Beginn und die Zuweisung = 5 am Ende ist, nicht berücksichtigt. Diese können mit UNION -Klauseln eingezogen werden, aber ich wollte die Kernidee ohne diese Komplikation darstellen.

Folgendes wäre eine Möglichkeit, es zu tun. Der Kern dieser Lösung ist

• Verwenden Sie CTE , um eine Liste aller aufeinanderfolgenden Start- und Enddaten mit Allocation = 0 zu erhalten.
• Verwenden Sie die Funktion ROW_NUMBER window, um Zeilennummern abhängig vom Start- und Enddatum zuzuweisen.
• Wählen Sie nur die Datensätze aus, in denen ROW_NUMBERS gleich 1 ist.
• Verwenden Sie DATEDIFF , um DayCount zu berechnen.

SQL-Anweisung

Testskript

Alternativer Weg mit CTE aber ohne ROW_NUMBER (),

Beispieldaten:

Abfrage:

Live-Beispiel unter data.SE .

Verwenden dieser Beispieldaten:

Versuchen Sie Folgendes:

Der erste Abschnitt der Abfrage ist ein rekursives SELECT, das von allen Zeilen mit Zuordnung = 0 verankert ist und dessen vorheriger Tag entweder nicht existiert oder die Zuweisung! = 0 hat. Dies gibt effektiv IDs zurück: 1 und 3 Dies sind die Anfangsdaten der Zeiträume, die Sie zurückgeben möchten.

Der rekursive Teil dieser Abfrage beginnt mit den Ankerzeilen und findet alle nachfolgenden Daten, für die auch die Zuweisung = 0 ist. Die SeedID verfolgt die verankerte ID über alle Iterationen hinweg.

Das Ergebnis ist bisher:

Die nächste Unterabfrage verwendet ein einfaches GROUP BY, um alle Startdaten für jede SeedID herauszufiltern, und zählt auch die Tage.

Die letzte Unterabfrage macht dasselbe mit den Enddaten, aber dieses Mal ist die Anzahl der Tage nicht erforderlich, da wir diese bereits haben.

Die letzte SELECT-Abfrage verbindet diese beiden zusammen, um das Start- und das Enddatum zu kombinieren, und gibt sie zusammen mit der Anzahl der Tage zurück.

Probieren Sie es aus, wenn es für Sie funktioniert Hier bleibt SDATE für Ihr DATUM gleich wie Ihr Tisch.

Eine Lösung ohne CTE:

Beispiel