Outer Apply und Cross Apply in SQL nutzen – Die Datenmenge bauen
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:
| ContNo | MonthEnd | EndDate |
| 123 | 2025-01-31 | 2025-12-31 |
| 123 | 2025-02-28 | 2025-12-31 |
| 123 | 2025-03-31 | 2025-12-31 |
| 123 | 2025-04-30 | 2025-12-31 |
| 123 | 2025-05-31 | 2025-12-31 |
| 123 | 2025-06-30 | 2025-12-31 |
| 123 | 2025-07-31 | 2025-12-31 |
| 123 | 2025-08-31 | 2025-12-31 |
| 123 | 2025-09-30 | 2025-12-31 |
| 123 | 2025-10-31 | 2025-12-31 |
| 123 | 2025-11-30 | 2025-12-31 |
| 123 | 2025-12-31 | 2025-12-31 |
| 456 | 2024-01-31 | 2024-12-31 |
| 456 | 2024-02-29 | 2024-12-31 |
| 456 | 2024-03-31 | 2024-12-31 |
| 456 | 2024-04-30 | 2024-12-31 |
| 456 | 2024-05-31 | 2024-12-31 |
| 456 | 2024-06-30 | 2024-12-31 |
| 456 | 2024-07-31 | 2024-12-31 |
| 456 | 2024-08-31 | 2024-12-31 |
| 456 | 2024-09-30 | 2024-12-31 |
| 456 | 2024-10-31 | 2024-12-31 |
| 456 | 2024-11-30 | 2024-12-31 |
| 456 | 2024-12-31 | 2024-12-31 |
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 |
| ContNo | MonthEnd | Amount |
| 123 | 2025-01-31 | 100.00 |
| 123 | 2025-04-30 | 110.00 |
| 123 | 2025-07-31 | 105.00 |
| 123 | 2025-12-31 | 120.00 |
| 456 | 2024-01-31 | 100.00 |
| 456 | 2024-06-30 | 130.00 |
| 456 | 2024-12-31 | 101.00 |
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.
| ContNo | MonthEnd | Amount |
| 123 | 2025-01-31 | 100.00 |
| 456 | 2024-01-31 | 100.00 |
| 456 | 2024-02-29 | NULL |
| 456 | 2024-03-31 | NULL |
| 456 | 2024-04-30 | NULL |
| 456 | 2024-05-31 | NULL |
| 456 | 2024-06-30 | 130.00 |
| 456 | 2024-07-31 | NULL |
| 456 | 2024-08-31 | NULL |
| 456 | 2024-09-30 | NULL |
| 456 | 2024-10-31 | NULL |
| 456 | 2024-11-30 | NULL |
| 456 | 2024-12-31 | 101.00 |
| 123 | 2025-02-28 | NULL |
| 123 | 2025-03-31 | NULL |
| 123 | 2025-04-30 | 110.00 |
| 123 | 2025-05-31 | NULL |
| 123 | 2025-06-30 | NULL |
| 123 | 2025-07-31 | 105.00 |
| 123 | 2025-08-31 | NULL |
| 123 | 2025-09-30 | NULL |
| 123 | 2025-10-31 | NULL |
| 123 | 2025-11-30 | NULL |
| 123 | 2025-12-31 | 120.00 |