Jan 24, 2019

8998

Bei SQL Abfragen Tage für die keine daten vorliegen mit 0 anzeigen

Hallo, ich habe aktuelle eine kleine Herausforderung bei der ich etwas Schwarm Intelligenz benötige

Ich bin aktuell dabei für ein kleines Shop System Auswertungen zu erstellen.

sowas wie .. Umsätze Pro Tag usw., Umsatz pro Kunde, Umsatzverlauf im letzten Jahr usw.
nun habe ich aber das Problem mit den Tagen an denen keine Umsätze stattgefunden haben.

kleines Beispiel:

SELECT SUM(Summe) FROM Rechnungen WHERE YEAR(Rechnungsdatum) = YEAR(GETDATE()) GROUP BY Rechnungsdatum

Liefert die Umsätze Pro Tag im Aktuellen Jahr, alles gut soweit, Problem ist nur zeige ich das in einem Diagramm werden Tage an denen keine Umsätze erfolgte ignoriert weil sie in der
Ergebnismenge nicht auftauchen.

01.01.2019 | 1000 €
02.01.2019 | 500 €
»»»» 03.01.2019 Fehlt da keine Rechnungen mit diesem Datum gefunden werden, es sollte aber im besten Fall mit 0 angezeigt werden.
04.01.2019 | 1500 €

Schlimmer ist es z.b. wenn ich Durchschnittswerte errechnen möchte

SELECT AVG(Summe) FROM Rechnungen WHERE YEAR(Rechnungsdatum) = YEAR(GETDATE()) GROUP BY Rechnungsdatum

Sollte den Durchschnittlichen Tagesumsatz liefern, allerdings sind die werte Absolut falsch da tage mit 0 € Umsatz nicht berücksichtigt werden ...

Kennt ihr einen guten weg mit diesem Problem in SQL Abfragen umzugehen?

Die Datenbank ist ein MS SQL Server 2012

Danke schon mal im Vorraus

Please also mark the comments that contributed to the solution of the article

Content-Key: 399279

Url: https://administrator.de/contentid/399279

Printed on: April 26, 2024 at 04:04 o'clock

19 Comments

Latest comment

Hi,
so wie ich das sehe, müsstes Du für jeden Tag einen Dummy-Datensatz mit 0€ erstellen, damit das mit dieser Abfrage so funktioniert.

E.

ja daran habe ich auch schon gedacht aber so leicht ist das nicht da ja das Beispiel oben sehr vereinfacht ist
im wirklichen leben müssten da haufenweise Daten Generiert werden, ich brächte einen Cronjob der Nachts automatisch für den Vortag die Einträge generiert und und und ....

Moin,

das kann ja auch gar nicht gehen, da die entsprechenden Daten nicht in der Tabelle vorhanden sind. Datenbanken sind dazu zu dumm.

Entweder erzeugst Du die Dummydatensätze zum Beispiel zu Beginn jeden Tages einen mit der Rechnungssumme 0, was wahrscheinlich nicht geht, weil Du dann eine Rechnungsnummer verbrauchst, die dann wieder ...

Oder Du schmeißt die Daten in eine Exceltabelle und löst das Problem mit dem Werkzeug, das dafür vorgesehen ist.

Liebe Grüße

Erik

Ich hatte vor 10 Jahren mal in VB 6 ein ähnliches Problem da hing auch ne SQL Datenbank hinter ...
da konnte man das irgendwie über eine Unterabfrage lösen bin ich der Meinung aber das ist soooo lange her ..
und den Code habe ich leider nicht mehr ...

Alternative habe ich mir jetzt überlegt eine Mappingtabelle zu erstellen mit allen Daten von 2010 - > 2050 oder so
Dann müsste man das eigentlich JOINEN können ... oder seht ihr da Probleme ?

Du könntest auch eine temporäre Tabelle erzeugen. In dieser zuerst die Dummy-Datensätze erstellen, mit 0€. Dann dazu die selektierten Daten hinzufügen. Jetzt die Dummy-Datensätze für jene Tage selektieren, für welche noch andere Datesätze existieren, und diese selektierten wieder löschen. In der temporären Tabelle die Berechnungen durchführen. Abschließend temporäre Tabelle wieder löschen.

Zitat von @MultiStorm:
Alternative habe ich mir jetzt überlegt eine Mappingtabelle zu erstellen mit allen Daten von 2010 - > 2050 oder so
Dann müsste man das eigentlich JOINEN können ... oder seht ihr da Probleme ?

Ein Tabelle nur mit Datumspalte. Für jeden Tag des Jahres einen Datensatz. Dann dies Tabelle mit der Umsatztabelle über das Datum verbinden ("joinen") und alle Datensätze abfragen. Das Ergebnis dann entweder in eine Tabelle zwischenspeichern und darüber abfragen oder direkt verketten mit einer "äußeren" SELECT.

