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=arch...@jab.org
