How to create a query in MySQL to subtract consecutive rows based on the date and a distinctive field?

Question

Based on SQL subtract two rows based on date and another column I had a good idea but I need something else.

I have the following table (inventory):

animal   date         quantity

dog      2015-01-01   400
cat      2015-01-01   300
dog      2015-01-02   402
rabbit   2015-01-01   500
cat      2015-01-02   304
rabbit   2015-01-02   508
rabbit   2015-01-03   524
rabbit   2015-01-04   556
rabbit   2015-01-05   620
rabbit   2015-01-06   748

By running this query:

SELECT a.animal, a.Date
AS actual_date, past_date.Date
AS past_date, (a.quantity - past_date.quantity)
AS quantity_diff
FROM inventory a
JOIN
(SELECT b.animal, b.date AS date1,
(SELECT MAX(c.date)
FROM inventory c
WHERE c.date < b.date AND c.animal = b.animal
GROUP BY c.animal)
AS date2 
FROM inventory b)
AS original_date ON original_date.animal = a.animal
AND original_date.date1 = a.date
JOIN
inventory past_date
ON past_date.animal = a.animal
AND past_date.date = original_date.date2

I get this:

animal   actual_date   past_date   quantity_diff

dog      2015-01-02    2015-01-01  2
cat      2015-01-02    2015-01-01  4
rabbit   2015-01-02    2015-01-01  8
rabbit   2015-01-03    2015-01-02  16
rabbit   2015-01-04    2015-01-03  32
rabbit   2015-01-05    2015-01-04  64
rabbit   2015-01-06    2015-01-05  128

What I want to get is this (see quantity_diff column):

animal   quantity_diff

cat      4
cat      NULL
dog      2
dog      NULL
rabbit   8
rabbit   16
rabbit   32
rabbit   64
rabbit   128
rabbit   NULL

http://sqlfiddle.com/#!9/c77d8/11

Answer 1

The Question has changed a little bit, here is my current understanding of what you want.

CREATE TABLE inventory
    (`animal` varchar(6), `date` date, `quantity` int);
INSERT INTO inventory
    (`animal`, `date`, `quantity`)
VALUES
    ('dog', '2015-01-01', 400),
    ('cat', '2015-01-01', 300),
    ('dog', '2015-01-02', 402),
    ('rabbit', '2015-01-01', 500),
    ('cat', '2015-01-02', 304),
    ('rabbit', '2015-01-02', 508),
    ('rabbit', '2015-01-03', 524),
    ('rabbit', '2015-01-04', 556),
    ('rabbit', '2015-01-05', 620),
    ('rabbit', '2015-01-06', 748);

The Query

select animal,actual_date,past_date,quantity_diff
from
(   SELECT a.animal, a.Date
    AS actual_date, past_date.Date
    AS past_date, (a.quantity - past_date.quantity)
    AS quantity_diff,
    1 as drewOrder
    FROM inventory a
    JOIN
    (SELECT b.animal, b.date AS date1,
    (SELECT MAX(c.date)
    FROM inventory c
    WHERE c.date < b.date AND c.animal = b.animal
    GROUP BY c.animal)
    AS date2 
    FROM inventory b)
    AS original_date ON original_date.animal = a.animal
    AND original_date.date1 = a.date
    JOIN
    inventory past_date
    ON past_date.animal = a.animal
    AND past_date.date = original_date.date2
    union
    select distinct animal,null,null,null,2 as drewOrder from inventory
) x
order by x.animal,x.drewOrder,x.actual_date;

The Results:

+--------+-------------+------------+---------------+
| animal | actual_date | past_date  | quantity_diff |
+--------+-------------+------------+---------------+
| cat    | 2015-01-02  | 2015-01-01 |             4 |
| cat    | NULL        | NULL       |          NULL |
| dog    | 2015-01-02  | 2015-01-01 |             2 |
| dog    | NULL        | NULL       |          NULL |
| rabbit | 2015-01-02  | 2015-01-01 |             8 |
| rabbit | 2015-01-03  | 2015-01-02 |            16 |
| rabbit | 2015-01-04  | 2015-01-03 |            32 |
| rabbit | 2015-01-05  | 2015-01-04 |            64 |
| rabbit | 2015-01-06  | 2015-01-05 |           128 |
| rabbit | NULL        | NULL       |          NULL |
+--------+-------------+------------+---------------+

Answer 2

Try this query:

SELECT L.animal,L.date,
(SELECT date FROM inventory WHERE animal=L.animal AND date<L.date ORDER BY date DESC LIMIT 1) AS 'past_date',
(L.quantity-(SELECT quantity FROM inventory WHERE animal=L.animal AND date<L.date ORDER BY date DESC LIMIT 1)) AS 'quantity_diff'
FROM inventory AS L
GROUP BY (CONCAT(L.animal,'-',L.date));

This query is going to search for each animal+date what is the most recent date before the current date. For example, for the first record:

dog      2015-01-01   400

The most recent date before '2015-01-01' is null, so there isn't anything to substract from the current quantity (400). For the second record:

dog      2015-01-02   402

The most recent date for current animal(dog), which it's before the current date('2015-01-02') is '2015-01-01', which has a quantity of 402, so the difference is 402-400=2. The process is the same for each animal.