inaliahgle inaliahgle - 27 days ago 9
SQL Question

Get total price and quantity of items sold with price changes

I have these tables:


  • items ( pk:id | Name )

  • priceChanges( pk:id | fk:item_id | price | fromDate | toDate )

  • request( pk:id | deliveryDate)

  • requested_category( pk:id | assigned_quantity | fk:item_id | fk:request_id )



The PriceChanges table keep track of the price's change of every item.

I want to get the items sold between two dates with the total income (price*quantity) for ech item, while the quantity is the total count of ths item sold with the same price in the period (without the item's price change).

That means that whenever the price for a change i get a new row with the total count of items sold with the new price.

Here is an exemple of the desired output :


id Name sold price total
------ ------- ----- ------ ------
8P01 51645A 3 1,20 3,60
8P01 51645A 1 2,82 2,82
8P01 51645A 5 2,50 12,50


i have tried this SQL command, but i can't stop getting too many duplicated row :

SELECT
i.id,
i.name,
sum(rc.assigned_quantity) AS assigned_quantity,
pc.price,
pc.price * sum(rc.assigned_quantity) as total
FROM requested_category rc
LEFT JOIN items i ON rc.item_id = i.id
LEFT JOIN request r ON rc.request_id = r.id
LEFT JOIN priceChanges pc ON pc.item_id = i.id
WHERE
r.delivery_date BETWEEN 'startDate' AND 'endDate'
GROUP BY i.id, i.name, pc.price


the result that i get:


id Name sold price total
------ ------- ----- ------ ------
8P01 51645A 3 1,20 3,60
8P01 51645A 1 1,20 1,20
8P01 51645A 5 1,20 6,00
8P01 51645A 3 2,82 7,86
8P01 51645A 1 2,82 2,82
8P01 51645A 5 2,82 12,10
8P01 51645A 3 2,50 7,50
8P01 51645A 1 2,50 2,50
8P01 51645A 5 2,50 12,50


I tried as requested in comments below the follow query:

SELECT i.id, i.name, rc.assigned_quantity, pc.price
FROM requested_category rc
LEFT JOIN items i ON rc.item_id = i.id
LEFT JOIN request r ON rc.request_id = r.id
LEFT JOIN priceChanges pc ON pc.item_id = i.id
WHERE r.delivery_date BETWEEN 'startDate' AND 'endDate'


I get this result:


id name assigned_quantity price
8P01 51645A 1 1.20
8P01 51645A 1 2.82
8P01 51645A 1 2.50
8P01 51645A 3 1.20
8P01 51645A 3 2.82
8P01 51645A 3 2.50
8P01 51645A 5 1.20
8P01 51645A 5 2.82
8P01 51645A 5 2.50


Thank you

Answer

I came back with the solution I used.
In my application, I use Symfony Framewok and Doctine Framework to handle the database.

i'm not a DBAdmin, but I think that this solution can be achieved by database triggers as well. In this case, I chose the applicatif approach because I used Top-Down design.

The business logic behind the need is trying to get the store output reporting (items*price per period) knowing that the price is fixed at the item's importation time.

I used the Entity Listener that watches for any change in items price. Whenever there is a change, I log the (price, quantity, start date,end date)
And when the reported is generated, I simply match the period with items price change and multiply it with the quantity that is logged in the same database entry.

Hope I helped someone.

Comments