none
Wie je Monat Umsatz kumulieren? RRS feed

  • Frage

  • Hallo, 

    ich habe eine Tabelle mit folgenden Feldern

    Wirtschaftsjahr, Jahr, Monat, RechnungspositionsID, Artikelumsatz

    Das Wirtschaftsjahr beginnt im November deshalb != Jahr

    In der Tabelle ist jede Rechnungsposition enthalten.

    Ich möchte jetzt auf Basis des Wirtschaftsjahres den Artikelsumsatz je Monat ausgeben, das aber kummuliert so das im letzten Monat des Wirtschaftsjahres (Oktober) der Jahresumsatz steht.

    Kann mir da bitte jemand auf die Sprünge helfen. 

    Hab es schon mit Over Partition probiert, aber noch nicht so wie es soll.

    Danke Gruß Andre

    Freitag, 29. November 2019 12:31

Antworten

  • Use tempdb
    go
    Create Table #Daten (Wirtschaftsjahr int, Jahr int, Monat int, RechnungspositionsID int, Artikelumsatz int);
    
    Insert into #Daten(Wirtschaftsjahr, Jahr, Monat, RechnungspositionsID, Artikelumsatz) values
    (2018, 2017, 11, 1, 100),
    (2018, 2017, 11, 2, 100),
    (2018, 2017, 11, 3, 100),
    (2018, 2017, 12, 1, 100),
    (2018, 2018,  1, 1, 100),
    (2018, 2018, 10, 1, 100),
    (2018, 2018, 10, 2, 100),
    (2019, 2018, 11, 1, 100),
    (2019, 2018, 12, 1, 100);
    
    
    Select Distinct Wirtschaftsjahr, Jahr, Monat, SUM(Artikelumsatz) OVER(PARTITION BY Wirtschaftsjahr ORDER BY Jahr, Monat) as RollingSum
    from #Daten
    ;
    
    go
    Drop Table #Daten;
    SELECT DISTINCT

    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    • Als Antwort markiert AndreT78 Freitag, 29. November 2019 13:55
    Freitag, 29. November 2019 13:52
    Beantworter