Edit:
Habe mal aus Interesse ein Bsp. gezimmert:

Table_1 --> Spalten "Datum" und "Umsatz", Umsätze für 3 Tage in 01/19
Table_2 --> nur Spalte "Datum", Werte von 01.01.2019 - 31.01.2019

SELECT Avg(TabX.Umsatz )
FROM (
SELECT     dbo.Table_2.Datum, ISNULL(dbo.Table_1.Umsatz,0) AS Umsatz
FROM         dbo.Table_2 LEFT OUTER JOIN
                      dbo.Table_1 ON dbo.Table_2.Datum = dbo.Table_1.Datum
					  ) AS TabX

generiere dir eine (Temporäre) Tabelle mit den Tagen in der Range die du haben willst.
z.B so

CREATE TABLE _Dates (
  d DATE,
  PRIMARY KEY (d)
)
DECLARE @dIncr DATE = '2000-01-01'  
DECLARE @dEnd DATE = '2100-01-01'  

WHILE ( @dIncr < @dEnd )
BEGIN
  INSERT INTO _Dates (d) VALUES( @dIncr )
  SELECT @dIncr = DATEADD(DAY, 1, @dIncr )
END

und mache mit dieser einen Outer-Join auf dein Query.
Du joinst also alle Tage aus deiner Range auf alle evtl. vorhandenen Ergebnisse deiner Rechnungen

Oder du verzichtest auf Behelfstabellen und / oder Daten und machst das alles in einem Rutsch, das Zauberwort ist CTE:

WITH t(datum) AS (
	SELECT	cast(convert(CHAR(4),datepart(year,getdate())) + '-01-01' AS DATE)  
	UNION ALL
	SELECT	dateadd(day,1,t.datum)
	FROM	t
	WHERE	datepart(year,t.datum) = datepart(year,dateadd(day,1,t.datum))
	)
SELECT	t.datum AS Datum,
		sum(Summe) AS Summe
FROM	t
LEFT JOIN Rechnungen r
ON		t.datum = r.Rechnungsdatum
GROUP BY t.datum
OPTION (MAXRECURSION 366)

müsste man probieren was performanter ist. 1x Generieren und davon zähren, oder jedes mal neu generieren...

ich habe mir jetzt erst mal eine feste Tabelle erstelle, aber das JOINEN will aktuell nicht so wie ich das will, ich bekomme trotzdem nicht die 0er werte ..
ich glaube ich habe da einen Logic Fehler

Siehe mein Bsp.
Damit hat das bei mir sofort funktioniert.

Vom Bauchgefühl her ist eine existierende Tabelle natürlich schneller aber wenn ich mir das genau überlege dann enthällt die Tabelle bei dir Datensätze für 100 Jahre, braucht also schonmal einen Index (hier durch Primary Key). Du wählst also jedesmal aus ~36.500 Datensätzen ~365 aus, das allein könnte schon langsammer sein als 365 Datensätze zu erzeugen und ohne WHERE-Bedingung zu joinen.

In jedem Fall wird der Unterschied maginal ausfallen, der Großteil an Kosten entsteht durch die Rechnungen-Tabelle.

ja ich prüfe dein beispiel gleich nochmal, wie gesagt bei mir hängen ja noch 2 oder 3 Joins mehr dran

vielleicht habe ich auch nur was übersehen aber eigentlich habe ich das genau so gemacht wie du gesagt hast

Funktioniert leider nicht
hier mal der Original SQL

SELECT CONVERT(date, DateValues.datevalue) as Datum, ISNULL(SUM(voucher_sum_inkl),0) as Summe 
FROM TICKET_Vouchers 
LEFT OUTER JOIN DateValues ON (CONVERT(date, TICKET_Vouchers.voucher_date) = DateValues.datevalue)
LEFT OUTER JOIN Operations ON (TICKET_Vouchers.OperationID = Operations.OperationID) 
WHERE year(Operations.OperationDate) = year(GETDATE()) 
GROUP BY CONVERT(date, DateValues.datevalue) 
ORDER BY CONVERT(date, DateValues.datevalue);

unterschied zum Beispiel ist der Join auf die Operation Tabelle, Hintergrund ... die Auswertung soll Session bedingt erfolgen,
und ich glaube genau das ist das Problem ...

Ich denke ich müsste mir einen SQL oder ne Function Schreiben der mir Session Anfang und Ende berechnen kann damit ich mir den JOIN auf
die Operation Tabelle ersparen kann ...
und dann in der WHERE Klausel das Datum als Bereich eingrenzen

ISNULL(SUM(voucher_sum_inkl),0)

