Track price changes

from the Artful Common Queries page


The MySQL LAG() function, since 8.0, makes short work of this task, for example in a table like ....
DROP TABLE IF EXISTS products;
CREATE TABLE products(
  productid int,
  startdate date,
  enddate date, 
  standardcost decimal(10,4)
);
INSERT INTO products VALUES
(707, '2011-05-31', '2012-05-29', 12.0278),
(707, '2012-05-30', '2013-05-29', 13.8782),
(707, '2013-05-30', NULL, 13.0863),
(708, '2011-1-1', NULL, 14.00 );
Just invoke LAG(standardcost) OVER(PARTITION BY productid ORDER BY startdate), and impose an outer query condition specifying just the rows that exhibit changed values ...
SELECT * 
FROM (
  SELECT
    productid, startdate,enddate,standardcost,
    LAG(standardcost, 1) OVER (
        PARTITION BY productid
        ORDER BY startdate
    ) as prev_cost
  FROM products
) x
WHERE standardcost <> prev_cost;
(The outer query is needed because a Lag() column will not accept a Where condition.)

Last updated 22 Dec 2021




Return to the Artful Common Queries page