Alle Antworten

  • Am einfachsten legst du dir dafür einen Kalender an, der die Wirtschaftsjahr/Monate mit dem echten Jahr/Monat verknüpft:

    JahrMonat | JahrMonatWirtschaft
    201811 | 201801
    201812 | 201802
    201901 | 201803
    :
    usw. usf.

    Dann kannst du einfach per Join mit JahrMonat verknüpfen und JahrMonatWirtschaft gruppieren.
    Ansonsten must du dir halt eine Formel ausdenken, die Jahr/Monat in den BusinessMonat umrechnet.

    case month(rechnungsdatum)
    when 11 then year(rechnungsdatum) * 100 + 01
    when 12 then year(rechnungsdatum) * 100 + 02
    when 01 then then (year(rechnungsdatum) - 1) * 100 + 03
    :
    :
    end JahrMonatWirtschaft

    Diesen Ausdruck kannst du dir auch als SQL-Funktion bauen.

    Freitag, 29. November 2019 12:44
  • Den Kalender habe ich ja quasi schon. 

    Ich habe das Wirtschaftsjahr und den Monat ja schon korrekt am Datensatz dran stehen.

    Mir geht es jetzt drum, wie ich das kummuliert je Monat aufsteigend im WJ summiere.

    Ergebniss soll sein (im Beispiel je Monat 100€ Umsatz)

    WJ    Monat   Umsatz(kum)

    2019   01        100

    2019   02         200

    2019   03        300

    Freitag, 29. November 2019 12:48
  • Mir geht es jetzt drum, wie ich das kummuliert je Monat aufsteigend im WJ summiere.

    Das kannst Du über eine Fenster Funktion SUM OVER erreichen:

    ;WITH testDaten AS 
        (SELECT '2019' AS WJ, '01' AS Monat, 100.0 AS Umsatz
         UNION ALL SELECT '2019', '02', 100.0
         UNION ALL SELECT '2019', '03', 150.0
         UNION ALL SELECT '2019', '04', 100.0)
    
    SELECT *, SUM(Umsatz) OVER (PARTITION BY WJ ORDER BY WJ, Monat) AS UmsatzLaufend
    FROM testDaten


    Olaf Helper

    [ Blog] [ Xing] [ MVP]



    Freitag, 29. November 2019 13:41
  • Hallo Andre,

    wenn ich das richtig verstanden habe, sollte es so gehen:

    Use tempdb
    go
    Create Table #Daten (Wirtschaftsjahr int, Jahr int, Monat int, RechnungspositionsID int, Artikelumsatz int);
    
    Insert into #Daten(Wirtschaftsjahr, Jahr, Monat, RechnungspositionsID, Artikelumsatz) values
    (2018, 2017, 11, 1, 100),
    (2018, 2017, 12, 1, 100),
    (2018, 2018,  1, 1, 100),
    (2018, 2018, 10, 1, 100),
    (2019, 2018, 11, 1, 100),
    (2019, 2018, 12, 1, 100);
    
    
    Select Wirtschaftsjahr, Jahr, Monat, SUM(Artikelumsatz) OVER(PARTITION BY Wirtschaftsjahr ORDER BY Jahr, Monat) as RollingSum
    from #Daten;
    
    go
    Drop Table #Daten;
    

    HTH!


    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    Freitag, 29. November 2019 13:41
    Beantworter
  • Standardmäßig würde ich erst mal ganz einfach Gruppierung und Summierung wählen:

    select jahr, monat, sum(betrag)
    from mytable
    group by jahr, monat
    Olapfunktionien "x over y" benötigt man erst, wenn man die Einzeldaten auch noch zusätzlich benötigt.
    Denn die OLAP-Aggregate wiederholen sich ja auf jedem Satz derselben Gruppe.

    Freitag, 29. November 2019 13:45
  • Standardmäßig würde ich erst mal ganz einfach Gruppierung und Summierung wählen:

    select jahr, monat, sum(betrag)
    from mytable
    group by jahr, monat
    Olapfunktionien "x over y" benötigt man erst, wenn man die Einzeldaten auch noch zusätzlich benötigt.
    Denn die OLAP-Aggregate wiederholen sich ja auf jedem Satz derselben Gruppe.

    das kummuliert aber nicht.
    Freitag, 29. November 2019 13:46
  • Mir geht es jetzt drum, wie ich das kummuliert je Monat aufsteigend im WJ summiere.

    Das kannst Du über eine Fenster Funktion SUM OVER erreichen:

    ;WITH testDaten AS 
        (SELECT '2019' AS WJ, '01' AS Monat, 100.0 AS Umsatz
         UNION ALL SELECT '2019', '02', 100.0
         UNION ALL SELECT '2019', '03', 150.0
         UNION ALL SELECT '2019', '04', 100.0)
    
    SELECT *, SUM(Umsatz) OVER (PARTITION BY WJ ORDER BY WJ, Monat) AS UmsatzLaufend
    FROM testDaten


    Olaf Helper

    [ Blog] [ Xing] [ MVP]



    Danke,

    das liefert mir zwar noch trotzdem je Rechnungsposition einen Datensatz zurück, genauso wie bei Christoph seinem Vorschlag, aber dann ich im Anschluss dann noch noch gruppieren.


    Freitag, 29. November 2019 13:49
  • Hallo Andre,

    wenn ich das richtig verstanden habe, sollte es so gehen:

    Use tempdb
    go
    Create Table #Daten (Wirtschaftsjahr int, Jahr int, Monat int, RechnungspositionsID int, Artikelumsatz int);
    
    Insert into #Daten(Wirtschaftsjahr, Jahr, Monat, RechnungspositionsID, Artikelumsatz) values
    (2018, 2017, 11, 1, 100),
    (2018, 2017, 12, 1, 100),
    (2018, 2018,  1, 1, 100),
    (2018, 2018, 10, 1, 100),
    (2019, 2018, 11, 1, 100),
    (2019, 2018, 12, 1, 100);
    
    
    Select Wirtschaftsjahr, Jahr, Monat, SUM(Artikelumsatz) OVER(PARTITION BY Wirtschaftsjahr ORDER BY Jahr, Monat) as RollingSum
    from #Daten;
    
    go
    Drop Table #Daten;

    HTH!


    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    Danke,

    das liefert mir zwar noch trotzdem je Rechnungsposition einen Datensatz zurück, genauso wie bei Olaf seinem Vorschlag, aber dann ich im Anschluss dann noch noch gruppieren.


    Freitag, 29. November 2019 13:51
  • Use tempdb
    go
    Create Table #Daten (Wirtschaftsjahr int, Jahr int, Monat int, RechnungspositionsID int, Artikelumsatz int);
    
    Insert into #Daten(Wirtschaftsjahr, Jahr, Monat, RechnungspositionsID, Artikelumsatz) values
    (2018, 2017, 11, 1, 100),
    (2018, 2017, 11, 2, 100),
    (2018, 2017, 11, 3, 100),
    (2018, 2017, 12, 1, 100),
    (2018, 2018,  1, 1, 100),
    (2018, 2018, 10, 1, 100),
    (2018, 2018, 10, 2, 100),
    (2019, 2018, 11, 1, 100),
    (2019, 2018, 12, 1, 100);
    
    
    Select Distinct Wirtschaftsjahr, Jahr, Monat, SUM(Artikelumsatz) OVER(PARTITION BY Wirtschaftsjahr ORDER BY Jahr, Monat) as RollingSum
    from #Daten
    ;
    
    go
    Drop Table #Daten;
    SELECT DISTINCT

    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    • Als Antwort markiert AndreT78 Freitag, 29. November 2019 13:55
    Freitag, 29. November 2019 13:52
    Beantworter
  • Danke schön, jetzt kann ich gleich beruhigt ins Wochenende ;)

    Wünsche allen auch ein schönes ruhiges Wochenende

    Freitag, 29. November 2019 13:56