>>>CREATE CURSOR stock(date d(8),; >>> voucherno n(4),; >>> open1 n(4),; >>> open2 n(4),; >>> arrival1 n(4),; >>> arrival2 n(4),; >>> sold1 n(4),; >>> sold2 n(4),; >>> closing1 n(4),; >>> closing2 n(4)) >>> >>>INSERT INTO stock VALUES ({^2012-07-01},155,15,30,0,0,0,0,15,30) >>>INSERT INTO stock VALUES ({^2012-09-09},156,0,0,10,0,0,0,0,0) >>>INSERT INTO stock VALUES ({^2012-09-09},157,0,0,9,0,0,0,0,0) >>>INSERT INTO stock VALUES ({^2012-09-10},158,0,0,10,0,0,0,0,0) >>>INSERT INTO stock VALUES ({^2012-09-11},159,0,0,0,15,0,0,0,0) >>>INSERT INTO stock VALUES ({^2012-09-21},160,0,0,0,0,30,60,0,0) >>>>>>
>>CREATE CURSOR stock(date d(8),; >> voucherno n(4),; >> open1 n(4),; >> open2 n(4),; >> arrival1 n(4),; >> arrival2 n(4),; >> sold1 n(4),; >> sold2 n(4),; >> closing1 n(4),; >> closing2 n(4)) >> >>INSERT INTO stock VALUES ({^2012-07-01},155,15,30,0,0,0,0,15,30) >>INSERT INTO stock VALUES ({^2012-09-09},156,0,0,10,0,0,0,0,0) >>INSERT INTO stock VALUES ({^2012-09-09},157,0,0,9,0,0,0,0,0) >>INSERT INTO stock VALUES ({^2012-09-10},158,0,0,10,0,0,0,0,0) >>INSERT INTO stock VALUES ({^2012-09-11},159,0,0,0,15,0,0,0,0) >>INSERT INTO stock VALUES ({^2012-09-21},160,0,0,0,0,30,60,0,0) >> >>SELECT Stock.Date,; >> Stock.voucherno,; >> SUM(IIF(Tbl1.voucherno== Stock.voucherno,000000, Tbl1.Closing1+Tbl1.Arrival1-Tbl1.Sold1)) AS Open1,; >> SUM(IIF(Tbl1.voucherno== Stock.voucherno,000000, Tbl1.Closing2+Tbl1.Arrival2-Tbl1.Sold2)) AS Open2,; >> MAX(Stock.Arrival1) AS Arrival1,; >> MAX(Stock.Arrival2) AS Arrival2,; >> MAX(Stock.Sold1) AS Sold1,; >> MAX(Stock.Sold2) AS Sold2,; >> SUM(Tbl1.Closing1+Tbl1.Arrival1-Tbl1.Sold1) AS Closing1,; >> SUM(Tbl1.Closing2+Tbl1.Arrival2-Tbl1.Sold2) AS Closing2; >>FROM Stock; >>LEFT JOIN Stock Tbl1 ON Stock.voucherno >= Tbl1.voucherno ; >>GROUP BY Stock.Date, Stock.voucherno; >>ORDER BY Stock.Date, Stock.voucherno >>>