根据平均购买率和销售率计算运营利润

Question

I am trying to calculate my profit, product stock quantity and value from purchase and sale. Suppose my purchase table as

and my sale table as

Currently I had done it successfully using php. But because of I am having over 100k purchase and sale, it is working very slowly. So I need to get a mysql solution. The following table describes my profit/stock calculation method.

1. merge purchase and sale and then sort in date wise.
2. Balance stock value depends only average purchase rate, not sale rate.
3. profit = (sold rate - average purchase rate) * sold quantity

So I need to get when I take the Report of 2017-11-23

• Profit = 602.38
• Stock Quantity = 110
• Stock Value = 9052.3806

Which will be a fast working method to get the above result, a simple select query or using stored procedure/function? if it is better a simple query, how should be it?

Answer 1

By the time, you've commented about wanting result for each Product_id, I almost done with my solution for displaying Net Profit,Stock Quantity and Stock value within given timeframe.

Hence, I am sharing my approach here anyway as It may be helpful to you or someone else who'll try to solve this problem.

Solution:

select
       final.profit,
       final.Balance_Stock_Quantity,
       final.Balance_Stock_Value
from
(
select tmp.date_,
       tmp.`Sale/Purchase`,
       tmp.product_id,
       tmp.quantity,
       tmp.rate,
       tmp.val,          
(
  case when tmp.`Sale/Purchase` = 'purchase' 
            then (@total_quant := @total_quant + tmp.quantity)
       else      (@total_quant := @total_quant - tmp.quantity)
  end
) as Balance_Stock_Quantity,
(
  case when tmp.`Sale/Purchase` = 'purchase' 
            then (@total_val := @total_val + tmp.val)
       else      (@total_val := @total_val - (@total_rate*tmp.quantity))
  end
) as Balance_Stock_Value,
(
  case when tmp.`Sale/Purchase` = 'purchase' 
            then (@total_rate := @total_val/@total_quant) 
       else @total_rate
  end
) as Balance_Stock_Rate,
(
  case when tmp.`Sale/Purchase` = 'sale' 
            then (@profit := (tmp.rate - @total_rate)*tmp.quantity) 
       else @profit
  end
) as profit

from
(
(select p_date as date_,
       'Purchase' as `Sale/Purchase`,
       p_product_id as product_id,
       p_quantity as quantity,
       p_rate as rate,
       (p_quantity * p_rate) as val
from purchase
where p_date BETWEEN '2017-11-23 00:00:00' AND '2017-11-23 05:00:00'
)
union all
(select s_date as date_,
       'Sale' as `Sale/Purchase`,
       s_product_id as product_id,
       s_quantity as quantity,
       s_rate as rate,
       (s_quantity * s_rate) as val
from sales
where s_date BETWEEN '2017-11-23 00:00:00' AND '2017-11-23 05:00:00' 
)
)tmp
cross join
(select 
        @total_quant := 0,
        @total_val := 0,
        @total_rate := 0, 
        @profit := 0) r
order by tmp.date_
)final
order by final.date_ desc
limit 1
;

DEMO

Note 1: I started to solve this problem with a lot of excitement as I found this a challenging one, but at the end I felt that I am basically doing the same thing which you've already done with some Programming language (for example,task like case...when and using variables to maintain previous values of Profit,Quantity etc.

So I am really not sure, how this will efficient than your current approach, but I guess it's still worth a try.

Note 2: If your goal is to display Profit, Stocks and Values for each product, I think you should stick with your current approach.

Although, It'll make your task easy if you store Total quantity, Rate and Total value (shown in 2nd image in your question) for each product in your Purchase and Sales table itself. Calculate and store these values while inserting other values in these tables if it's affordable for you. This will save a lot of time and effort when you write query for Final Report.

Hope it helps!