problem mit komplizierter sql abfrage

[polondo]

Hallo,

Ich habe 2 Tabellen aus denen ich folgende Information abrufen möchte: Zu einer bestimmten Produkt_id die sich in beiden Tabellen befindet, sollen alle Produkteingänge und Produkabgänge summiert werden und schließlich voneinander abgezogen um den Lagerbestand zu erhalten. Dazu habe ich bereits mit Hilfe anderer User folgende Abfrage:

SQL-Code:

select
  ((SELECT isnull(SUM(quantity),0) FROM productbookingpos
    WHERE prod_ID = 1907
    AND invalid =0)
   -
   (SELECT isnull(SUM(quantity),0) FROM invoicepos
    WHERE prod_ID = 1907
    AND invalid =0)) AS ERGEBNIS

Hierbei habe ich nun das Problem das ich keine Richtigen Werte bekomme wenn mein Produkt bzw. Produkt_id mehrere Ablaufdaten hat(expdates).
Um das Problem zu lösen habe ich nun folgendes gebastelt:

SQL-Code:

SELECT distinct expdate,isnull(SUM(quantity),0) as Input FROM productbookingpos
WHERE prod_ID = 1907 (beispiel)
AND invalid =0
GROUP BY expdate

SELECT distinct expdate,isnull(SUM(quantity),0) as Output FROM invoicepos
WHERE prod_ID = 1907 (beispiel)
AND invalid =0
GROUP BY expdate

Das Ergebnis sieht so aus:

Für den Produkteingang:
expdate quantity
04/04/30 98
04/12/30 361
10/10/30 72

Für den Produktausgang:
expdate quantity
04/04/30 97
04/12/30 359

Wie ist es nun möglich diese beiden Abfragen zusammenzuschmeißen und dabei pro expdate die Differenz als Lagerbestand zu bekommen.
Das mein Ergebnis also folgendermaßen aussieht:

expdate Restbestand
04/04/30 1
04/12/30 2
10/10/30 0 (oder aber garnichts angezeigt da ja kein passender Eintrag im Produtkausgang vorhanden ist)

Ich wär für alle Antworten sehr dankbar, da mich das ganz schon zu lange beschäftigt.
Ich arbeite mit dem SqlServer2000.
Hoffe einer wird schlau aus dem was ich geschrieben hab

mfg R.

[omata]

Hallo polondo,

vielleicht so...

SQL-Code:

SELECT 'productbookingpos & invoicepos', a.prod_id, 
       DATEDIFF(day, a.expdate, b.expdate) tage, 
       COALESCE(SUM(a.quantity), 0) - COALESCE(SUM(b.quantity), 0) ergebnis
FROM productbookingpos a
INNER JOIN invoicepos b        
  ON     a.prod_id = b.prod_id
     AND b.invalid = 0
WHERE a.invalid = 0
GROUP BY a.prod_id, a.expdate, b.expdate

UNION

SELECT 'nur productbookingpos', 
       prod_id, 
       NULL tage, 
       COALESCE(SUM(quantity), 0) ergebnis
FROM productbookingpos a
WHERE invalid = 0
  AND NOT EXISTS (SELECT *
                  FROM invoicepos
                  WHERE prod_id = a.prod_id
                    AND invalid = 0)
GROUP BY prod_id, expdate

UNION

SELECT 'nur invoicepos', 
       prod_id, 
       NULL tage, 
       COALESCE(SUM(quantity), 0) ergebnis
FROM invoicepos a
WHERE invalid = 0
  AND NOT EXISTS (SELECT *
                  FROM productbookingpos
                  WHERE prod_id = a.prod_id
                    AND invalid = 0)
GROUP BY prod_id, expdate

Gruss
Thorsten

[polondo]

Danke erstmal für deine Antwort!
Wenn ich das ausführe meldet mir der query-assistent vom sqlserver folgendes:

Arithmetischer Überlauffehler beim Konvertieren von expression in den Datentyp datetime.
[Microsoft][ODBC SQL Server Driver]Fehler in der Funktionsreihenfolge

