[Mysql]- Wert einer Spalte abhängig vom Wert auf zwei Spalten verteilen

fish-guts

fish-guts

Erfahrenes Mitglied
Hi alle

Ich schreibe gerade eine mySQL Abfrage für einen Report und benötige hier etwas Hilfe, bzw. einen Hinweis, ob das überhaupt möglich ist.

Ich habe eine Tabelle INVENTAR, diese besteht aus den Spalten
Code: 
MASCHINEN_ID (pk)
TEILE_ID (fk)
HISTORY_ID
LAGERBESTAND
AENDERUNGSDATUM

Vorab sei erwähnt, dass Änderungen an der Tabelle ausgeschlossen sind.

Ich habe nun folgendes Problem:

Ich muss einen Report erstellen, der den Verbrauch der Teile aufzeigt. Da ich nur die Spalte Lagerbestand zur Verfügung habe, i habe ich mir folgende Gedanken gemacht:

- Wie kann ich das nur mit dem Lagerbestande lösen
- Da ich die Spalte "HISTORY_ID" habe, habe ich mir überlegt, ob ich mit den Deltas arbeiten will.
Beispiel:

  1. Mein erster Record in einem Monat hat für die TEILE_ID 1 einen Lagerbestand von 5000.
  2. Ich erfasse das zu Beginn des Monats. Es wird ein Record erstellt mit HISTORY_ID = 1.
  3. Nun kommen 2000 Teile dazu. Ich setze den Lagerbestand auf 7000. Es wird ein neuer Record erstellt mit HISTORY_ID = 2.
  4. Nun werden 3000 Teile verbraucht. Ich setze den Lagerbestand auf 4000. Es wird ein neuer Record erstellt mit HISTORY_ID = 3
  5. Am Ende des Monats werden nochmals 1000 Teile verbraucht. Ich setze den Lagerbestand auf 3000. Es wird ein neuer Record erstellt, mit HISTORY_ID = 4

Meine Abfrage soll nun folgende Fragen beantworten:


  • Wieviele Teile habe ich diesen Monat verbraucht?
  • Wieviele Teile habe ich diesen Monat erhalten?
  • Wieviele Teile habe ich ende Monat noch übrig?

Dies für jeden Monat über ein Jahr verteilt und für eine bestimmte MASCHINEN_ID.

Ist so etwas überhaupt möglich?

Vielen Dank für eure Hilfe.


fish-guts
 
Hallo

Danke für deine Zeit. Ganz genau so wird siehts nachher aus.

Für jeden neuen Lagerbestand wird eine neue HISTORY_ID generiert.

// EDIT: Wichtig noch: Das Datum muss nicht zwingend fortlaufen sein. Diese fallen sehr unterschiedlich aus. Ich msus dann einfach das erste Datum das für einen Monat erfasst worden ist nehmen, selbiges für den monatlichen Lagerendbestand.

Grüsse


fish-guts
 
Zuletzt bearbeitet:
Ungetestet:
SQL: 
SELECT 
  calc.MASCHINEN_ID,
  calc.TEILE_ID,
  SUM(calc.veraenderung) AS bestand,
  SUM(CASE WHEN calc.veraenderung >= 0 THEN calc.veraenderung ELSE 0 END) AS erhalten,
  SUM(CASE WHEN calc.veraenderung < 0 THEN calc.veraenderung ELSE 0 END) AS verbraucht,
  MIN(calc.AENDERUNGSDATUM) AS monat
FROM
  (
  SELECT
    act.MASCHINEN_ID,
    act.TEILE_ID,
    --act.HISTORY_ID AS HISTORY_ID_aktuell, 
    --prev.HISTORY_ID AS HISTORY_ID_vorher, 
    CASE WHEN act.HISTORY_ID = 1 AND prev.HISTORY_ID = 1
      THEN act.LAGERBESTAND
      ELSE act.LAGERBESTAND - prev.LAGERBESTAND
    END AS veraenderung,
    act.LAGERBESTAND AS lager_aktuell,
    --prev.LAGERBESTAND AS lager_vorher,
    act.AENDERUNGSDATUM
  FROM 
    INVENTAR act
  INNER JOIN
    INVENTAR AS prev
  ON
    act.MASCHINEN_ID = prev.MASCHINEN_ID
    AND
    act.TEILE_ID = prev.TEILE_ID
    AND 
    (
      (act.HISTORY_ID = (prev.HISTORY_ID + 1)) 
      OR 
      (act.HISTORY_ID = 1 AND prev.HISTORY_ID = 1)
    )
  ) calc
GROUP BY 
  calc.MASCHINEN_ID,
  calc.TEILE_ID,
  MONTH(calc.AENDERUNGSDATUM);
 
Zuletzt bearbeitet von einem Moderator:
Vielen Dank, das sieht schon gut aus, leider noch nicht ganz korrekt. Ich habe die Query noch ergänzt, mit der Einschränkung auf dieses Jahr:

Was noch berücksichtigt werdn sollte sind allfällige Anfangsbestände. Ich versuche das mal an einem konkreten Beispiel zu erläutern:

