title: Calculate the exact margin from sales orders and purchase orders
body: <p>I  am trying to generate a report which calculates the margin from the below database. The problem is that the cost (existing in purchase_order_products table) of the product may change. </p>
  
  <p>The cost of product with id 4022 on 2017-06-08 is 1110, however its cost is 1094 on 2017-07-25. This is confusing. I am unable to get the exact cost for each product sold. </p>
  
  <p>I wrote a PHP algorithm which loops through all orders and purchase orders and used the oldest cost to newest cost. but the algorithm has a very high time complexity. Can this be done just using mysql query?</p>
  
  <p>Please check below scenario: </p>
  
  <p>Company created a purchase order for product X: quantity 3, cost 10. on day 1</p>
  
  <p>Customers bought 2 product X sell price: 12 on day 1 (still have 1 item in inventory with cost 10)</p>
  
  <p>Company  created a purchase order for product X : quantity 4, cost 9. on day 2</p>
  
  <p>Customers bought 3 product X sell price: 12 on day 2</p>
  
  <p>Customers bought 2 product X sell price: 12 on day 3</p>
  
  <p>Company  created a purchase order for product X : quantity 2, cost 11. on day 3</p>
  
  <p>Customers bought 2 product X sell price: 12 on day 3</p>
  
  <p><strong>The report:</strong> </p>
  
  <p>day 1: 
  sold 2 product X  for 12 , cost 10 , profit: 2 * (12 - 10)</p>
  
  <p>day 2: 
  sold 3 product X for 12 , 1 item has a cost of 10, 2 items have a cost of 9 , </p>
  
  <p>profit: 1 * (12 - 10) + 2 * (12 - 9)</p>
  
  <p>day 3: 
  sold 2 product X for 12 , cost 9 , profit: 2 * (12 - 9)</p>
  
  <p>sold 2 product X for 12 , cost 11 , profit: 2 * (12 - 11)</p>
  
  <p>Therefor the profit of newly sold products is calculated using the their corresponding cost. Hope you got my point.</p>
  
  <p><strong>Databse Structure:</strong>
  <a href="https://i.stack.imgur.com/Ybg8o.png" rel="noreferrer"><img src="https://i.stack.imgur.com/Ybg8o.png" alt="Database tables"></a></p>
  
  <p><strong>4 Products From Database</strong></p>
  
  <p><a href="https://i.stack.imgur.com/CIR9G.png" rel="noreferrer"><img src="https://i.stack.imgur.com/CIR9G.png" alt="enter image description here"></a></p>
  
  <p><strong>Products Purchase orders for the above products</strong></p>
  
  <p><a href="https://i.stack.imgur.com/qCdq0.png" rel="noreferrer"><img src="https://i.stack.imgur.com/qCdq0.png" alt="enter image description here"></a></p>
  
  <p><strong>Sold Products</strong>
  <a href="https://i.stack.imgur.com/BotCs.png" rel="noreferrer"><img src="https://i.stack.imgur.com/BotCs.png" alt="enter image description here"></a></p>
  
  <p><a href="https://1drv.ms/u/s!AnLXMGMAcW07hCj06lZ_WLB-GtP4" rel="noreferrer">Dump File Attached here</a></p>