CODEWITHSUNDEEP
mysqlsqlselectgroup-bydifference
YasirPoongadan
YasirPoongadan

Reputation: 739

find price difference for different date

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

Upvotes: 2

Views: 1020

Answers (3)

Saharsh Shah
Saharsh Shah

Reputation: 29051

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;

Upvotes: 1

fthiella
fthiella

Reputation: 49049

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.

Upvotes: 0

Lajos Veres
Lajos Veres

Reputation: 13725

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)

Upvotes: 3

Related Questions