SQL - Durchschnittsverbrauch berechnen

[user0815]

Hallo,

ich habe 2 Tabellen mit folgenden Feldern:
FAHRZEUGE: FahrzeugNr, Bezeichnung, ...
ABGABEDATEN: FahrzeugNr, AbgabemengeLiter, Kilometerstand, DatumUhrzeitUhrzeit, ...

Ich versuche jetzt für eine frei wählbare Anzahl von Fahrzeugen, für einen frei wählbaren Zeitraum den Durchschnittsverbrauch nach folgender Formel zu berechnen sowie noch andere Daten anzuzeigen:

Strecke = Max(Kilometerstand) - Min(Kilometerstand)
Liter = Summe(AbgabemengeLiter) - First(AbgabemengeLiter)

Verbrauch = (100/Strecke) * Liter

Code:

SELECT ABGABEDATEN.FahrzeugNr, FAHRZEUGE.Bezeichnung,
  Count(ABGABEDATEN.FahrzeugNr) AS ANZAHL,
  Sum(ABGABEDATEN.AbgabemengeLiter) AS GESAMTMENGE,
  Max(ABGABEDATEN.Kilometerstand) AS MAXKilometerstand,
  Min(ABGABEDATEN.Kilometerstand) AS MINKilometerstand,
  Max(ABGABEDATEN.Kilometerstand) - Min(ABGABEDATEN.Kilometerstand) AS STRECKE,
  
  ((100 / (Max(ABGABEDATEN.Kilometerstand) - Min(ABGABEDATEN.Kilometerstand))) * (Sum(ABGABEDATEN.AbgabemengeLiter) - 
  (SELECT FIRST 1
      T.AbgabemengeLiter AS ERSTE
    FROM
      ABGABEDATEN AS T
    WHERE
      T.FahrzeugNr = ABGABEDATEN.FahrzeugNr AND
      T.DatumUhrzeit >= '01.05.2012' AND
      T.DatumUhrzeit <= '31.12.2012'
    ORDER BY
      T.DatumUhrzeit))) AS VERBRAUCH  
     
FROM
  ABGABEDATEN
  INNER JOIN FAHRZEUGE ON ABGABEDATEN.FahrzeugNr = FAHRZEUGE.FahrzeugNr                              
WHERE
  (ABGABEDATEN.FahrzeugNr = 115) OR
  (ABGABEDATEN.FahrzeugNr = 120) OR
  (ABGABEDATEN.FahrzeugNr = 121 AND
  ABGABEDATEN.DatumUhrzeit >= '01.05.2012' AND
  ABGABEDATEN.DatumUhrzeit <= '31.12.2012')
GROUP BY
  ABGABEDATEN.FahrzeugNr,
  FAHRZEUGE.Bezeichnung

Im Test funktioniert das ganze... wenn allerdings für ein Fahrzeug in dem Zeitraum keine Abgabe vorhanden ist, so erhalte ich die Fehlermeldung "divide by zero" was ja auch richtig ist.

Frage: Wie kann ich das ganze besser umsetzen?

Ich habe vor die Daten im Fastreport anzuzeigen.
Zudem möchte ich auch wählbar machen ob die Abgabedaten zusätzlich angezeigt werden sollen.

Nimmt man dafür temporäre Tabellen? Mehrere Queries?

Grüße
user0815

[Lemmy]

Hi,

zum einen in eine SP verpacken, macht das Leben ungemein leichter und dann würde ich diese Fahrzeuge mit Fahrleistung = 0km rausfiltern:

Code:

Select-....
...

GROUP BY
  ABGABEDATEN.FahrzeugNr,
  FAHRZEUGE.Bezeichnung 
Having (Max(ABGABEDATEN.Kilometerstand) - Min(ABGABEDATEN.Kilometerstand))>0

Wenn die dennoch auftauchen sollen, dann ein case:

Code:

SELECT ABGABEDATEN.FahrzeugNr, FAHRZEUGE.Bezeichnung,
  Count(ABGABEDATEN.FahrzeugNr) AS ANZAHL,
  Sum(ABGABEDATEN.AbgabemengeLiter) AS GESAMTMENGE,
  Max(ABGABEDATEN.Kilometerstand) AS MAXKilometerstand,
  Min(ABGABEDATEN.Kilometerstand) AS MINKilometerstand,
  Max(ABGABEDATEN.Kilometerstand) - Min(ABGABEDATEN.Kilometerstand) AS STRECKE,
  case (Max(ABGABEDATEN.Kilometerstand) - Min(ABGABEDATEN.Kilometerstand))
    when 0 then 0
    else 
  ((100 / (Max(ABGABEDATEN.Kilometerstand) - Min(ABGABEDATEN.Kilometerstand))) * (Sum(ABGABEDATEN.AbgabemengeLiter) - 
  (SELECT FIRST 1
      T.AbgabemengeLiter AS ERSTE
    FROM
      ABGABEDATEN AS T
    WHERE
      T.FahrzeugNr = ABGABEDATEN.FahrzeugNr AND
      T.DatumUhrzeit >= '01.05.2012' AND
      T.DatumUhrzeit <= '31.12.2012'
    ORDER BY
      T.DatumUhrzeit)))   
   end AS VERBRAUCH
    
