SQL monatliche Summierung

[haentschman]

Hallo alle miteinander...

Ich stehe ein wenig auf dem Schlauch... :roll:

Ich habe folgende Daten (gekürzt):

Code:

Datum       Preis
01.02.10    10
01.02.10    20
.
.
03.05.10    30

usw.

Ich benötige als Ergebnismenge:

Code:

Monat       Preis
02/10       Summe(Einzeltage)
.
.

usw.

Auch leere Monate sollen berücksichtigt werden und einen Datensatz mit 0 liefern.

Im Moment lege ich eine RAM Table an und fülle Sie entsprechend der Ergebnisse aus der DB (einzelne Queries). Dieses ist ja etwas mühsam, da die Monate gesplittet werden müssen aus einem Zeitraum von z.B. 01.02.10 - 31.05.10

Ist das mit SQL lösbar ? Ich habe schon über eine View nachgedacht, aber noch nicht probiert. Welche Ideen kommen noch als Lösungsweg in Frage ?

Danke... :hi:

Nachtrag: bin grad an probieren mit groupby und extract für den Monat. Die Variante mit der MemTable ist wahrscheinlich das günstigste, da ich dem Report auch eigene Bezeichnungen mitgeben kann (Felder definieren) die aus der DB nicht mitkommen.

[jfheins]

Das einfachste wäre jetzt sowas in der Art:

Code:

select *,sum(preis) from tabelle group by month(datum)

(Ich hoffe mal, du hast das Datum nicht als String in der Datenbank)
So als Anfang. Die Query liefert keine leeren Monate zurück, da kann man noch dran verbessern.

[FredlFesl]

Na ja, fast.

Code:

select extract (month from datum) as 'Monat', 
       sum(Preis) as PreisProMonat 
from Tabelle 
group by extract (month from datum)

[haentschman]

Danke für eure Antworten...

in diese Richtung bin ich auch grad unterwegs. Problematisch dabei ist, daß der Zeitraum jahrübergreifend sein kann und somit Monat und Jahr gruppiert werden müssen.

Bsp:
11-12
12-12
01-13
02-13
.
.
usw...

[himitsu]

Kann man vielleicht mehrere GROUP-Regeln angeben?

SQL-Code:

select *, sum(preis) from tabelle group by month(datum), year(datum)

.

oder man rechnet Jahr und Monat wieder zusammen

SQL-Code:

select *, sum(preis) from tabelle group by month(datum) - 1 + year(datum) * 12

.

SQL-Code:

select *, sum(preis) from tabelle group by encodedate(year(datum), month(datum), 1)

.
(gibt doch bestimmt sowas wie EncodeDate)

oder man rundet irgendwie die Tage weg :gruebel:

[haentschman]

Sooo...

Delphi-Quellcode:

QuerySales.SQL.Text:= 'select extract(year from PAYMENT_DATE)||extract(month from PAYMENT_DATE) as aMonth, sum(PAYMENT) as SumPrice from PAYMENTS where PAYMENT_DATE > :D group by aMonth';
QuerySales.ParamByName('D').AsDate:= StrToDate('01.01.2011'); // erst mal zu Testzwecken statisch
QuerySales.Open;

damit kommen schon mal alle Jahr+Monate auch Jahrübergreifend gruppiert und summiert. Eine gute Basis. :thumb:

gefunden in: http://www.firebirdfaq.org/faq155/
... was nicht alles geht... :gruebel:

Ich bastel weiter... 8-)

[jobo]

Du brauchst jetzt noch eine 2.Tabelle, die alle benötigten Monaten/Jahre separat und vollständig liefert, Datum im gleichen Format wie Deine Extraxt Werte.
Die beiden Mengen outer joinen und Du bekommst auch die "leeren" MOnate angezeigt.

Pseudo:

SQL-Code:

Select l.Zeitangabe, P.Preissumme
  from Timeline  l,
       Preissumme p
where l.MonatJahr = p.MonatJahr (+)

Nicht vorhandene Preise dann noch zu Null formatieren.

[omata]

[Blödsinn entfernt]

[jobo]

Naja, es ging um das Prinzip.
Du sparst Dir das Jahr und implizierst in Deinem SQL, dass es in der payment Tabelle auftauchen muss?
Um es zu bekommen, muss dazu noch die ganze Paymenttabelle durchgegrouped werden.
Ob das nun ein Gewinn ist?

[omata]

Zitat:

Zitat von jobo (Beitrag 1113268)

Ob das nun ein Gewinn ist?

Ja, ist es.

[alex517]

Hi,

ich verwende für solche Fälle eine Procedure die mir den Zeitstrahl
vorgibt. Damit ist keine extra Tabelle notwendig.
Damit würde deine Abfrage so aussehen

SQL-Code:

