CREATE CURSOR Stock (Itemid I, Qty N(7,2), Batch C(4), DATE D, Stopdelv L)
INSERT INTO Stock VALUES (1,10.00,"001A",{01.01.2010},.F.)
INSERT INTO Stock VALUES (1,15.00,"002A",{01.01.2010},.T.)
INSERT INTO Stock VALUES (3,20.00,"001B",{01.01.2010},.T.)

CREATE CURSOR Production (Itemid I, Qty N(7,2), Batch C(4), DATE D, Stopdelv L)
INSERT INTO Production VALUES (1,10.00 ,"003A",{05.01.2010},.F.)
INSERT INTO Production VALUES (1,15.00 ,"004A",{07.01.2010},.T.)
INSERT INTO Production VALUES (5,20.00,"001B",{01.01.2010},.F.)
INSERT INTO Production VALUES (6,20.00,"002B",{01.01.2010},.F.)

CREATE CURSOR Sales  (Itemid I, Qty N(7,2), Batch C(4), DATE D)
INSERT INTO Sales VALUES (1,5.00,"001A",{01.01.2010})
INSERT INTO Sales VALUES (1,5.00,"002A",{01.01.2010})
INSERT INTO Sales VALUES (1,2.00,"002A",{01.01.2010})
INSERT INTO Sales VALUES (3,2.00,"001B",{01.01.2010})
INSERT INTO Sales VALUES (3,2.00,"001B",{01.01.2010})
INSERT INTO Sales VALUES (3,4.00,"001B",{01.01.2010})
INSERT INTO Sales VALUES (1,9.00,"003A",{05.01.2010})
INSERT INTO Sales VALUES (1,2.00,"004A",{07.01.2010})
INSERT INTO Sales VALUES (1,3.00,"004A",{07.01.2010})
INSERT INTO Sales VALUES (1,3.00,"004A",{07.01.2010})
INSERT INTO Sales VALUES (1,2.00,"004A",{07.01.2010})
INSERT INTO Sales VALUES (5,4.00,"001B",{01.01.2010})
INSERT INTO Sales VALUES (6,3.00,"002B",{01.01.2010})


SELECT pl.Itemid, pl.Batch, pl.Date, ;
		NVL(s.Qty, CAST(0 AS N(7,2))) AS StockQty,  ; 
		NVL(p.Qty,CAST(0 AS N(7,2))) AS ProductionQty,  ; 
		NVL(sl.SalesQty,CAST(0 AS N(7,2))) AS SalesQty,  ; 
		NVL(s.Qty, CAST(0 AS N(7,2))) + ;
			NVL(p.Qty,CAST(0 AS N(7,2))) - ;
			NVL(sl.SalesQty,CAST(0 AS N(7,2))) AS StockLeftQty ;
	FROM ( ;
				SELECT  Itemid, Batch, Date FROM Stock WHERE Stopdelv ;
				UNION ;
				SELECT  Itemid, Batch, Date FROM Production WHERE Stopdelv ;
				) pl ;
	LEFT JOIN Stock s ON s.Itemid = pl.Itemid AND s.Batch = pl.Batch AND s.Date = pl.Date ;
	LEFT JOIN Production p ON pl.Itemid = p.Itemid AND pl.Batch = p.Batch AND pl.Date = p.Date ;
	LEFT JOIN ( ;
				SELECT Itemid, Batch, Date, SUM(Qty) AS SalesQty ;
					FROM Sales ;
					GROUP BY Itemid, Batch, Date ;
				) sl ON sl.Itemid = pl.Itemid AND sl.Batch = pl.Batch AND sl.Date = pl.Date