SQL nach selbst definiertem Jahr gruppieren?
Hi zusammen,
ich versuche eine Abfrage zu erstellen, in der ich nach Geschäftsjahr gruppiere.
Sagen wir mal das Geschäftsjahr geht immer vom 11.03 (2020) bis zum 11.02 (2021) des folge Jahres. Dann kann ich ja nicht einfach nur nach dem Jahr gruppieren, da ja praktisch 2 Jahre mit drin sind.
Hat da vielleicht jemand Ahnung wie man sowas umsetzt?
4 Antworten
Ich würde eine Tabelle GESCHAEFTSJAHR anlegen, in der alle Von-Bis-Daten eine ID oder einer eindeutigen Geschäftsjahresbezeichnung zugeordnet werden. Diese ID / eindeutige Bezeichnung würde ich in die Tabelle von dir eintragen (dann ist die Tabelle nicht mehr normalisiert) oder eine Prozedur / Funktion schreiben, die zu einem Datum die ID ausliest.
Auch wenn es gegen die Normalisierung steht, würde ich aus pragmatischer Sicht die Geschäftsjahres-ID in einer Spalte dazu schreiben.
Es ist auch nicht unüblich, da ein Auge zuzudrücken und zum Beispiel Buchungsdatum und Geschäftsjahr in einer Buchungsdatentabelle einzufügen, selbst wenn sich das Geschäftsjahr aus dem Buchungsdatum ableiten lässt.
Wenn die Aufgabe theoretischer Natur ist, könntest du mit CASE WHEN und EXTRACT versuchen zu arbeiten, mit denen du ein Datum sezieren kannst und in Abhängigkeit der Werte der Bestandteile entsprechend reagieren kannst.
Pseudocode:
Jahr(Datum)+(Wenn Monat(Datum)>=7 dann 1 sonst 0)
wird nix, weil du keine gruppen wie 2020-11-21 + 2021-02-10 irgendwie zusammen fassen kannst so das das auch über die andern jahre automatisch geht . und da nichtmal der timestamp irgenwelche regelmäßigkeiten über 4 jahre hat , ist das egal wie du es drehst so nicht zu machen .
Der Nachteil ist auch, dass man ziemlich unflexibel ist, falls sich die Geschäftsjahrlogik mal ändern sollte. Besser man definiert die Geschäftsjahre in einer separaten Tabelle.
den gedanken (Jahr(Datum)+) hatte ich auch, scheitert schon am jahr . das ändert sich nämlich auch .
Warum sollte das nicht gehen. Ich prüfe innerhalb eines CASE ob das Datum BETWEEN '01.01.' || Jahr(Datum) und 'XX.XX.' || Jahr(Datum) liegt, wenn ja dann ist mein Wert Jahr(Datum) - 1, sprich ich gehöre zum letzten Geschäftsjahr, ansonsten Jahr(Datum).
Oder bin ich gerade voll auf dem Holzweg?
probier es doch mal aus , obs so einfach ist . ich sehe da holz bei gernelles Jahr(Datum) -1 .
und das muss noch gruppiert werden .
weil in einem jahr treten schon 2 werte auf, einmal nach oben einmal nach unten . und das soll die selber gruppe sein .
aber ich lerne gerne dazu .
und das soll jeweils die selber gruppe sein . eine schicke lösung würde dem fragenden helfen . als test brauch man ja gerade mal 5 oder 6 einträge .
Das ist formal eine Alternative zu meinem Vorschlag.
Bleibt zu testen, ob man auf ein so zusammengestricktes Feld gruppieren kann.
Bei BETWEEN muss man aufpassen, wenn teilweise Uhrzeiten mit am Datum hängen.
01.01. bis 30.06. würde zum Beispiel 30.06. 10:00 nicht erfassen.
01.01. bis 01.07. würde hingegen 01.07. 00:00:00 mit erfassen.
Du denkst, du bist schlau und nimmst noch die Uhrzeit in die Bis-Angabe hinzu?
01.01. bis 30.06. 23:59:59,999
Pustekuchen. Wird mal flux auf 31.07. 00:00:00 gerundet. Du müsstest 998 verwenden.
if you want to search the entire day of the 18th. I set miliseconds to 998 because SQL Server was pulling in 2013-10-19 00:00:00:0000 in the query.
https://stackoverflow.com/questions/19451767/datetime-between-statement-not-working-in-sql-server
Between erspart Schreibarbeit, wo es angebracht ist.
Save ist man mit
Datum >= 01.01. and Datum < 01.07.
ich hab mal was gefunden was bei MySQL so gedacht sein soll
SELECT ( CASE WHEN (MONTH(datum)) <=3 THEN convert( YEAR(datum)-1,varchar(4)) + '-' + convert( YEAR(datum)%100,varchar(4)) ELSE convert(YEAR(datum),varchar(4))+ '-' + convert( (YEAR(datum)%100)+1,varchar(4))END) AS FinancialYear FROM najgeschaeft
das funktioniert , man muss noch den TAG mit einbauen.
so hab was funktionierendes gefunden , danke für dein hinweis .
SELECT (
CASE
WHEN (MONTH(datum) >=3) AND (DAY(datum) >=11)
THEN convert( (YEAR(datum)%100)+1,varchar(4))
ELSE convert( (YEAR(datum)%100),varchar(4))
END)
AS FinancialYear
FROM najgeschaeft
Ausgabe
21
21
21
22
22
23
Daten
1 | 2020-03-11 | test1 | 10
2 | 2020-02-10 | test2 | 10
3 | 2021-02-11 | test3 | 10
4 | 2021-03-11 | test4 | 10
5 | 2022-02-10 | test5 | 10
6 | 2022-03-11 | test6 | 10
lol mein beispiel funktioniert irgendwie noch nicht über die jahresgrenze :( . ohne variable wird das nix .
Dann definierst du das Jahr oder eine andere Spalte über eine Berechnung mit dem Datum. Welches RDBMS du benutzt weiß man leider nicht, da du verschiedenste Stichwörter gesetzt hast.
Sauber wäre natürlich, wie von Suboptimierer erklärt das Geschäftsjahr irgendwo anzulegen.
Alternativ kannst du je nach RDBMS dann sowas nutzen wie die CASE Anweisung und quasi das Jahr - 1 nehmen, wenn das Datum zwischen dem 01.01 und dem 11.02 liegt, ansonsten das Jahr selbst und natürlich auch danach gruppieren.
Je nach RDBMS nutzt du eben verschiedene Methoden für sowas.
Anbei mal ein Beispiel in Oracle, wie es da problemlos funktionieren würde:
WITH TAB AS (
SELECT TO_DATE('01.01.2020', 'DD.MM.YYYY') AS Datum, 1 AS Zahl FROM DUAL
UNION
SELECT TO_DATE('01.03.2020', 'DD.MM.YYYY') AS Datum, 2 AS Zahl FROM DUAL
UNION
SELECT TO_DATE('02.03.2020', 'DD.MM.YYYY') AS Datum, 3 AS Zahl FROM DUAL
UNION
SELECT TO_DATE('01.01.2021', 'DD.MM.YYYY') AS Datum, 4 AS Zahl FROM DUAL)
SELECT CASE WHEN EXTRACT(MONTH FROM Datum) < 3
THEN EXTRACT(YEAR FROM Datum) -1
ELSE EXTRACT(YEAR FROM Datum)
END AS Geschaeftsjahr,
SUM(Zahl)
FROM TAB
GROUP BY CASE WHEN EXTRACT(MONTH FROM Datum) < 3
THEN EXTRACT(YEAR FROM Datum) -1
ELSE EXTRACT(YEAR FROM Datum)
END
Oben sind nur Testdaten, die zur Tabelle TAB zusammengefasst werden. Als Beispiel hier der Wechsel zum 01.03.
Möchte man irgendwo mitten im Monat wechseln z.B. zum 15.03, dann ist die Prüfung eben etwas ala:
EXTRACT(MONTH FROM Datum) < 3 OR EXTRACT(MONTH FROM Datum) = 3 AND EXTRACT(DAY FROM Datum) < 15
Beim Concatten vom Datum und Hin- und Herwandlung von Datum zu CHAR zickt Oracle hier auch ein wenig, so geht es aber.
Natürlich könnte man sich mit dem WITH z.B. auch eine virtuelle Geschäftsjahrestabelle anhand der Daten machen und hat die Konvertierung dann nicht mehr im eigentlichen SELECT.
das grouping ist ja übelst inperformant und irgendwie doppelt oder ? du hast doch schon das Geschäftsjahr ?
Seine Fragestellung war danach zu gruppieren, damit muss bei einer CASE Anweisung bzw. generell bei berechneten Werten diese genauso in die GROUP BY Clause. Anders geht es soweit ich weiß nicht.
Dein CONVERT ist da nix anderes, das ist ja nur eine Typumwandlung. EXTRACT gibt eben eine NUMBER raus, mit der ich gleich rechnen kann.
Intern wird das RDBMS den Kram vermutlich eh nur einmal ausführen. Finde aber auch Schade, dass ich das nicht oben benennen kann und unten die benannte Spalte verwenden kann. Das geht nur im ORDER BY.
ja aber wenn du schon AS Geschaeftsjahr
hast
warum machst du ds nochmal , du kannst doch danach gruppieren , deine erklärung ergibt keinen sinn .
ich mach doch auch nix anderes . nur doppel ich ds nicht im group
du doppelst das nur
Wenn er nach gruppieren fragt, dann gruppiere ich. Ich will ja in dem Beispiel Zahl aufsummieren, gruppiert nach dem Geschäftsjahr.
Ein GROUP BY Geschaeftsjahr ist nicht möglich, das gibt als Fehlermeldung invalid identifier. Zumindest Oracle erfordert bei Berechnungen im GROUP BY genau den gleichen Wert und kann nicht auf den oben gegebenen Namen referenzieren.
Wenn das anders gehen sollte (ab von einem SELECT außen rum), bin ich sicher lernbereit, hat mich schon immer genervt :)
Btw gerade mal getestet, Oracle führt es zumindest intern nur einmal aus. Wenn ich in die Bedingung noch eine Funktion einbaue, die ein SLEEP mit drin hat, dann kriege ich die gleiche Ausführungszeit, egal ob gruppiert oder nicht. Ist also nur von der Syntax unschön, ansonsten ändert das nix.
so mal ein ganz einfaches beispiel
MySQL :
SELECT (
CASE
WHEN (MONTH(datum) >=3) AND (DAY(datum) >=11)
THEN convert( (YEAR(datum)%100)+1,varchar(4))
ELSE convert( (YEAR(datum)%100),varchar(4))
END)
AS FinancialYear
FROM najgeschaeft
/* GROUP BY FinancialYear */
SQL
SELECT (
CASE
WHEN (MONTH(datum) >=3) AND (DAY(datum) >=11)
THEN convert( varchar(4),(YEAR(datum)%100)+1)
ELSE convert( varchar(4),(YEAR(datum)%100))
END)
AS FinancialYear
FROM najgeschaeft
/* GROUP BY FinancialYear */
Ausgabe
21
21
21
22
22
23
Daten
1 | 2020-03-11 | test1 | 10
2 | 2020-02-10 | test2 | 10
3 | 2021-02-11 | test3 | 10
4 | 2021-03-11 | test4 | 10
5 | 2022-02-10 | test5 | 10
6 | 2022-03-11 | test6 | 10
funtz also doch ganz einfach :)
die leerzeilen sollen nur die finanzjahr grenzen zeigen .
und gar nicht so schwer lol .
Versuche es rein durch eine Abfrage