Cross Join oder Right Join nur wie?

[IchHH]

Moin, ich habe da mal eine Frage...

stellt euch vor ihr habt 3 Bankkonten auf den an unterschiedlichen Tagen Buchungen drauf erfolgen. Mal auf einem Konto mal auf einem anderen Konto und manchmal auch auf allen dreien.

Wenn ich nun eine Stichtagsanalyse machen möchte, so würde ich mit zum Beispiel den heutigen Tag nehmen und schauen, wie ist der Kontostand auf alle drei Konten. Für mich ist das Logisch, auf der Datenbank sieht es jedoch ein wenig anders aus. Dort sind nur die Bewegungen drauf. Also Anfangsbestand +/- Veränderungen an den jeweiligen Buchungstagen. Damit habe ich also nicht immer ein Stand vom jeden Konto zum heutigen Tag.

	KTO 1	KTO 2	KTO 3
31.01.	5	7
01.02.	10	-5	3
02.02.	2
03.02		3	4
04.02	1		5
Endbestand

Erreichen möchte ich dass ich zu jeden Tag den Endbestand sehe, auch wenn dort keine Buchung erfolgt. Das würde ich mittels LAST_VALUE erreichen. Mit fehlt aber dazu die Anreicherung der Datumsfelder zu jeden Konto da in der Datenbanktabelle schließlich nur steht

KTO	Value	Datum
1	5	31.01.
1	10	01.02.
1	2	02.02.
1	1	04.02.
2	7	31.01.
2	-5	01.02.
2	3	03.02.
3	3	01.02.
3	4	03.02.
3	5	04.02.

Was also würde ich tun?!

In der Theorie würde ich versuchen jedem Konto die fehlenden Tage hinzuzufügen. Nur wie? Ein Cross Join sorgt dafür das jedes Datum mit jeden Tag aus der Datumstabelle "angereichert" wird, was dafür sorgt, dass ich für jede Buchung alle Tage bekomme, mal ganz abgesehen von der Menge ist das auch ein Performance Problem. Wenn ich ein Right Join machen habe ich das Problem das er das nicht auf jeden Konto anwendet.

Wie also setzt man so etwas um wenn man das in einer View und nicht in ein SP erledigen möchte? Oder geht das nur mit einem SP?

 

[dabadepdu]

Du nimmst eine Tabelle oder generierte Daten mit den Tagen, die Du wissen willst und machst einen Left Join auf die Bankdaten über das Datumsfeld.
Die Datumswerte kannst du so anlegen, wie Du sie brauchst, statisch, generiert, wie auch immer.
select .. from <datumswerte> d left join <bankdaten> b on d.datum= b.datum

Lücken, die durch fehlende Daten entstehen, kannst mit Case usw. oder gar nicht behandeln.

Was mir an den Beispieldaten nicht ganz klar ist, hast Du die obere oder die untere Variante? Also Konten nebeneinander (kein Handlungsbedarf) oder untereinander? Im 2. Fall musst Du je Konto(typ) 1-3 einen join dazu machen.

 

[Kampfgummibaerlie]

wäre auch über eine rekursive abfrage möglich, um die fehlenden Tage zu generieren.

arbeite vollständig ohne gegebenen Tabellen, habe die Grundidee denke ich eingebaut:

with recursive a(konto, betrag, datum) as (
select 1, '100'::numeric, current_date
union all
select konto, betrag, datum+1 from a 
where datum < '2023-06-30'::date
)
select konto, sum(betrag), max(datum) from a group by konto, datum order by datum;

 

[IchHH]

Danke euch beiden,

@Kampfgummibaerlie dein Code läuft bei mir garnicht, bekomme nur Fehlermeldung.

@dabadepdu wie meinst du das, ich müssen das für des Konto machen? Es sind über 30 Stück! Ich bezog meine Anfrage auf die zweite Darstellung. Wie wäre nun die Vorgehensweise? Müsste ich erst alle Konten aus der Tabelle rausfiltern um Sie dann einzeln mit der Datumstabelle zu verbinden um Sie dann wieder per Union zusammenzuführen?

 

[IchHH]

@dabadepdu magst du mir noch sagen wie ich das umsetze? Ich habe zwar eine Lösung im Netz gefunden, nur verstanden habe ich Sie nicht, weshalb ich das nicht auf meinem Model umsetzen kann.

 

[castorp]

Kampfgummibaerli hat die Syntax für Postgres verwendet.

Die Idee ist eine Liste von Datumswerten für ein bestimmtest Interval zu generieren, diese kann dann mit einem outer join mit der KONTO Tabelle verbunden wäre.

Für SQL Server müsste das wohl so aussehen

with datums_liste(datum) as (
  select cast('20230601' as date)
  union all
  select dateadd(day, 1, datum)
  from datums_liste 
  where datum < '20230630'
)
select dl.datum, k.*
from datums_liste dl
  left join konto k on dl.datum = k.datum
order by dl.datum;

 

[IchHH]

Ok, das Skript verstehe ich, die Vorgehensweise nicht. Wenn man eine Datumstabelle hat in der alle benötigen Werte enthalten sind wofür dann noch eine erzeugen? In meinem Fall sieht die Datumstabelle wie folgt aus:

SELECT DISTINCT TheLastOfWeek, TheLastOfWeek as TheDate2, ISOweek, TheISOYear FROM [dbo].[t_SQL_Datum]
        WHERE TheDate2 <= GetDate()
        AND Year(TheDate2) >= YEAR(GetDate())-3

Hier ein Auszug des Ergebnisses

