Look into select into or just insert. The mysql doc covering this might even have a suitable example. On Jul 23, 2011 8:42 AM, "Velen Vydelingum" <ve...@biz-mu.com> wrote: > Hi, > > I have 2 tables and need to create a 3rd one as follows: > > Table Sales: > Code Price sQty sDate > 123 12.00 2 2011-03-12 > 190 10.00 1 2011-03-14 > 123 13.00 2 2011-03-29 > 123 13.00 3 2011-03-31 > 190 11.00 2 2011-03-31 > > > Table Costs > Code Cost hDate > 123 8.00 2011-03-01 > 123 9.50 2011-03-09 > 190 7.00 2011-03-09 > 123 9.00 2011-03-13 > 190 8.00 2011-03-13 > 190 9.00 2011-03-30 > 123 9.50 2011-03-30 > > > I want to have the results for Table Profit as follows: > > Table Profit > Code Cost Sales Profit > 123 65.50 159.50 94.00 > 190 26.00 44.00 18.00 > > the cost should be a sum of sQty * cost for each item using the cost which
> is prevailing at sDate, that is where hDate<=sDate > the sales is the sum of sQty * Price for each item Code > profit is the difference between cost and sales > > Can anyone help me to have 1 query to build up this 3rd table (Profit)? > > Thanks. > > Regards, > > > Velen > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=ag4ve...@gmail.com >
