uweziegenhagen.de

Ich halte mich schon recht erfahren im Umgang SQL, kürzlich bin ich aber an einer Ecke von SQL vorbeigekommen, die ich auch noch nicht kannte. Dabei handelt es sind um die „OUTER APPLY“ bzw. „CROSS APPLY“ Operatoren.

Um die Lösung herzuleiten betrachten wir zuerst das Problem:

Gegeben seien Kreditverträge mit verschiedenen Zahlplänen. Ein Vertrag zahlt vielleicht monatlich, einer zahlt quartalsweise. Ein Vertrag hat dabei ein Startdatum und ein Enddatum sowie verschiedene Cashflows.

Hier ein paar Testdaten mit den entsprechenden Tabellen, die dazugehörigen SQL-Statements folgen später.

Cashflows

Cashflows

Das Problem ist jetzt, wie kann man eine monatliche Übersicht pro Vertrag bekommen, bei der in den Monaten, wo es kein Cashflow gab, einfach der letzte Cashflow angezeigt wird? Das Endergebnis soll wie folgt aussehen:

Im nächsten Teil fangen wir dann mit den SQL Statements an.

Wir kennen jetzt das Problem, nun können wir uns an die Lösung wagen! In diesem Blogbeitrag werden wir alles vorbereiten, was wir für das anschließende „Forward Filling“ der Daten brauchen.

Im ersten Schritt bestimmen wir für die einzelnen Verträge die Monatsendwerte von Start des jeweiligen Vertrags bis zu dessen Ende. Dazu nutze ich den den folgenden rekursiven CTE (Common Table Expression)

WITH MonthSequence AS (
    SELECT
        c.ContNo,
        EOMONTH(c.StartDate) AS MonthEnd,
        c.EndDate
    FROM Contracts c
    UNION ALL
    SELECT
        ms.ContNo,
        EOMONTH(DATEADD(MONTH, 1, ms.MonthEnd)),
        ms.EndDate
    FROM MonthSequence ms
    WHERE EOMONTH(DATEADD(MONTH, 1, ms.MonthEnd)) <= ms.EndDate
)

Das erzeugt uns die folgende Sequenz:

Als nächstes suchen wir uns die End-of-Month der Cashflows raus, die wir im nächsten Schritt mit den End-of-Month aus Schritt 1 kombinieren werden.

WITH CashflowBuckets AS (
    SELECT
        ContNo,
        EOMONTH(CashflowDate) AS MonthEnd,
        Amount
    FROM Cashflows
)
SELECT * FROM CashflowBuckets

Die Kombination ist dann recht einfach und gibt uns die Liste aller End-of-Months aus mit den dazugehörigen Cashflows.

WITH MonthSequence AS (
    SELECT
        c.ContNo,
        EOMONTH(c.StartDate) AS MonthEnd,
        c.EndDate
    FROM Contracts c
    UNION ALL
    SELECT
        ms.ContNo,
        EOMONTH(DATEADD(MONTH, 1, ms.MonthEnd)),
        ms.EndDate
    FROM MonthSequence ms
    WHERE EOMONTH(DATEADD(MONTH, 1, ms.MonthEnd)) <= ms.EndDate
)
,CashflowBuckets AS (
    SELECT
        ContNo,
        EOMONTH(CashflowDate) AS MonthEnd,
        Amount
    FROM Cashflows
)
,Combined AS (
   SELECT
        ms.ContNo,
        ms.MonthEnd,
        cb.Amount
    FROM MonthSequence ms
    LEFT JOIN CashflowBuckets cb
        ON ms.ContNo = cb.ContNo
        AND ms.MonthEnd = cb.MonthEnd
)
 
SELECT * FROM Combined

In gelb sind die Einträge markiert, die wir im finalen Schritt mit den jeweils letzten gültigen Cashflow-Werten befüllen müssen.

Schauen wir uns das Forward Filling an. Je nach Version des SQL Servers funktioniert eine Version oder leider nicht. 🙂

Mit SQL Server 2022 kann man den folgenden Code nutzen, um einen weiteren CTE zu bauen:

Filled AS (
    SELECT
        ContNo,
        MonthEnd,
        Amount,
        last_value(Amount) IGNORE NULLS
        OVER (
            PARTITION BY ContNo
            ORDER BY MonthEnd
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS ForwardFilledAmount
    FROM Combined
)
SELECT * FROM Filled

Versionen von SQL Server vor 2022 unterstützen leider „ignore nulls“ nicht, hier kann man dann folgenden Code nutzen:

Filled AS (
    SELECT
        c.ContNo,
        c.MonthEnd,
        c.Amount,
        ca.ForwardFilledAmount
    FROM Combined c
    OUTER APPLY (
        SELECT TOP 1 Amount AS ForwardFilledAmount
        FROM Combined c2
        WHERE c2.ContNo = c.ContNo
          AND c2.MonthEnd <= c.MonthEnd
          AND c2.Amount IS NOT NULL
        ORDER BY c2.MonthEnd DESC
    ) ca
)
SELECT * FROM Filled

OUTER APPLY funktioniert dabei so ähnlich wie ein LEFT JOIN. Der Unterschied besteht darin, dass beim OUTER APPLY die rechte Seite von der linken Seite abhängt. In einem weiteren Artikel zum Thema werde ich noch ein paar Beispiele dazu zeigen. CROSS APPLY ist ähnlich, hier ist es aber kein Äquivalent zum LEFT JOIN, sondern zum INNER JOIN. Auch dazu mehr in einem der weiteren Artikel.