SQL get change in values based on consecutive dates

I want to get the change in Price from day to day. What SQL query will accomplish this?

Original Table

Date       Company    Price
---------------------------
1/4/2012   Apple      458
1/3/2012   Apple      462
1/2/2012   Apple      451
1/1/2012   Apple      450

Desired Table

Date       Company    Price   Day_Change
-------------------------------------
1/4/2012   Apple      458     -4    
1/3/2012   Apple      462     9
1/2/2012   Apple      451     1
1/1/2012   Apple      450     NULL

Answers


Join the table to itself to get yesterday's price for the company, then subtract it from today's price

select
    t1.date,
    t1.company,
    t1.price,
    t1.price - t2.price as day_change
from price_table t1
left join price_table t2 
    on t2.date = subdate(t1.date, 1)
    and t2.company = t1.company

After this you can add a normal where clause, eg where t1.date > subdate(current_date(), 7) to get the last seven day's prices

FYI day_change will be NULL if there isn't a row for yesterday's price


Another approach, will work even in non-contiguous dates:

Source 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);

Output:

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

Query:

select 

date, 
company, 
price, 
day_change

from
(    
  select 

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


  from fluctuate f

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

  order by company, date 

) as yyy
order by company, date desc

Source: http://www.sqlfiddle.com/#!2/56de3/3


@Bohemian's answer is correct and will return the price difference since the day before, but practically I suspect you will actually want the price difference since the previous day's trading (which may span weekends, public holidays, etc.).

To accomplish this, one must first use a subquery to determine the last day on which each company traded (individual companies can be suspended, or might be trading in different markets subject to different holidays); then use that (date,company) pair to lookup the last price...

SELECT current.*, current.Price - previous.Price AS Day_Change
FROM (
    SELECT yourtable.*, MAX(before.Date) AS prevDate
    FROM
           yourtable
      JOIN yourtable AS before
            ON before.Date    < yourtable.Date
           AND before.Company = yourtable.Company
    GROUP BY yourtable.Date, yourtable.Company
  ) AS current
  JOIN yourtable AS previous
        ON previous.Date   = current.prevDate
       AND previous.Company= current.Company

Need Your Help

How can I combine multiple char's to make a string?

c++ string char

I am doing string parsing and essentially what I would like to do is something like this:

I/O optimization for Python

python performance io built-in chunks

I'm writing a program that takes in multiple lines of codes. At the moment, I am processing each one separately -> as each line comes in, I strip it, store it, etc., before asking for the next line...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.