Wie soll bei einer Summe DBNULL herauskommen?
Du musst erst "joinen". Dabei die DBNULL-Werte durch 0 ersetzen. Und erst diese ersetzten Werte summieren bzw. den Durchschnitt berechnen.

Und außerdem musst Du anders herum "joinen". In der "DateValues" sind doch Sätze dabei, für welche es keine "untergeordneten" Sätze in "TICKET_Vouchers" gibt. Also

SELECT DateValues.datevalue, ISNULL(voucher_sum_inkl,0)
FROM DateValues 
LEFT OUTER JOIN TICKET_Vouchers ON (CONVERT(date, TICKET_Vouchers.voucher_date) = DateValues.datevalue)
LEFT OUTER JOIN Operations ON (TICKET_Vouchers.OperationID = Operations.OperationID) 

Das liefert Dir erstmal eine Selektion ("Tabelle"), in welcher für jeden Tag (DateValues.datevalue) eine Wert für "voucher_sum_inkl" steht, welcher nicht DBNULL ist, also 0 oder ein "richtiger" Wert.
Wenn Du den Zeitraum filtern willst, dann würde ich das auch gleich über Tabelle "DateValues" machen, also

WHERE year(DateValues.datevalue) = year(GETDATE())

weil das logischer wäre.
Und da "drumherum" kannst Du jetzt gruppieren und Berechnungen anstellen.

SELECT TEMP1.datevalue, SUM(TEMP1.VALUE1) AS Summe
FROM (
SELECT DateValues.datevalue, ISNULL(voucher_sum_inkl,0) AS VALUE1
FROM DateValues 
LEFT OUTER JOIN TICKET_Vouchers ON (CONVERT(date, TICKET_Vouchers.voucher_date) = DateValues.datevalue)
LEFT OUTER JOIN Operations ON (TICKET_Vouchers.OperationID = Operations.OperationID) 
WHERE year(DateValues.datevalue) = year(GETDATE())
           ) AS TEMP1
GROUP BY TEMP1.DateValue
ORDER BY TEMP1.DateValue

emeriks hat Recht und zusätzlich musst du eventuell noch einen Zwischenschritt machen. Wenn nämlich in Operations mehrere Datensätze zu einem Eintrag in TICKET_Vouchers existieren dann kannst du nicht mehr einfach Spalten aus Operations aggregieren, du musst erst die Werte aus TICKET_Vouchers aggregieren und in einem zweiten Schritt Operations aggregieren.

Eine Funktion brauchst du in jedem Fall nicht, genau für sowas ist SQL gemacht.

erst mal vielen vielen dank für die Mühe dir du / Ihr euch gemacht habt
Das Funktioniert so nun auch bis auch eine kleine Anpassung die ich vornehmen muste, was du natürlich nicht wissen konntest das es sehr wohl Rechnungen aus Dem Vorjahr geben kann die aber zur Session 2019 gehören ... Außerdem interessieren mich natürlich die werte Ab morgen auch "NOCH" nicht

SELECT TEMP1.datevalue, SUM(TEMP1.VALUE1 as Summe)
FROM (
		SELECT DateValues.datevalue, ISNULL(voucher_sum_inkl,0) AS VALUE1
		FROM DateValues 
		LEFT OUTER JOIN TICKET_Vouchers ON (CONVERT(date, TICKET_Vouchers.voucher_date) = DateValues.datevalue)
		LEFT OUTER JOIN Operations ON (TICKET_Vouchers.OperationID = Operations.OperationID) 
		WHERE DateValues.datevalue >= (
										SELECT TOP 1 TICKET_Vouchers.voucher_date FROM TICKET_Vouchers
										LEFT JOIN Operations ON (TICKET_Vouchers.OperationID = Operations.OperationID)
										WHERE year(Operations.OperationDate) = year(GETDATE())
										ORDER BY TICKET_Vouchers.voucher_date
									 )
			  AND DateValues.datevalue <= CONVERT(date, GETDATE())
     ) AS TEMP1
GROUP BY TEMP1.DateValue
ORDER BY TEMP1.DateValue

der SubSelect ist nicht 100% Sauber da es theoretisch sein kann das 1 oder 2 tage vergehen bis die ersten Käufe nach Verkauf Start erfolgen ...
das muss ich mir aber nochmal in ruhe anschauen aber bei 1 oder 2 tagen ist das glaube ich zu vernachlässigen ...

jetzt habe ich was ich will und Brauche

1000 Dank an alle

Ich glaube da gibt es auch noch Optimierungsmöglichkeiten aber dazu wäre es das beste für alle verschiedenen Fälle die entstehen können auch einen Beispieldatensatz (Ausgangsdatensatz) zu haben. Aber gut das es erstmal läuft...

German solved Question Databases Development

Hotly discussed

Check of ZFW Firewallgleixnerd - 5 Comments