Das hier ist eine Zeile des Output dieser Query.

MASCHINEN_ID, TEILE_ID, bestand, erhalten, verbraucht, monat

85268 85171 0.000 1.000 -1.000 2
85268 85171 0.000 1.000 -1.000 3
85268 85171 1.000 1.000 0.000 4

Diese Einträge sind alle von 2013. Soweit so gut. Nun habe ich für Februar und März je zwei Einträge in meiner DB.

Februar:

12.02.2013: Bestand 1
13.02.2013: Bestand 0

Das sind alle Einträge im Februar.

Der Output müsste also wie folgt aussehen:

85268 85171 0.000 0.000 1.000 2

Sprich, ich hatte Anfang Monat (am 12.02) bereits einen Lagerbestand von 1.

Beim nächstgen Eintrag der zugleich auch der letzte in diesem Beispiel ist ist der Bestand 0.

Das heisst, diesen Monat sind keine Teile dazukommen und 1 wurde verbraucht.

Kann man das irgendwie so realisieren?

Es sollte natürlich auch für mehr Einträge gehen, also wenn ich zum Beispiel am

12.02 Bestand 1
13.02 Bestand 7
14.02 Bestand 5
16.02 Bestand 12
17.02 Bestand 8

Dann sollte der Output sein:

85268 85171 8.000 13.000 6.000 2

Ist das verständlich erklärt? :)

Vielen Dank nochmals für deine Hilfe.
 
teste mal sowas

SQL: 
SELECT 
  calc.MASCHINEN_ID,
  calc.TEILE_ID,
  SUM(CASE WHEN calc.HISTORY_ID_aktuell = 1 AND calc.HISTORY_ID_vorher = 1 THEN calc.lager_aktuell ELSE calc.veraenderung END) AS bestand,
  SUM(CASE WHEN calc.veraenderung >= 0 THEN calc.veraenderung ELSE 0 END) AS erhalten,
  SUM(CASE WHEN calc.veraenderung < 0 THEN ABS(calc.veraenderung) ELSE 0 END) AS verbraucht,
  MIN(calc.AENDERUNGSDATUM) AS monat
FROM
  (
  SELECT
    act.MASCHINEN_ID,
    act.TEILE_ID,
    act.HISTORY_ID AS HISTORY_ID_aktuell, 
    prev.HISTORY_ID AS HISTORY_ID_vorher, 
    act.LAGERBESTAND - prev.LAGERBESTAND AS veraenderung,
    act.LAGERBESTAND AS lager_aktuell,
    --prev.LAGERBESTAND AS lager_vorher,
    act.AENDERUNGSDATUM
  FROM 
    INVENTAR act
  INNER JOIN
    INVENTAR AS prev
  ON
    act.MASCHINEN_ID = prev.MASCHINEN_ID
    AND
    act.TEILE_ID = prev.TEILE_ID
    AND 
    (
      (act.HISTORY_ID = (prev.HISTORY_ID + 1)) 
      OR 
      (act.HISTORY_ID = 1 AND prev.HISTORY_ID = 1)
    )
  ) calc
GROUP BY 
  calc.MASCHINEN_ID,
  calc.TEILE_ID,
  MONTH(calc.AENDERUNGSDATUM);
 
Zuletzt bearbeitet von einem Moderator:
Hallo BaseBallBatBoy

Vielen Dank für deine Bemühungen. Das sieht alles sehr gut aus, habe noch Abweichungen bezüglich der Inventardaten, sollte diese aber selber ausmerzen können.

Ich lase das Thema für alle Fälle mal noch offen, schliesse es dann wenn alles ok ist.

Danke nochmals und eine schönen Tag.

Grüsse

fish-guts
 
Hi

Ich habe nun folgende (ergänzte) Query:

Code: 
SELECT 
    CALC.INSTALLATION_ID,
    CALC.COMPONENT_ID,
    CALC.PREVIOUS_STOCK,
    CALC.ROB,
    CALC.HISTORY_ID_PREVIOUS,
    SUM(CASE WHEN CALC.STOCK_CHANGE >= 0 THEN CALC.STOCK_CHANGE ELSE 0 END) AS QTY_RECEIVED,
    SUM(CASE WHEN CALC.STOCK_CHANGE < 0 THEN ABS(CALC.STOCK_CHANGE) ELSE 0 END) AS QTY_CONSUMED,
    MONTH(MIN(CALC.LAST_CHANGE)) AS STOCK_MONTH

