>>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 >This is nice set-based solution, but I think the same rule applies here as to SQL Server up to 2012 - it is better to use cursor based solution. In this case SCAN BASED solution is very striaghtforward.