SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    May 2003
    Location
    somewhere on the Earth
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy 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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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. #3
    SitePoint Zealot
    Join Date
    May 2003
    Location
    somewhere on the Earth
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote 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.




    Quote 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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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. #5
    SitePoint Zealot
    Join Date
    May 2003
    Location
    somewhere on the Earth
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what about redesigning the database to make this possible? You know, that's the core of the system

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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


Bookmarks

Posting Permissions

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