FROM
(
SELECT
    CUR.INSTALLATION_ID,
    CUR.COMPONENT_ID,
    CUR.HISTORY_ID AS HISTORY_ID_CURRENT, 
    --PREV.HISTORY_ID AS HISTORY_ID_PREVIOUS,
    (SELECT 
        MAX(INVENTORY.HISTORY_ID)
        FROM INVENTORY
        WHERE YEAR(INVENTORY.LAST_CHANGE) = 2013
        AND MONTH(INVENTORY.LAST_CHANGE) = 6
        AND INVENTORY.INSTALLATION_ID = 86484
        AND INVENTORY.COMPONENT_ID = CUR.COMPONENT_ID
    ) AS CURRENT_HISTORY_MAX,
    (SELECT 
        INVENTORY.ON_STOCK
        FROM INVENTORY
        WHERE INVENTORY.HISTORY_ID = CURRENT_HISTORY_MAX
        AND MONTH(INVENTORY.LAST_CHANGE) = 6
        AND INVENTORY.INSTALLATION_ID = 86484
        AND INVENTORY.COMPONENT_ID = CUR.COMPONENT_ID
    ) AS ROB,



    
    CUR.ON_STOCK - PREV.ON_STOCK AS STOCK_CHANGE,
    CUR.ON_STOCK AS CURRENT_STOCK,
    --PREV.ON_STOCK AS PREVIOUS_STOCK,

    PREV.LAST_CHANGE
    FROM INVENTORY CUR
    INNER JOIN INVENTORY AS PREV
    ON CUR.INSTALLATION_ID = PREV.INSTALLATION_ID
    AND CUR.COMPONENT_ID = PREV.COMPONENT_ID
    AND 
    (
        (CUR.HISTORY_ID = (PREV.HISTORY_ID + 1)) 
        OR 
        (CUR.HISTORY_ID = 1 AND PREV.HISTORY_ID = 1)
    )
    WHERE MONTH(CUR.LAST_CHANGE) = 6
) AS CALC
WHERE YEAR(CALC.LAST_CHANGE) = 2013
AND MONTH(CALC.LAST_CHANGE) = 6
AND CALC.INSTALLATION_ID = 86484
GROUP BY 
    CALC.INSTALLATION_ID,
    CALC.COMPONENT_ID,
    MONTH(CALC.LAST_CHANGE)

Hinweis: INSTALLATION_ID = MASCHINEN_ID, TEILE_ID = COMPONENT_ID

Die Verbrauchten und erhaltenen Teile werden grundsätzlich korrekt berechnet. Ich habe jetzt eine neue Anforderung bekommen:

Als Ausgangswert muss jedesmal der letzte Wert vor dem ersten Wert des Monats genommen werden.

Beispiel:

In der Query verwende ich Juni. Also soll als Ausgangswert für den Lagerbestand (ON_STOCK) der letzte Eintrag vom Mai verwendet werden. Wenn im Mai kein Eintrag vorhanden soll einfach der letzte vorhandene Wert genommen werden, auch wenn dieser vom letzten Jahr ist.

Hinweis: Die HISTORY_ID ist für jedes Teil fortlaufend, sprich man könnte einfach auf die letzte HISTORY_ID vor dem Ersten Eintrag des Monats gehen. Ich weiss nur noch nicht wie.

Ich denke, dass wir hier einfach etwas mit dem JOIN für PREV machen müssen. Hinweise?

Vielen Dank

Gruss

fish-guts
 
> Als Ausgangswert muss jedesmal der letzte Wert vor dem ersten Wert des Monats genommen werden.

Ausgangswert wovon? ROB? Received? Consumed? Verstehe momentan nicht genau wie du das meinst...
Mach doch kurz zwei Rechnungsbeispiele. Eines wo es einen Vormonatswert gibt und eines wo es keinen gibt (weil tiefste Hist_id für den gewählten Monat = 1).

PS: bist du sicher dass du PREV.LAST_CHANGE willst? Nicht CUR? Und warum holst du dir ROB so? Das soll doch der Endbestdand der Periode sein, oder?
 
Hallo

Sorry für die späte Rückmeldung, war grade zwei Wochen offline.

> Als Ausgangswert muss jedesmal der letzte Wert vor dem ersten Wert des Monats genommen werden.


Es geht dabei um den ROB Wert. Die Idee dabei ist, dass folgendes berücksichtigt wird (nochmals Vollständig, um Klarheit zu schaffen):

1. Für jeden Monat des Jahres soll ja der Verbrauch bestimmt werden, und zwar relativ zum Vormonat.
2. Wenn im Vormonat kein Wert vorhanden ist soll der letzte existierende Wert genommen werden.
3. Ich muss für jeden Monat wissen, wieviel verbraucht und wieviel hinzugekommen ist.
4. Ich muss dann für jeden Monat wissen, wie Ende Monat der Lagerbestand aussieht (ROB -> Remaining on Board)


Beispiel:

Ich habe die Installation 12345 und habe im Mai noch 50 Lagerbestand.

Im August verbrauche ich 20 und es kommen 30 hinzu. Da für den Mai und den Juli keine Werte vorliegen, soll von den 50 vom Mai ausgegangen werden. Nach dem letzten Eintrag im August habe ich also noch 60 ROB.

Hinweis: Es kann vorkommen, dass mehr als ein Eintrag pro Tag verfasst. Dies muss allenfalls auch noch berücksichtigt werden.

Vielen Dank für die Unterstützung und liebe Grüsse

fish-guts
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück