SQL subtract two rows based on date and another column

Question

I need to subtract two rows in MySQL by using the most recent date from the previous date:

Starting table:

Stock       Date          Price
GOOG        2012-05-20    402
GOOG        2012-05-21    432
APPL        2012-05-20    553
APPL        2012-05-21    590
FB          2012-05-20     40
FB          2012-05-21     34

This is the result table:

Stock       Date          Price Change
GOOG        2012-05-21    30
APPL        2012-05-21    37
FB          2012-05-21    -6

Right now I just have two dates per company, but bonus upvotes if your query can handle any number of dates.

@JohnWoo Why assume SQL Server? yyyy-mm-dd looks like MySQL to me. — Michael Berkowski, Sep 07 '12 at 00:31
@MichaelBerkowski yep you are correct. didn't notice the dates. sleepy already. hehe — John Woo, Sep 07 '12 at 01:06
although analytics functions will be added in mysql 8.0, this might be helpful. https://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql — รยקคгรђשค, Mar 23 '18 at 09:35

John Woo · Accepted Answer · 2012-09-07T09:23:13.253

What I've done was I get two separate queries to get each stock's maximum date and minimum date. Try this,

SELECT  maxList.stock, 
        maxList.Date,
        (maxlist.Price - minlist.Price) totalPrice
FROM
    (
        SELECT  a.*
        FROM    tableName a INNER JOIN
        (
            SELECT      Stock, MAX(date) maxDate
            FROM        tableName
            GROUP BY    Stock
        ) b ON  a.stock = b.stock AND
                a.date = b.maxDate
    ) maxList INNER JOIN
    (
        SELECT  a.*
        FROM    tableName a INNER JOIN
        (
            SELECT      Stock, MIN(date) minDate
            FROM        tableName
            GROUP BY    Stock
        ) b ON  a.stock = b.stock AND
                a.date = b.minDate
    ) minList ON maxList.stock = minList.stock

SQLFiddle Demo

UPDATE 1

seeing your last sentence: Right now I just have two dates per company, but bonus upvotes if your query can handle any number of dates. What if you have records like this?

FB          2012-05-20     40
FB          2012-05-21     34
FB          2012-05-22     42

what would be its result?

enter image description here

Mike Parkhill · Answer 2 · 2012-09-07T15:16:26.760

I know this was answered already, but the problem caught my interest so I thought I'd give it a try.

I assumed you might want to see the difference between any two consecutive dates.

So for the following data:

GOOG        2012-09-07  42.34
GOOG        2012-09-06  44.56
GOOG        2012-09-01  44.32
FB          2012-09-07  17.82
FB          2012-08-05  12.98

You would get:

GOOG        2012-09-07  2012-09-06  -2.22
GOOG        2012-09-06  2012-09-01  0.24
FB          2012-09-07  2012-08-05  4.84

The query I wrote for this is:

SELECT t1.Stock, t1.Date AS CurrentDate, oldDate.Date AS OldDate, (t1.Price - oldDate.Price) AS PriceChange
FROM MP_StockTable t1
JOIN (SELECT t2.Stock, t2.Date AS date1, (SELECT MAX(t3.Date) FROM MP_StockTable t3 WHERE t3.Date < t2.Date AND t3.Stock = t2.Stock GROUP BY t3.Stock) AS date2 
        FROM MP_StockTable t2) AS StockDates ON StockDates.Stock = t1.Stock AND StockDates.date1 = t1.Date
JOIN MP_StockTable oldDate ON oldDate.Stock = t1.Stock AND oldDate.Date = StockDates.date2

The inline table basically calculates the closest previous date for each row. So for the data above, the generated table would like this:

GOOG        2012-09-07  2012-09-06
GOOG        2012-09-06  2012-09-01
GOOG        2012-09-01  NULL
FB          2012-09-07  2012-08-05
FB          2012-08-05  NULL

We then use the two dates from that table to get our two price points.

NOTE: I was testing in SQLServer, so apologies if this needs some tweaks for mySql (it's been years since used mySql, so I've forgotten the differences).

SQL subtract two rows based on date and another column

2 Answers2

SQLFiddle Demo

Linked