Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: Calculating movement

    Hello all,
    I've been scratching my brain on this one but so far no result.
    we have table with daily product availability and I would like to know which products have the highest rotation.

    Example:

    date product stock
    1/1/2005 x 5
    2/1/2005 x 4 (sold 1)
    3/1/2005 x 1 (sold 3)
    4/1/2005 x 10 (purchased 9)
    1/1/2005 y 0
    2/1/2005 y 5 (purchased 5)
    3/1/2005 y 1 (sold 4)
    4/1/2005 y 3 (purchased 2)
    ...


    Is it possible to obtain for each product the sum of the 'sold' items, in this case it would be product x: 4 and product y: 4

    Any deas would be really most welcome

    Thanks & merry christmas to all

    Michael

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If the dates in your table increment by 1 all the time, you could try this:
    Code:
    SELECT p1.product, SUM(p1.stock - p2.stock) sold
    FROM product p1, product p2
    WHERE p1.product = p2.product
      AND p2.stock < p1.stock
      AND p2.dt = p1.dt + 1
    GROUP BY p1.product;
    But, if you have gaps between dates, you'll need a subquery to find out the right date:
    Code:
    ... AND p2.dt =
    (SELECT MIN(p3.dt) FROM product p3
     WHERE p3.product = p1.product
       AND p3.dt > p1.dt
    )
    I hope this helps ...

Posting Permissions

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