[polondo]

entschuldige bitte der Fehler ist dieser hier. habe den falsch reinkopiert.

Server: Nachr.-Nr. 8115, Schweregrad 16, Status 2, Zeile 1
Arithmetischer Überlauffehler beim Konvertieren von expression in den Datentyp datetime.

[omata]

Ok, hab mich schon über die ODBC-Info gewundert. Ich habe das Statement nochmal geändert. Sollte aber trotzdem noch der selbe Fehler kommen. Du möchtest immer alle Zeilen haben auch wenn es in der einen Tabelle gar keinen Eintrag gibt? Deshalb jetzt die drei Abfragen. Allerdings kann nur, wenn in beiden Tabellen ein Datum steht auch eine Differenz gebildet werden. Da war mir schonmal nicht ganz klar was für eine Differenz du meinst. Ich habe jetzt Tage als Einheit gewählt. Dort denke ich wird wohl auch der Fehler sein. Nimm dochmal das DATEDIFF raus (einfach mal mit NULL ersetzten) vielleicht liegt es da dran. Ich kenne deine Daten ja nicht.

Gruss
Thorsten

[polondo]

Wahrscheinlich war meine Erklärung etwas mißverständlich.
Um das ganze vielleicht etwas zu vereinfachen mach ich es mal anders.
Stell dir vor ich habe diese beiden Tabellen mit folgendem Inhalt:

Wareneingang:
expdate datum anzahl
prod_id1 04/04/30 5
prod_id2 04/12/30 8
prod_id3 10/10/30 9

Warenausgang:
expdate datum anzahl
prod_id1 04/04/30 2
prod_id2 04/12/30 4

ich möchte mit einer sql-Abfrage dann zu folgendem Ergebniss kommen:

prod_id datum Restbestand im Lager
prod_id1 04/04/30 3
prod_id2 04/12/30 4

Das heißt einmal geht es darum pro Tabelle eine Summe der Anzahl pro Prod_id & dazugehörigem Datum zu bilden und zum Zweiten dann diese Summen pro prod_id und Datum entsprechend ihrer prod_ids voneinander abzuziehen, sodass ich dann den Restbestand in meinem Lager bekomme.
P.S: Falls Du dich fragst wieso das Datum eine so wichtige Rolle bei dem ganzen spielt dann ist das ganz einfach: Da es sich um ein Lagerverwaltungsprogramm für eine Firma mit Chemikalien handelt, stellt das Datum ein Ablaufdatum dar und ist zigleich mit der Prod_id ein gemeinsamer Schlüssel in der DB.

[Jelly]

Du könntest eine View erstellen, mit der Du dann später deine Abfragen formulierst:

SQL-Code:

create view Bewegungen
as
select expdate, datum, anzahl from Wareneingang
union
select expdate, datum, -anzahl as anzahl from Warenausgang

Mit dieser View hast Du dann Ein- und Ausgänge in einem Select. Ausgänge werden als negativ übernommen, so dass du einfach über die Spalte Anzahl summieren kannst:

SQL-Code:

select expdate, datum, sum(anzahl) as AnzahlCumul
from Bewegungen
group by expdate, datum

[polondo]

Danke erstmal!

An eine View hatte ich auch schon gedacht, allerdings würde ich gerne eine Abfrage haben die alles mit einem Schlag erledigt. Sollte eigentlich dazu dienen um meinen bisherigen Code etwas zu vereinfachen.


mfg R.

[Jelly]

Hast Du doch dann... Die View erstellst Du ja nur einmal auf dem Server... In Delphi benutzt Du nur die 2. Abfrage

[omata]

Wenn das ganze in eine Abfrage soll...

SQL-Code:

SELECT expdate, datum, SUM(anzahl) AS AnzahlCumul
FROM (SELECT expdate, datum, anzahl 
      FROM wareneingang
      UNION
      SELECT expdate, datum, -anzahl AS anzahl 
      FROM warenausgang) x
GROUP BY expdate, datum

Gruss
Thorsten