Textsatz mit \LaTeX, Programmieren, Zahlen, etc.
In this final article of this series I will add another data mart. In the models/marts subfolder we add fx_current_rates.sql with the following SQL code:
{{ config(materialized='view') }} SELECT currency, valid_from, rate_vs_eur FROM {{ REF('fx_rates_with_validity') }} WHERE is_current = TRUE |
This will be a new view on top of the „fx_rates_with_validity“ mart. As this view ‚materializes‘ in the Postgres DB, we can use HeidiSQL to retrieve data from it:
In this article we will continue our dbt journey. With dbt-core installed we can ask dbt to set up a new project for us.
Simply go to the root folder where your project folder shall be created and run dbt init projectname. dbt will then ask you for the database adapter and the connection settings as well as for the schema dbt is to build its objects in.
After these questions and answers dbt tells you that you may run dbt debug now to check if everything is fine, BUT: you need to switch first to the subdirectory you created otherwise there will be a meaningless error message:
Encountered an error:
Internal Error
Profile should not be None if loading profile completed
If run in the correct subdirectory dbt debug should not produce any errors. If it does, run the error message through the search engine of your choice.
C:\Users\UWe\Desktop\fx_project>dbt debug 16:52:20 Running with dbt=1.10.13 16:52:20 dbt version: 1.10.13 16:52:20 python version: 3.12.6 16:52:20 python path: C:\Python312\python.exe 16:52:20 os info: Windows-11-10.0.26200-SP0 16:52:20 Using profiles dir at C:\Users\UWe\.dbt 16:52:20 Using profiles.yml file at C:\Users\UWe\.dbt\profiles.yml 16:52:20 Using dbt_project.yml file at C:\Users\UWe\Desktop\fx_project\dbt_project.yml 16:52:20 adapter type: postgres 16:52:20 adapter version: 1.9.1 16:52:20 Configuration: 16:52:20 profiles.yml file [OK found and valid] 16:52:20 dbt_project.yml file [OK found and valid] 16:52:20 Required dependencies: 16:52:20 - git [OK found] 16:52:20 Connection: 16:52:20 host: localhost 16:52:20 port: 5432 16:52:20 user: postgres 16:52:20 database: FX 16:52:20 schema: dbt 16:52:20 connect_timeout: 10 16:52:20 role: None 16:52:20 search_path: None 16:52:20 keepalives_idle: 0 16:52:20 sslmode: None 16:52:20 sslcert: None 16:52:20 sslkey: None 16:52:20 sslrootcert: None 16:52:20 application_name: dbt 16:52:20 retries: 1 16:52:20 Registered adapter: postgres=1.9.1 16:52:20 Connection test: [OK connection ok] 16:52:20 All checks passed!
In the project folder you find a set of subfolders, remove the models/example subfolder, as it may cause error messages.
Next we will create some basic models, using YAML files.
In models, create sources.yml with the following content:
version: 2 sources: - name: raw schema: raw tables: - name: ecb_fx_rates |
In models, create a subfolder staging and add stg_fx_rates.sql to this staging subfolder with the following content:
{{ config(materialized='view') }} SELECT fx_date, currency, rate, created_at FROM {{ SOURCE('raw', 'ecb_fx_rates') }} |
This will simply select all the data we have in our raw DB schema.
Next comes is the interesting part. Each row gets a valid_from and valid_to date using a window function.
In models, create a subfolder marts and add fx_rates_with_validity.sql to this marts subfolder with the following content:
{{ config(materialized='table') }} WITH base AS ( SELECT fx_date, currency, rate FROM {{ REF('stg_fx_rates') }} ), with_validity AS ( SELECT currency, fx_date AS valid_from, -- valid_to is the day before the next rate, or null if it's the latest lead(fx_date) OVER ( partition BY currency ORDER BY fx_date ) - INTERVAL '1 day' AS valid_to, rate, -- flag the currently active rate CASE WHEN lead(fx_date) OVER ( partition BY currency ORDER BY fx_date ) IS NULL THEN TRUE ELSE FALSE END AS is_current FROM base ) SELECT * FROM with_validity ORDER BY currency, valid_from |
This will give uzs data as the following
| currency | valid_from | valid_to | rate_vs_eur | is_current |
|---|---|---|---|---|
| USD | 2025-01-02 | 2025-01-02 | 1.0312 | false |
| USD | 2025-01-03 | 2025-01-05 | 1.0345 | false |
| USD | 2025-01-04 | null | 1.0821 | true |
It also makes sense now to add some data checks.
In models, create schema.yml with the following content:
version: 2 models: - name: stg_fx_rates columns: - name: rate_date tests: [not_null] - name: currency tests: [not_null] - name: rate_vs_eur tests: [not_null] - name: fx_rates_with_validity columns: - name: currency tests: [not_null] - name: valid_from tests: [not_null] - name: is_current tests: [not_null] |
We can now run the whole setup:
dbt run dbt test dbt docs generate dbt docs serve
Going forward
Since ECB publishes the latest rate every data at around 16:00 CET the Python job to fetch them should be run each day (apparently after 16:00 CET). Once the raw database is updated you can update the dbt models:
dbt run --select stg_fx_rates fx_rates_with_validity dbt test --select stg_fx_rates fx_rates_with_validity
In the final part of the series we will add another data market for the latest rates.
I have recently found the necessity to deal with dbt and Snowflake. As I learn best by practically doing something I decided to write a few practical use cases. Privately I will focus on Postgres SQL as I am not sure about Snowflake’s free tier. What is this dbt? dbt (data build tool) is a commandline tool based on Python that take care of the „T“ in „ETL“, the transformation of data in a data warehouse using SQL. dbt does not care about how you get the raw data into the (raw) DWH, this is not in its scope. dbt-core is available for free (Therefore I will focus on dbt-core, for production environments the paid tiers are most likely more suitable).
In this first example we will download the ECB fx rates from the European Central Bank and play a bit with them by transforming them and creating some data marts.
As mentioned I will use Postgres locally on my machine, so the installation of Postgres on my Windows machine was step 1. To deal with Postgres I can recommend HeidiSQL, does the trick.
Step 2 was the installation of dbt in Python. (Note that I had issues with Python 3.14, I downgraded to Python 3.12)
Besides dbt-core we need the postgres-Adapter dbt-postgres, so pip install dbt-core dbt-postgres helped. I usually do not need virtual environments, so I just installed it globally. We also need the Postgres library and lxml and requests for the ingestion of the XML file:
pip install requests psycopg2-binary lxml
As mentioned dbt does not care how you load the raw data, so with the help of some AI-tool I got the following Python Code to load the 90-days F/X rates file from the European Central Bank.
import requests import xml.etree.ElementTree as ET from datetime import datetime import psycopg2 from psycopg2.extras import execute_values ECB_URL = "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml" DB_CONFIG = { "host": "localhost", "port": 5432, "dbname": "FX", "user": "postgres", "password": "<meingeheimespasswort>", } def fetch_ecb_fx(): r = requests.get(ECB_URL, timeout=30) r.raise_for_status() root = ET.fromstring(r.content) ns = { "gesmes": "http://www.gesmes.org/xml/2002-08-01", "def": "http://www.ecb.int/vocabulary/2002-08-01/eurofxref", } rows = [] # Find Cube with time attribute for cube in root.findall(".//def:Cube[@time]", ns): fx_date = cube.attrib["time"] for rate in cube.findall("def:Cube", ns): rows.append( ( fx_date, rate.attrib["currency"], float(rate.attrib["rate"]), ) ) return rows def init_db(conn): with conn.cursor() as cur: cur.execute( """ CREATE SCHEMA IF NOT EXISTS raw; CREATE TABLE IF NOT EXISTS raw.ecb_fx_rates ( fx_date date NOT NULL, currency varchar(3) NOT NULL, rate numeric(20,10) NOT NULL, created_at timestamp default now(), PRIMARY KEY (fx_date, currency) ); """ ) conn.commit() def upsert_rates(conn, rows): sql = """ INSERT INTO raw.ecb_fx_rates (fx_date, currency, rate) VALUES %s ON CONFLICT (fx_date, currency) DO UPDATE SET rate = EXCLUDED.rate; """ with conn.cursor() as cur: execute_values(cur, sql, rows) conn.commit() def main(): rows = fetch_ecb_fx() print(f"Fetched {len(rows)} FX rates") conn = psycopg2.connect(**DB_CONFIG) try: init_db(conn) upsert_rates(conn, rows) print("Load complete") finally: conn.close() if __name__ == "__main__": main() |
When this code is run it should provide some output close to the following.
Fetched 1798 FX rates
Load complete
In the second part of this series we will take care of the dbt work.
Hier ein einfaches Beispiel, wie man PDF-Formulare mit dem hyperref Paket erstellen kann.
\documentclass{article} % Load hyperref with form support \usepackage{hyperref} \hypersetup{ pdfauthor={Your Name}, pdftitle={Sample Fillable PDF Form}, pdfcreator={LaTeX with hyperref} } \begin{document} \section*{Beispielformular} Bitte ausfüllen! \vspace{1em} \noindent \textbf{Name:}\\[0.5em] \TextField[ name=name, width=0.8\linewidth, bordercolor={0 0 0} ]{} \vspace{1em} \noindent \textbf{E-Mail:}\\[0.5em] \TextField[ name=email, width=0.8\linewidth, bordercolor={0 0 0} ]{} \vspace{1em} \noindent \textbf{Alter:}\\[0.5em] \TextField[ name=age, width=4cm]{} \vspace{1em} \noindent \textbf{Lieblingsfrucht:}\\[0.5em] \ChoiceMenu[ name=gender, width=4cm, combo ]{Wählen einen Eintrag: }{Apfel,Birne,Kirsche} \vspace{1em} \noindent \textbf{Newletter abonnieren?}\\[0.5em] \CheckBox[name=subscribe]{} Ja \vspace{1.5em} \noindent \textbf{Anmerkungen:}\\[0.5em] \TextField[ name=comments, width=\linewidth, height=3cm, multiline=true ]{} \vspace{1.5em} \noindent\PushButton[ name=print, onclick={this.print({bUI:true,bSilent:false,bShrinkToFit:true});}, bordercolor={0 0 0}, width=4cm, height=1cm ]{Print Form} \end{document} |
Ausgehend von einer Anfrage auf der Dante Beraterkreis Mailingliste hier einige Ideen, wie man pixelbasiert PDF-Dateien miteinander vergleichen kann.
Schritt 1: Umwandlung ins PNG Format. Ich nutze dazu pdftoppm aus dem Poppler-Paket.
pdftoppm -png 1.pdf > 1.png
das gleiche dann nochmal für die zweite Datei.
Dann mit ein wenig Python-Magie die beiden PNGs vergleichen:
from PIL import Image, ImageChops # generic import numpy as np # numerical from skimage.metrics import structural_similarity as ssim import cv2 # Load the images img1 = Image.open("1.png") img2 = Image.open("2.png") # Compute absolute difference diff = ImageChops.difference(img1, img2) # Save or show the difference image diff.save("difference.png") diff.show() # Load PNGs into numpy arrays img1 = np.array(Image.open("1.png")) img2 = np.array(Image.open("2.png")) # Compute pixel-wise absolute difference diff = np.abs(img1.astype(int) - img2.astype(int)) # Save result Image.fromarray(diff.astype(np.uint8)).save("difference.png") # Or compute some stats: print("Mean difference:", diff.mean()) print("Max difference:", diff.max()) # Convert to grayscale gray1 = cv2.cvtColor(img1, cv2.COLOR_BGR2GRAY) gray2 = cv2.cvtColor(img2, cv2.COLOR_BGR2GRAY) score, diff = ssim(gray1, gray2, full=True) print("SSIM:", score) # diff is in [0,1]; scale to 0-255 to view as an image diff = (diff * 255).astype("uint8") cv2.imwrite("ssim_diff.png", diff) |
Hier ein Beispiel, wie man mit TikZ einen Aktenordner in TikZ zeichnen kann.
\documentclass[tikz,border=5pt]{standalone} \begin{document} \begin{tikzpicture}[line join=round, line cap=round] % ------------------------------ % Parameter % ------------------------------ \def\ordnerwidth{2.5} % Rückenbreite des Ordners \def\ordnerheight{11} % Höhe des Ordners % Abgeleitete Maße \pgfmathsetmacro{\lochdiam}{0.7*\ordnerwidth} \pgfmathsetmacro{\labelwidth}{0.8*\ordnerwidth} \pgfmathsetmacro{\lochx}{0.5*\ordnerwidth} \pgfmathsetmacro{\labelx}{0.5*(\ordnerwidth-\labelwidth)} % ------------------------------ % Zeichnung % ------------------------------ % Rücken \draw[fill=gray!20] (0,0) rectangle (\ordnerwidth,\ordnerheight); % Etikettfeld (mittig auf Rücken) \draw[fill=white] (\labelx,3) rectangle ++(\labelwidth,7); \foreach \y in {8.5,8,7.5,7,6.5} { \draw (\labelx+0.2,\y) -- (\labelx+\labelwidth-0.2,\y); } % Griffloch (mittig unten am Rücken) \draw[fill=black!40] (\lochx,1.2) circle (\lochdiam/2); \draw[fill=white] (\lochx,1.2) circle (\lochdiam/2.2); \end{tikzpicture} \end{document} |
Und hier der Code für den Ordner in 3D:
\documentclass[tikz,border=5pt]{standalone} \begin{document} \begin{tikzpicture}[line join=round, line cap=round] % ------------------------------ % Parameter % ------------------------------ \def\ordnerwidth{2.5} % Rückenbreite des Ordners \def\ordnerheight{10} % Höhe \def\depth{2} % Tiefe in die Perspektive % Abgeleitete Maße \pgfmathsetmacro{\lochdiam}{0.8*\ordnerwidth} \pgfmathsetmacro{\labelwidth}{0.8*\ordnerwidth} \pgfmathsetmacro{\lochx}{0.5*\ordnerwidth} \pgfmathsetmacro{\labelx}{0.5*(\ordnerwidth-\labelwidth)} % ------------------------------ % Zeichnung % ------------------------------ % Rückseite (Rücken sichtbar) \draw[fill=gray!20] (0,0) -- (\ordnerwidth,0) -- (\ordnerwidth,\ordnerheight) -- (0,\ordnerheight) -- cycle; % Seitenfläche \draw[fill=gray!10] (\ordnerwidth,0) -- (\ordnerwidth+\depth,0.6) -- (\ordnerwidth+\depth,\ordnerheight+0.6) -- (\ordnerwidth,\ordnerheight) -- cycle; % Oberseite \draw[fill=gray!30] (0,\ordnerheight) -- (\ordnerwidth,\ordnerheight) -- (\ordnerwidth+\depth,\ordnerheight+0.6) -- (\depth,\ordnerheight+0.6) -- cycle; % Etikettfeld (mittig auf Rücken) \draw[fill=white] (\labelx,6) rectangle ++(\labelwidth,3); \foreach \y in {8.5,8,7.5,7,6.5} { \draw (\labelx+0.2,\y) -- (\labelx+\labelwidth-0.2,\y); } % Griffloch (mittig unten am Rücken) \shade[ball color=black!50] (\lochx,1.2) circle (\lochdiam/2); \fill[white] (\lochx,1.2) circle (\lochdiam/2.5); % Umrandung \draw[thick] (0,0) -- (\ordnerwidth,0) -- (\ordnerwidth+\depth,0.6) -- (\ordnerwidth+\depth,\ordnerheight+0.6) -- (\depth,\ordnerheight+0.6) -- (0,\ordnerheight) -- cycle; \end{tikzpicture} \end{document} |
Hier ein Versuch, ein Maths bzw. Abacus Eurorack Modul mit TikZ zu zeichnen.
%!TEX TS-program = pdflatex \documentclass[12pt,ngerman,tikz]{standalone} \begin{document} \begin{tikzpicture}[red,draw=red, fill=red] % a plug \newcommand{\plug}[2]{% three circles, inner one is filled \fill (#1,#2) circle (0.1875cm); \draw (#1,#2) circle (0.28cm); \draw (#1,#2) circle (0.42cm); } % buttons \newcommand{\button}[2]{% three circles, inner one is filled \fill (#1,#2) circle (0.5cm); \fill[white] (#1,#2) circle (0.4cm); } % a plug with a label above \newcommand{\labelplug}[3]{% \fill (#1,#2) circle (0.1875cm); \draw (#1,#2) circle (0.28cm); \draw (#1,#2) circle (0.42cm); \node[above=0.35cm,font=\sffamily\bfseries\scriptsize] at (#1,+#2) {#3}; } % \knob{x}{y}{size}{angle} \newcommand{\knob}[4]{% \pgfmathsetmacro{\radius}{#3}% \pgfmathsetmacro{\reduced}{\radius * 0.8}% \pgfmathsetmacro{\tikzAngle}{90 - #4}% \draw[thick, fill=white!30] (#1,#2) circle (\radius cm); \draw[line width=2pt, red] (#1,#2) -- ++(\tikzAngle:\reduced cm); } % plug with label and led, in a frame \newcommand{\labelplugled}[3]{% \fill (#1,#2) circle (0.1875cm); \draw (#1,#2) circle (0.28cm); \draw (#1,#2) circle (0.42cm); \node[above=0.35cm,font=\sffamily\bfseries\scriptsize] at (#1,#2) {#3}; \draw[rounded corners=2pt] (#1-0.5, #2-0.5) rectangle (#1+0.5, {#2+1.2}); \draw (#1,#2+0.945) circle (0.1cm); } % plug with label but no led, in a frame \newcommand{\labelplugnoled}[3]{% \fill (#1,#2) circle (0.1875cm); \draw (#1,#2) circle (0.28cm); \draw (#1,#2) circle (0.42cm); \node[above=0.35cm,font=\sffamily\bfseries\scriptsize] at (#1,#2) {#3}; \draw[rounded corners=2pt] (#1-0.5, #2-0.5) rectangle (#1+0.5, {#2+1.2}); } % Screenshot from the manual, in the background %\node[anchor=north west ,inner sep=0] (frame1) at (1,13) {\includegraphics[width=10cm]{2025-09-20 04 09 06.png}}; % grid \draw[help lines,red] (0,0) grid (13,14); \draw[very thick, rounded corners=2pt](1,0.35) rectangle (11,13); \labelplug{1.75}{11.5}{IN 1}; \labelplug{2.85}{11.5}{TRIG 1}; \labelplug{5.45}{11.5}{IN 2}; \labelplug{6.55}{11.5}{IN 3}; \labelplug{9.175}{11.5}{TRIG 4}; \labelplug{10.25}{11.5}{IN 4}; \plug{1.75}{10.2}; \plug{1.75}{9}; \plug{1.75}{7.9}; \plug{1.75}{6.55}; \plug{10.25}{10.2}; \plug{10.25}{9}; \plug{10.25}{7.9}; \plug{10.25}{6.55}; \labelplugled{1.75}{1.8}{EOR}; \labelplugled{2.9}{1.8}{$\smallint$}; \labelplugnoled{4.9}{1.8}{OR}; \labelplugled{6}{1.8}{SUM}; \labelplugnoled{7.1}{1.8}{INV}; \labelplugled{9.14}{1.8}{$\smallint$}; \labelplugled{10.25}{1.8}{EOC}; \labelplugnoled{4.25}{3.6}{OUT 1}; \labelplugnoled{5.4}{3.6}{OUT 2}; \labelplugnoled{6.55}{3.6}{OUT 3}; \labelplugnoled{7.7}{3.6}{OUT 4}; \knob{3.5}{10.2}{0.7}{0} \knob{8.5}{10.2}{0.7}{0} \knob{3.5}{7.9}{0.7}{0} \knob{8.5}{7.9}{0.7}{0} \knob{3.5}{5.7}{0.7}{0} \knob{8.5}{5.7}{0.7}{0} \knob{5}{9}{0.35}{0} \knob{7}{9}{0.35}{0} \knob{5}{6.75}{0.35}{0} \knob{7}{6.75}{0.35}{0} \button{2.3}{4.15} \button{9.7}{4.15} \node at (5,9.6){\sffamily\bfseries\footnotesize1}; \node at (7,9.6){\sffamily\bfseries\footnotesize4}; \node at (5,7.35){\sffamily\bfseries\footnotesize2}; \node at (7,7.35){\sffamily\bfseries\footnotesize4}; \node at (4.75,8.5){\sffamily\bfseries--}; \node at (5.3,8.5){\sffamily\bfseries+}; \node at (4.75,6.25){\sffamily\bfseries--}; \node at (5.3,6.25){\sffamily\bfseries+}; \node at (6.75,8.5){\sffamily\bfseries--}; \node at (7.3,8.5){\sffamily\bfseries+}; \node at (6.75,6.25){\sffamily\bfseries--}; \node at (7.3,6.25){\sffamily\bfseries+}; \draw[very thick] (3.4,11.5) -- (3.95,11.5) -- (5,10.5) -- (5,9.8); \draw[very thick] (8.7,11.5) -- (8.1,11.5) -- (7,10.5) -- (7,9.8); \draw[very thick] (5.45,10.85) -- (5.7,10.6) -- (5.7,7.5) -- (5.3,7.1); \draw[very thick] (6.55,10.85) -- (6.3,10.6) -- (6.3,7.5) -- (6.7,7.1); \foreach \x in {0,1,2,3,4,5,6,7,8,9,10,11,12} { \node(a) at (\x,-0.5){\x};} \foreach \y in {0,1,2,3,4,5,6,7,8,9,10,11,12,13} { \node(a) at (0.5,\y){\y};} \end{tikzpicture} \end{document} |
Mit dem isosigns Paket kann man in LaTeX Zeichen aus den ISO Standards 3864, 7010 und 7001 setzen.
Link zur Doku: https://ctan.org/pkg/isosigns
Hier ein Screenshot aus der Dokumentation:
Excel 365 aus Office365 hat eine Reihe neuer Funktionen, die ich auch noch nicht kannte. Hier eine Liste, die mir ChatGPT ausgespuckt hat.
| Name (Englisch) | Erläuterung |
|---|---|
| TEXTKETTE (TEXTJOIN) | Verbindet Text aus mehreren Zellen mit einem Trennzeichen. Unterstützt auch Zellbereiche. |
| TEXTVERKETTEN (CONCAT) | Ähnlich wie VERKETTEN, aber flexibler und moderner. |
| WENNS (IFS) | Ersetzt verschachtelte WENN-Funktionen durch eine klarere Syntax. |
| MAXWENNS (MAXIFS) | Gibt den größten Wert zurück, der bestimmte Kriterien erfüllt. |
| MINWENNS (MINIFS) | Gibt den kleinsten Wert zurück, der bestimmte Kriterien erfüllt. |
| ERSTERWERT (SWITCH) | Gibt einen Wert basierend auf dem ersten zutreffenden Vergleich zurück. |
| FILTER | Filtert Daten dynamisch basierend auf Bedingungen. |
| SORTIEREN (SORT) | Sortiert Daten dynamisch. |
| SORTIERENNACH (SORTBY) | Sortiert Daten basierend auf einem anderen Bereich. |
| EINDEUTIG (UNIQUE) | Gibt eindeutige Werte aus einem Bereich zurück. |
| SEQUENZ (SEQUENCE) | Erstellt eine Liste von Zahlen in einer Sequenz. |
| WAHL.ZEILE (XLOOKUP) | Moderner Ersatz für SVERWEIS und WVERWEIS. |
| WAHL.BEREICH (XMATCH) | Gibt die Position eines Werts in einem Bereich zurück. |
| LET | Ermöglicht das Definieren von Variablen innerhalb einer Formel. |
| LAMBDA | Erstellt benutzerdefinierte Funktionen direkt in Excel. |
| TEXTSPLIT | Teilt Text anhand eines Trennzeichens in mehrere Zellen auf. |
| TEXTBEFORE / TEXTAFTER | Gibt Text vor oder nach einem bestimmten Zeichen zurück. |
| VSTACK / HSTACK | Stapelt Zellbereiche vertikal oder horizontal. |
| WRAPROWS / WRAPCOLS | Wandelt eine Liste in ein Array mit mehreren Zeilen oder Spalten um. |
| TAKE / DROP | Gibt die ersten/letzten Elemente eines Bereichs zurück oder entfernt sie. |
| EXPAND | Erweitert einen Bereich auf eine bestimmte Größe. |
| TOCOL / TOROW | Wandelt einen Bereich in eine Spalte oder Zeile um. |
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.