List:General Discussion« Previous MessageNext Message »
From:Velen Vydelingum Date:July 23 2011 12:42pm
Subject:rebuilding table
View as plain text  
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



Thread
MySQL refusing to accept passwordsTim Thorburn22 Jul
  • Re: MySQL refusing to accept passwordsJohan De Meersman22 Jul
  • Re: MySQL refusing to accept passwordsMySQL)22 Jul
    • Re: MySQL refusing to accept passwordsMySQL)22 Jul
      • Re: MySQL refusing to accept passwordsClaudio Nanni23 Jul
    • Re: MySQL refusing to accept passwordsTim Thorburn23 Jul
      • Re: MySQL refusing to accept passwordsJohan De Meersman23 Jul
      • Re: MySQL refusing to accept passwordsMySQL)25 Jul
        • Re: MySQL refusing to accept passwordsmisiaq26 Jul
  • rebuilding tableVelen Vydelingum23 Jul
    • Re: rebuilding tableshawn wilson23 Jul