TheLastOfWeek	TheDate2	ISOweek	TheISOYear
2020-01-05	2020-01-05	1	2020
2020-01-12	2020-01-12	2	2020
2020-01-19	2020-01-19	3	2020
2020-01-26	2020-01-26	4	2020
2020-02-02	2020-02-02	5	2020

Wenn diese Tabelle mittels Left Join auf die Datentabelle trifft und die mit dem Datum verbunden werden, so werden nur die Angezeigt, wo die Werte identisch sind und weniger die Werte pro Konto hinzugefügt. (siehe die zweite Tabelle oben). Oder übersehe ich was?

 

[castorp]

Hmm, in der Tabelle stehen doch nur die Wochenanfänge drin, nicht alle Tage jeden Monats

 

[IchHH]

Das stimmt. Das reicht mir aber an Daten. Da ich auch in der Datentabelle auf die Wochenanfänge aggregiert. Meine Frage ist nur, wie erreiche ich es nun dass für jedes Konto diese Datumstabelle angewandt wird?

 

[IchHH]

Ich habe eine Lösung gefunden, habe nun aber das Problem, die Lücken mit Werten zu füllen, weshalb ich darauf hoffe das ich mir sagen könnt was an meiner FIRST_VALUE Formel für den Wert "Value_new" falsch ist.

SELECT DISTINCT
Q.Kontonummer
,Q.TheLastOfWeek
,Q.TheISOYear
,Q.ISOWeek
,FIRST_VALUE(Value_new) OVER (Partition by Basis.Kontonummer, Basis.Währungsschlüssel, Basis.TheDate2 ORDER BY Basis.Kontonummer, Basis.TheLastOfWeek) Value_new
,FIRST_VALUE(Basis.Währungsschlüssel) IGNORE NULLS OVER (PARTITION BY Q.Kontonummer ORDER BY Q.Kontonummer, Q.TheLastOfWeek) AS Währungsschlüssel
FROM
(
SELECT DISTINCT
Kontonummer
,Datum.TheLastOfWeek
,Datum.ISOweek
,Datum.TheISOYear
FROM Basis
Cross Join Datum
) Q
Left Join Basis
 ON Q.TheLastOfWeek = Basis.TheLastOfWeek
AND Q.Kontonummer = Basis.Kontonummer

Order by Kontonummer, Q.TheLastOfWeek

Auszug meines Ergebnisses:

Kontonummer	TheLastOfWeek	TheISOYear	ISOWeek	Value_new	Währungsschlüssel
0011002600	2021-12-19	2021	50	545,54	CNY
0011002600	2021-12-26	2021	51	NULL	CNY
0011002600	2022-01-02	2021	52	115,25	CNY
0011002600	2022-01-09	2022	1	NULL	CNY
0011002600	2022-01-16	2022	2	NULL	CNY

Ich erwarte eigentlich das bei Value_new in der zweiten Zeile ebenfalls 545,54 steht und in der 4 (545,54+115,25)=660,79 ebenso in der 5 Zeile.

 

[ukulele]

Dort sind nur die Bewegungen drauf. Also Anfangsbestand +/- Veränderungen an den jeweiligen Buchungstagen. Damit habe ich also nicht immer ein Stand vom jeden Konto zum heutigen Tag.

	KTO 1	KTO 2	KTO 3
31.01.	5	7
01.02.	10	-5	3
02.02.	2
03.02		3	4
04.02	1		5
Endbestand

Das ist mir noch nicht ganz klar, wie genau sieht dein Anfangsbestand aus, ein eigener Datensatz z.B. zum Jahresanfang (EB-Wert oder so) oder hast du in jedem Datensatz eines jeden Tages entweder einen Anfangsbestand oder einen Endsaldo? Wenn Ersteres, gibt es auch einen Anfangsbestand 0 für alle Konten oder würde der Datensatz dann fehlen?

Gibt es maximal eine Kontobewegung pro Konto und Tag oder können das mehrere sein?

 

[IchHH]

@ukulele Ich habe pro Jahr ein Anfangsbestand der Gebucht wird. Deshalb gibt es kein Anfangsbestand von 0 sondern den Wert des Endbestandes vom Vorjahr pro Konto. Bewegungen kann es pro Tag mehrere geben. Ich habe diese jedoch schon Aggregiert in meiner Datenbasis enthalten. So dass nur ein Wert pro Tag, Konto und Währung rauskommt.

 

[dabadepdu]

@dabadepdu wie meinst du das, ich müssen das für des Konto machen? Es sind über 30 Stück!

Wenn es so viele sind (und vielleicht dynamisch), wäre eine Join Lösung pro Konto ungeeignet.

Hier ist ein Beispiel für lückenlose Datumswerte je Konto:

SQL Server 2022

| (No column name) | | :----------------| | Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) <br> Oct 8 2022 05:58:25 <br> Copyright (C) 2022 Microsoft Corporation<br> Express Edition (64-bit) on Windows Server 2019 Standard 10.0 \<X64> (Build 17763: ) (Hypervisor)<br> |

dbfiddle.uk

 

[IchHH]

@dabadepdu Mein Code sieht anders aus, aber er macht doch das selbe. Eine Lösung für die Aufsummierung fehlt mir nur noch.

 

[dabadepdu]

Mein Code sieht anders aus, aber er macht doch das selbe.

Naja, ich habe Deine Frage an mich -so wie zitiert- beantwortet.

Zu Deinem neuen Problem:
Wenn ich Dich richtig verstanden hab, ist das ein running total Problem.

sum(<lückenhafterwert-der-wachsen soll>) over (partition by <keywert> order by <reihenfolgewert>)[code]