How to calculate the profit of sales orders for a given time if the cost if variable?

Question

I have the below tables in database, I need to calculate the revenue(sum of sell price of all orders ), the cost (sum of all costs of all sold products) and the profit (the revenue minus the cost). However the cost is variable. The cost of an item may be changed with time.

Example: I bought 10 items of item X for $ 5 3 items were sold. Then I bought 3 items of X for $ 4

I tried getting the average cost of item x and then use it to calculate the profit, but it is not a good approach.

How can I know the cost of item x for each sales order? Is there a way to do that?

I don't want a code . I just need an idea for solving this issue!

• sales_order

  id

  grand_total (sum of sell_price of its items)

  order_status (pending/complete)

  delivery_date (date)

• sales_order_item

  id

  sales_order_id

  product_id (unique)

  quantity (ordered quantity)

  sell_price (sell price for single item)

• purchase_order

  id

  created_at

  status (pending / approved)

• purchase_order_items

  id

  purchase_order_id

  product_id

  cost (cost of each item)

Answer 1

Well if I understand correctly, you can fetch purchase_order_items and sales_order_item (with product_id) and for each product_id income is sell_price * quantity and cost is cost * quantity.

UPDATE: Using inner join.

UPDATE: If you are not keeping the actual variable that effects the cost then the only solution is machine learning or statistics (probability theory).