Get fluctuating price on MySQL

Given this data:

CREATE TABLE fluctuate
    (Date datetime, Company varchar(10), Price int);

INSERT INTO fluctuate
    (Date, Company, Price)
VALUES
    ('2012-01-04 00:00:00', 'Apple', 458),
    ('2012-01-03 00:00:00', 'Apple', 462),
    ('2012-01-02 00:00:00', 'Apple', 451),
    ('2012-01-01 00:00:00', 'Apple', 450),
    ('2012-01-01 00:00:00', 'Microsoft', 1),
    ('2012-01-03 00:00:00', 'Microsoft', 7),
    ('2012-01-05 00:00:00', 'Microsoft', 5),
    ('2012-01-07 00:00:00', 'Microsoft', 8),
    ('2012-01-08 00:00:00', 'Microsoft', 12);

You want to get the fluctuation in price even the date is non-contiguous:

DATE                       COMPANY             PRICE               DAY_CHANGE
January, 04 2012           Apple               458                 -4
January, 03 2012           Apple               462                 11
January, 02 2012           Apple               451                 1
January, 01 2012           Apple               450                 0
January, 08 2012           Microsoft           12                  4
January, 07 2012           Microsoft           8                   3
January, 05 2012           Microsoft           5                   -2
January, 03 2012           Microsoft           7                   6
January, 01 2012           Microsoft           1                   0

Since there's no windowing function on MySQL yet, we will settle for some MySQL-ism:

select 

date, 
company, 
price, 
day_change

from
(    
  select 

     case when company <> @original_company then
         -- new company detected,
         -- reset the original_price base on the new company
         @original_price := price
     end,
    f.*,
    price - @original_price as day_change,
    (@original_price := price),
    (@original_company := company)


  from fluctuate f

  cross join
  (
    select 
     @original_price := price,
     @original_company := company
     from fluctuate 
     order by company, date limit 1
  )
  as zzz

  order by company, date 

) as yyy
order by company, date desc

How does it work, first we need to get the starting price from the table, we use cross join to get the initial values. Then from there, we subtract the price from the original price and alias it as day_CHANGE, then save the state of the current price to original price, then apply the new original price to next price, and so on.

Now on new company, to reset the state of original price based on that company, on the first expression of our SELECT statement, we detect if the current company is not equal to the original_company; if it isn't, we reset the original_price based on the current company


Happy Coding! :-)

Live test: http://www.sqlfiddle.com/#!2/e16ec/1