problem mit komplizierter sql abfrage

[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