select
  JM.Jahr,
  JM.Monat,
  sum(PAYMENT) as SumPrice
from
  SP_JJMM(:VonDatum, :BisDatum) JM
  left join PAYMENTS P on (EXTRACT(YEAR from P.PAYMENT_DATE) = JM.JAHR AND EXTRACT(MONTH from P.PAYMENT_DATE) = JM.MONAT)
group BY
  JM.Jahr,
  JM.Monat

Edit: muss natürlich left join heißen..

oder so

SQL-Code:

select
  JM.Jahr,
  JM.Monat,
  sum(PAYMENT) as SumPrice
from
  SP_JJMM(:VonDatum, :BisDatum) JM
  left join PAYMENTS P on (EXTRACT(YEAR from P.PAYMENT_DATE) * 100 + EXTRACT(MONTH from P.PAYMENT_DATE) = JM.JJJMM)
group BY
  JM.Jahr,
  JM.Monat

und hier noch die SP dazu:

SQL-Code:

create or alter procedure SP_JJMM (
    ADATUM_VON date,
    ADATUM_BIS date)
returns (
    JAHR integer,
    MONAT integer,
    JJJJMM INTEGER)
as
declare variable VON integer;
declare variable BIS integer;
begin
  IF (ADATUM_VON is not NULL) THEN
    VON = EXTRACT(YEAR FROM ADATUM_VON) * 100 + EXTRACT(MONTH FROM ADATUM_VON);
  ELSE
    VON = EXTRACT(YEAR FROM CURRENT_DATE) * 100 + EXTRACT(MONTH FROM CURRENT_DATE);

  IF (ADATUM_BIS is not NULL) THEN
    BIS = EXTRACT(YEAR FROM ADATUM_BIS) * 100 + EXTRACT(MONTH FROM ADATUM_BIS);
  ELSE
    BIS = EXTRACT(YEAR FROM CURRENT_DATE) * 100 + EXTRACT(MONTH FROM CURRENT_DATE);

  JAHR = NULL;
  MONAT = NULL;

  WHILE (VON <= BIS) DO
  BEGIN
    JAHR = VON / 100;
    MONAT = MOD(VON, 12) +1;
    JJJJMM = JAHR * 100 + MONAT;
    suspend;
    VON = VON +1;
  END
end

[FredlFesl]

Wenn Du aus deiner SP eine Tabelle aller Tage seit Christi Geburt (oder weiß der Himmel :mrgreen: seit wann) machst, kannst Du zudem noch weitere Informationen dort hinein packen. Als da wären:
Name des Wochentags, Feiertage, Arbeitstage, Ferien, Bürozeiten, Mittagspausen.

Alles kann man natürlich individuell ausrechnen, das dauert aber auch...

Die Tabelle wird zwar nicht sonderlich klein sein (sagen wir: 2000x365x24 Zeilem, 20 oder so Spalten), aber WTF. Wir haben es schließlich mit einer DB zu tun, da sind die paar Zeilen ein Klacks.

Vorteil: Du kannst für jedes Projekt diese Tabelle übernehmen und musst nur geschäftslogik-spezifische Werte (Arbeitszeiten, Ferien etc.) anpassen.

[omata]

Da finde ich jobo's Ansatz ja noch besser. Naja, holt die Kanonen raus, die Spatzen sind im Anflug. (Ich bleibe lieber bei Datenmanipulation auf reinster SQL-Ebene -> kurz, einfach, einfach schön <- Geschmacksache)

[omata]

Oh, hab es erst jetzt gesehen...

Zitat:

Zitat von jobo (Beitrag 1113268)

Du sparst Dir das Jahr und implizierst in Deinem SQL, dass es in der payment Tabelle auftauchen muss?

Äh, ja, alles andere macht auch keinen Sinn.

[jobo]

Das Jahr aus der Payments Tabelle zu holen, birgt die Gefahr, dass es dort mal gar nicht drin steht. Lückenhafte Daten waren ja angesagt. Und wenn wir schon poetisch werden bei dem Thema:
Lieber ne Taube in der Hand, als ein Spatz auf dem Dach. ;)

Die Spalte mit den Jahren kann über eine separate Tabelle ebenfalls pivotisiert werden. Eine Tabelle mit 5, 10 oder50 Jahreseinträgen bietet einen definierten Rahmen, deren Kreuzprodukt mit einer Monatstabelle a 12 Sätze liefert gezielt alles was gebraucht/gewünscht ist.

Der Ansatz per Stored Proc bietet weiterhin auch die Möglichkeit, auch Daten für wöchentliche oder ander Cluster auszuspucken.

Edit meint: Hab einen Beitrag übersehen, die Lösung von Alex gefällt mir am besten

[omata]

Ich habe meinen Scheiss entfernt. Sorry, für den Spam.