SQL query to show changes to "total" column

Question

I have a table which contains stock transactions:

+------+----------------------+------------------+
| Item | Running Stock Total  | Transaction Time |
+------+----------------------+------------------+
| foo  | 4                    | 2012-05-12 11:07 |
| bar  | 3                    | 2012-05-12 10:42 |
| bar  | 3                    | 2012-05-12 9:42  |
| bar  | 2                    | 2012-05-11 15:42 |
| foo  | 3                    | 2012-05-11 10:02 |
| bar  | 3                    | 2012-05-10 13:44 |
...etc...
+------+----------------------+------------------+

i.e. Any time something happens to stock, a row is created in this table- this may mean the stock level goes up (new stock ordered), down(stock sold) or remains unchanged (stock relocated).

I need to create an sql query that returns only the rows where the stock level has actually changed for a particular part, and it needs to show the changes in a "stock up" and "stock down" column.

i.e. 1 Item='bar'

+------+-----------+------------+----------------------+------------------+
| Item | Stock Up  | Stock Down | Running Stock Total  | Transaction Time |
+------+-----------+------------+----------------------+------------------+
| bar  |      1    |     0      |  3                   | 2012-05-12 9:42  |
| bar  |      0    |     1      |  2                   | 2012-05-11 15:42 |
| bar  |      1    |     0      |  3                   | 2012-05-10 13:44 |
+------+-----------+------------+----------------------+------------------+

e.g.2 Item='foo'

+------+-----------+------------+----------------------+------------------+
| Item | Stock Up  | Stock Down | Running Stock Total  | Transaction Time |
+------+-----------+------------+----------------------+------------------+
| foo  |      1    |     0      |  4                   | 2012-05-12 11:07 |
| foo  |      2    |     0      |  3                   | 2012-05-11 10:02 |
+------+-----------+------------+----------------------+------------------+

So something like...

SELECT 
  Item, {xyz} as 'Stock Up', {abc} as 'Stock Down',  `Running Stock Total`, `Transaction Time`
FROM
  `StockTransactions`
WHERE
  `Item`='foo'
HAVING 
  ('Stock Up'>0 or 'Stock Down'>0)

Can this be done?

Answer 1

My solution works based on the assumption, that Transaction Time is unique per Item.

I am simulating row_number() analytical function via the helper view that I create:

CREATE VIEW running_stock AS
SELECT s.item,s.running_total,s.transaction_dt,
  (SELECT count(*) FROM stock WHERE item=s.item
      AND transaction_dt <= s.transaction_dt) AS row_number
  FROM stock s
 ORDER BY 1, 4;

After view is in place, you can obtain the desired results with the following query:

SELECT c.item AS "Item",
  greatest(c.running_total - p.running_total, 0) AS "Stock Up",
  greatest(p.running_total - c.running_total, 0) AS "Stock Down",
  c.running_total AS "Running Total",
  c.transaction_dt AS "Transaction Time"
FROM running_stock c
LEFT JOIN running_stock p ON c.item = p.item
      AND p.row_number + 1 = c.row_number
WHERE c.row_number > 1
ORDER BY 1, 5;

You can play with this query on the SQL Fiddle.

Answer 2

SELECT   `Item`,
         `Stock Up`,
         `Stock Down`,
         `Running Stock Total`,
         `Transaction Time`

FROM (
  SELECT   `Item`,
           GREATEST(`Running Stock Total` - @`last_total`, 0) AS `Stock Up`,
           GREATEST(@`last_total` - `Running Stock Total`, 0) AS `Stock Down`,
           `Running Stock Total`,
           `Transaction Time`,
           @`last_total` := `Running Stock Total`
  FROM     `StockTransactions` JOIN (SELECT @`last_total` := 0) AS lt
  WHERE    `Item` = 'bar'
  ORDER BY `Transaction Time` ASC
) AS t

ORDER BY `Transaction Time` DESC

See it on sqlfiddle. The outer query can obviously be omitted if you're happy for the results to be ordered in ascending order of transaction time and with the extra last_total column.