0

I am trying to track price changes on a daily basis. I have 2 tables Stores and Prices.

Table Stores columns:

  • s_id
  • company
  • address
  • city
  • state
  • zip
  • phone

Table Prices columns:

  • ref
  • storeid
  • price
  • mgr
  • datetime

NOTE: storeid in Prices is a reference to s_id in Stores

What I would like to do is run a query to find all prices from yesterday and compare them to today to see if they have changed. Please understand that I am filing in for the database admin for any advice you can offer.

2

Would be something like:

select  p1.ref, p1.storeid, p1.price, p2.price
from    prices p1 
join prices p2 on p1.ref = p2.ref
where   trunc(p1.datetime) = trunc(sysdate)
and     trunc(p2.datetime) = trunc(sysdate-1)
and     p1.price <> p2.price;

You can add Stores to get store information if you need. I do not know which DB you use so I gave a solution for Oracle. The trunc function gives you the date part of a date.

| improve this answer | |
1

With MySQL, you can use this query:

SELECT Pnew.ref
    , Pnew.storeid
    , Pnew.mgr
    , Pnew.price as `NewPrice
    , Pold.price as `OldPrice
FROM prices Pnew
INNER JOIN prices Pold
    ON Pnew.ref = Pold.ref
        AND DATE(Pnew.datetime) = CURDATE()
        AND DATE(Pold.datetime) = SUBDATE(CURDATE(), 1)
WHERE Pnew.price <> Pold.price;

It supposes that column ref is the key.

This query returns ref with today's date and an extra column with CHANGED or same:

SELECT Pnew.ref
    , Pnew.storeid
    , Pnew.mgr
    , Pnew.price as `NewPrice`
    , Pold.price as `OldPrice`
    , IF(Pnew.price <> Pold.price, 'CHANGED', 'same')
FROM prices Pnew
INNER JOIN prices Pold
    ON Pnew.ref = Pold.ref
    AND DATE(Pold.datetime) = SUBDATE(Pnew.datetime, 1)
WHERE DATE(Pnew.datetime) = CURDATE()
;

This SQL Fiddle works with a small sample of 6 rows. It contains both queries.

Output:

ref | storeid | mgr | price | price | 
  1 |       1 |   1 |     1 |     2 | CHANGED 
  2 |       1 |   1 |     3 |     3 | same 
  3 |       1 |   1 |     6 |     4 | CHANGED 
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.