# Thread: Calculating movement

1. Registered User
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. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
Provided Answers: 5
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
•