Calculating movement

Backoffice · #1 (**permalink**) 12-25-04, 06:49

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

Littlefoot · #2 (**permalink**) 12-25-04, 18:08

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 ...

Internet Services

Web Development

Digital Marketing

Consumer Tech