1. ## sql language help

For a certain product, build two tables :

table sale: Id, PricePerUnit, Quantity, Time
table purchase: Id, CostPerUnit, Quantity, Time

1. ProfitPerUnit = PricePerUnit - CostPerUnit

2. CostPerUnit might change from time to time ( and so does PricePerUnit). Thus if someone has bought a certain quantity of this product, and the CostPerUnit of all those he bought might vary

The problem for me is how to calculate the ProfitPerUnit for a deal.

Using SQL in MySQL.

Another thing is that, is it ok to merge table sale and table purchase together, just let Quantity of sale be positive and of purchase be negative? What's the disadvantage ?

Thanks!!!

2. it all depends on how a "deal" is defined

as for combining your tables, don't do it unless the purchase and sale are made at the exact same time

rudy

3. Originally Posted by r937
it all depends on how a "deal" is defined
Here's a example:

In table purchase, there are a few records:
Id CostPerUnit Quantity
_____________________________
1 100.00 10
2 110.00 10
3 105.00 10
........

To make all this simple, let there be only one record in table sale:
Id PricePerUnit Quantity
______________________________
1 150.00 15

You see, 15 of this product has been sold.
for the first 10 of 15, their CostPerUnit is 100.
but for the following 5, their CostPerUnit is 110

So the total profit of this deal(a record in table sale, representing an action between a certain customer and the retailer ) is:
Profit=(150-100)*10+(150-110)*5

If we sell 30 instead of 15, the profit should be
(150-100)*10+(150-110)*10+(150-105)*10

If multi-deals is considered, the situation will be more complex.

I think if I want to calculate the profit, I have to know the CostPerUnit. and for a certain deal, there might be many different CostPerUnit (according to the purchase and store), as in the example.

Originally Posted by r937
as for combining your tables, don't do it unless the purchase and sale are made at the exact same time
I see. And could you please tell me the reason? Thank you very much!

4. ah, i get it now

this type of calculation is called FIFO (first in first out)

i think you should calculate the profit in your application script

if you use sql, and the table design as given, you will always have to re-calculate your inventory position going right back to Day 1 for all purchases and all sales

since mysql before 4.1 does not support subselects, i don't even want to think about it

i mean, isolating a particular deal by recalculating from Day 1 is going to be tough enough in application logic, never mind sql

as for merging the purchase and sales tables, i was wrong, somehow i thought you meant having separate sales and purchase columns on the same row, which of course you did not, so go ahead and merge them

rudy

5. what about redesigning the database to make this possible? You know, that's the core of the system

6. yes, you could redesign the tables, that would be nice

but then, all the calculation would still have to be done in the application -- you would get a sale, then you would query the "uncommitted" or "available" purchases to identify the fifo units that the sale will claim, but then -- and this is the key point -- you would have to update those purchases to mark them committed or not available

therefore, you would calculate the profit at that time too

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•