MYSQL Select records with closest date

Question

I am having the following 2 tables in my database:

product_price

CREATE TABLE `product_price` (
  `asin` varchar(10) NOT NULL,
  `date` date NOT NULL,
  `price` decimal(7,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`asin`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

product_info

CREATE TABLE `product_info` (
  `asin` varchar(10) NOT NULL,
  `name` varchar(200) DEFAULT NULL,
  `brand` varchar(50) DEFAULT NULL,
  `part_number` varchar(50) DEFAULT NULL,
  `url` text,
  `image` text,
  PRIMARY KEY (`asin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

I want to find the products which its price has been reduced between a start date and a end date.

I am using the following query right now which works:

$query      = "SELECT pi.*, prev.price AS 'old_price', curr.price, ROUND((100.0*(curr.price - prev.price) / prev.price),0) As PercentDiff FROM product_price As curr 
       JOIN product_price As prev ON curr.date = '".$end_date."' AND prev.date = '".$start_date."' 
       JOIN product_info pi ON curr.asin = pi.asin WHERE curr.asin = prev.asin HAVING PercentDiff < 0 ORDER BY PercentDiff";

But the problem is that table product_price has not has a record if the price has not changed.

Eg for example

asin        date         price
AAAAAAAAA  2018-07-17     7
AAAAAAAAA  2018-07-15     6

start_date= "2018-07-16" end_date="2018-07-17"

I want it to have it in my results as price was reduced.

How i had to change my query??

I tried to change prev.date = '".$start_date."' to prev.date <= '".$start_date."'

But returns all the records and not the closest one as i want.

Here is some sample data from product_price:

+------------+------------+--------+
| asin       | date       | price  |
+------------+------------+--------+
| B000GBKDB4 | 2018-07-02 |  38.66 |
| B000GBKDCI | 2018-07-02 |  72.98 |
| B000GBKFLW | 2018-07-02 |  33.27 |
| B000GBKFMG | 2018-07-02 |  63.45 |
| B000GBLZEI | 2018-07-02 |  34.90 |
| B000GBLZEI | 2018-07-04 |  21.31 |
| B000GBLZEI | 2018-07-05 |  20.24 |
| B000GBLZEI | 2018-07-06 |  18.41 |
| B000GBLZEI | 2018-07-08 |  17.49 |
| B000GBLZEI | 2018-07-09 |  15.79 |
| B000GBLZEI | 2018-07-11 |  14.84 |
| B000GBLZEI | 2018-07-16 |  14.29 |

Answer 1

Still not very clear what you are after but this should select the correct prices applicable for a pair of dates. 2018-07-03 to 2018-07-07 that don't appear in the product_price table.

Hopefully this will set you on the right track.

SELECT *,

    (SELECT price 
        FROM product_price 
        WHERE product_price.asin = product_info.asin AND 
            product_price.date <= '2018-07-03'
        ORDER BY product_price.date DESC
        LIMIT 1) AS start_price,

    (SELECT price 
        FROM product_price 
        WHERE product_price.asin = product_info.asin AND 
            product_price.date <= '2018-07-07'
        ORDER BY product_price.date DESC
        LIMIT 1) AS end_price

FROM product_info
WHERE asin = 'B000GBLZEI'

Following on from your comment, you could use a temporary table like this

CREATE TEMPORARY TABLE IF NOT EXISTS price_changes (
    asin VARCHAR(10), 
    start_price DECIMAL(7,2), 
    end_price DECIMAL(7,2));

TRUNCATE price_changes;

INSERT INTO price_changes 
SELECT asin, 

    (SELECT price 
        FROM product_price 
        WHERE product_price.asin = product_info.asin AND 
            product_price.date <= '2018-07-03'
        ORDER BY product_price.date DESC
        LIMIT 1) AS start_price,

    (SELECT price 
        FROM product_price 
        WHERE product_price.asin = product_info.asin AND 
            product_price.date <= '2018-07-07'
        ORDER BY product_price.date DESC
        LIMIT 1) AS end_price

FROM product_info;

SELECT *,
    ROUND((100.0 * (end_price - start_price) / start_price), 0) AS price_difference
FROM product_info 
    JOIN price_changes ON
        price_changes.asin = product_info.asin;