I'm trying to optimize my current query which is calculating profit based on last purchase's net amount, depending upon product's selling date and quantity in sale's table. Currently, it's consuming too much memory and time.
Spacking is an item's selling unit. For Example, if spacking of x product is 10 which means it could be sold in 10 pieces, or let's say a box contains 10 items, but we count it as 1 box, but we also sell it in pieces, so spacking would be 10 for that item. Then net amount of that item per unit would be net_amount/spacking.
In the nested query, I'm calculating the price of a unit by ((total amount of that product id /quantity))/spacking and multiplying it with the current selling quantity, then subtracting it from the total selling price which will give me a profit of that product.
Product
| pcode (varchar(10)) | product_name (varchar(50)) | spacking (int) | Price (double) |
|---|---|---|---|
| 1343 | Chocolates Box | 10 | $20 |
| 1344 | Panadol Strip | 1 | $10 |
| 1345 | Neurobion Inj | 5 | $2 |
Purchase
| Product_code (FKey - Product's pcode) | date (date) | Full_item (int) | Pieces (int) | Bonus (int) | Discount (double) | Net Amount/Total Amount (double) |
|---|---|---|---|---|---|---|
| 1343 | 23-03-2021 | 10 | 0 | 2 | 2% | $130 |
| 1343 | 22-03-2021 | 10 | 0 | 1 | 2% | $130 |
| 1344 | 22-03-2021 | 5 | 0 | 5% | $15 | |
| 1344 | 1-03-2021 | 5 | 0 | 5% | $10 | |
| 1343 | 22-03-2021 | 10 | 0 | 1 | 2% | $130 |
| 1343 | 21-03-2021 | 10 | 0 | 0 | 2% | $130 |
Sale
| Product_code (FKey - Product's pcode) | date (date) | Full_item (int) | Pieces (int) | Bonus (int) | Discount (double) | Net Amount/Total Amount (double) |
|---|---|---|---|---|---|---|
| 1343 | 23-03-2021 | 2 | 0 | 0 | 0% | $40 |
| 1344 | 22-03-2021 | 1 | 0 | 0 | 0% | $10 |
| 1343 | 21-03-2021 | 1 | 0 | 0 | 0% | $20 |
| 1343 | 22-03-2021 | 0 | 9 | 0 | 0% | $18 |
| 1343 | 21-03-2021 | 1 | 0 | 0 | 0% | $20 |
In sale, I'm adding all the sale's transaction for each item that was purchased, each day it may have a different purchased price, to get the profit, we have to subtract the selling price from the latest purchasing price of an item. So date also plays a role here to extract the exact profit, as the sale of an item can be from different dates.
Hence, I wrote a query below which fetches all the records of sale along with its profit on each product.
for a single day, 461 rows, Query took 4.2298 seconds. for multiple days' record, it takes a long time to process.
select
product.product_name,
product.pcode,
product.spacking,
(
select
round(sale.net_amount - ( (round((p.net_amount / (p.full_item + p.bonus_full)), 2) / product.spacking)* ((sale.full_item + sale.bonus_full)* product.spacking ) + (round((p.net_amount / (p.full_item + p.bonus_full)), 2) / product.spacking)* (sale.pieces)), 2
)
from
purchase p
where
p.invoice_date <= sale.invoice_date
and sale.product_code = p.product_code
order by
p.invoice_date desc LIMIT 1)) as pnet_amount,
DATE_FORMAT(sale.invoice_date, '%M %d %Y') as date,
sale.invoice_number,
sale.full_item,
sale.pieces,
sale.bonus_full_item,
sale.price,
sale.discount,
sale.net_amount
from
sale
join
product
on product.pcode = sale.product_code
join
customer
on customer.ccode = sale.customer_code
where
sale.invoice_date BETWEEN '2021-03-14' and '2021-03-16'
order by
sale.invoice_date desc
