find price difference for different date

Question

how find difference of price for two selected day. my table as shown

---------------------------------------
id      price      date        product
---------------------------------------
1       10         15-12-2013  pen
2       40         15-12-2013  book
3       15         16-12-2013  pen
4       42         16-12-2013  book
-------------------------------------

i want an sql query to get output like, if startdate:15-12-2013 & enddate: 16-12-2013

product  startdate(15-12-2013)  enddate(16-12-2013) difference
--------------------------------------------------------------
pen        10                      15                  5
book       40                      42                  2
--------------------------------------------------------------

what if startdate or enddate is not present for a particular product? you need to use the last known price? — fthiella, Dec 26 '13 at 11:14
Something in the lines of `IF(startdatePrice >= enddatePrice, startdatePrice - enddatePrice, enddatePrice - startdatePrice)` — silentw, Dec 26 '13 at 11:14

score 3 · Answer 1 · edited May 23 '17 at 12:08

Maybe something like this?

select
        p1.product,
        p1.price,
        p2.price,
        p1.price - p2.price as diff
from
        product p1,
        product p2
where
        p1.product=p2.product and
        date(p1.date)='2013-12-15' and
        date(p2.date)='2013-12-16'

If performance is a question then this link can provide a better alternative for the date matching part: MySQL SELECT WHERE datetime matches day (and not necessarily time)

Saharsh Shah · Answer 2 · 2013-12-30T04:52:12.463

1

Try this:

SELECT product, StartDatePrice, EndDatePrice, (EndDatePrice - StartDatePrice) AS Difference 
FROM (SELECT product, MAX(IF(a.date = '15-12-2013', a.price, 0)) AS StartDatePrice, 
             MAX(IF(a.date = '16-12-2013', a.price, 0)) AS EndDatePrice 
      FROM tableA a 
      GROUP BY product
     ) AS A;

edited Dec 30 '13 at 04:52

answered Dec 26 '13 at 11:16

Saharsh Shah

28,687
8
48
83

score 0 · Answer 3 · answered Dec 26 '13 at 11:33

If you insert a row on your prices table whenewer a price changes, and not every day, you should consider using this query:

SELECT
  p1.product,
  p1.price as stardtade,
  p2.price as enddate,
  p2.price-p1.price as difference
FROM
  prices p1 INNER JOIN (SELECT product, MAX(dt) max_dt
                        FROM prices
                        WHERE dt<='2013-12-15'
                        GROUP BY product) st
  ON p1.product=st.product AND p1.dt = st.max_dt

  INNER JOIN

  prices p2 

  ON p1.product=p2.product

  INNER JOIN (SELECT product, MAX(dt) max_dt
                        FROM prices
                        WHERE dt<='2013-12-16'
                        GROUP BY product) ed
  ON p2.product=ed.product AND p2.dt = ed.max_dt

it is more complicated, but it will work even if some dates are not present in your table. In that case it will use the lask known value for the price.

Please see fiddle here.

find price difference for different date

3 Answers3