FROM
  ABGABEDATEN
  INNER JOIN FAHRZEUGE ON ABGABEDATEN.FahrzeugNr = FAHRZEUGE.FahrzeugNr                            
WHERE
  (ABGABEDATEN.FahrzeugNr = 115) OR
  (ABGABEDATEN.FahrzeugNr = 120) OR
  (ABGABEDATEN.FahrzeugNr = 121 AND
  ABGABEDATEN.DatumUhrzeit >= '01.05.2012' AND
  ABGABEDATEN.DatumUhrzeit <= '31.12.2012')
GROUP BY
  ABGABEDATEN.FahrzeugNr,
  FAHRZEUGE.Bezeichnung

beides aus dem Kopf, sollte aber eigentlich klappen...

Problematisch könnte lediglich die Datenmenge werden, d.h. dass die Abfrage halt langsam wird. In einer SP hast Du da mehr Möglichkeiten...

[user0815]

Code:

GROUP BY
  ...
  Having (Max(ABGABEDATEN.Kilometerstand) - Min(ABGABEDATEN.Kilometerstand))>0

Danke, das hat schonmal geholfen.
Die Geschwindigkeit ist auch OK, habe 3700 Abgabedaten und 88 Fahrzeuge (prepared in 0.004 sec, processed in 0.137 sec).

Ich werde mich aber mal mit SP beschäftigen.

[user0815]

... ich komme nicht weiter.

Eine dynamische Anzahl von Parametern (x Fahrzeugnummern) kann ich nicht an eine SP übergeben oder?
Nur eine Fahrzeugnummer + Zeitraum (Datum von, bis), wie nutze ich das dann für die Ausgabe von x Fahrzeugen im Fastreport?
Einen Query für die Fahrzeuge (Master) und einen für die Abgabedaten (Detail) mit den Feldern Kilometerstand, AbgabemengeLiter, DatumUhrzeit (Detail nur für den Zeitraum), verworfen.

Einen Query für alle benötigten Daten (x Fahrzeuge, Kilometerstand, AbgabemengeLiter, DatumUhrzeit) und diesen im Fastreport ausgeben. Das funktioniert, aber wie kann ich jetzt dazu auch den Durchschnittsverbrauch ausgeben (am Ende der Auflistung zu einem Fahrzeug)? Habe ja die Einschränkungen das mindestens 2 Abgaben vorhanden sein müssen, wenn nur 1 oder keine Abgabe vorhanden ist müsste zumindest ein Hinweistext ausgegeben werden.
Löse ich das ganze über ein Script in dem ich die Werte in eine TStringList sicher & dann die Berechnung selber durchführe oder geht es auch anders ???

Stehe irgendwie auf dem Schlauch.
user0815

[Lemmy]

Hallo,

eine dynamische Anzahl von Parametern habe ich bisher noch nicht an eine SP übergeben, den Bedarf hatte ich bisher nur einmal, weil die Datenmenge einfach zu groß war.

Aber bei dir: Lass dir doch von der SP alle Daten der Fahrzeuge ermitteln und mach dann ein

Code:

Select * from SPName where Fahrzeugnummer in ();

dann hast Du die Einschränkung.


Den Durchschnittsverbrauch kannst du dir in FastReport ja durch eine entsprechende Summierung ermitteln. Sollten jetzt "fehlerhafte" Daten da sein (gefahrene km=0) und du diese entsprechend markiert haben willst, kommt es drauf an, welchen FR du hast:

ohne Scripting (kostenlose oder Standard Edition): Du kannst die entsprechenden Daten hervorheben (highlight) mit einer entsprechenden Bedingung (gef. km =0) und diese z.B. rot darstellen

mit Scripting: wie oben, zusätzlich noch die Möglichkeit bei der Druckvorschau z.B: einen Hinweisdialog anzeigen zu lassen... usw.

Grüße

[p80286]

Zitat von user0815:

Eine dynamische Anzahl von Parametern (x Fahrzeugnummern) kann ich nicht an eine SP übergeben oder?

warum willst Du das?

versuch doch so etwas:

Code:

select Fahrzeugno, durchsch(Fahrzeugno)
from Fahrzeuge
where Fahrzeugno in (No1...Nox)

Gruß
K-H

(nicht flink genug)