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, '2024-05-31', '2024-05-29', 12.0278),
(707, '2024-05-30', '2024-05-29', 13.8782),
(707, '2024-05-30', NULL, 13.0863),
(708, '2025-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.

For other examples of tracking value changes, see "Track state changes" under "Aggregates".

Last updated 31 Mar 2024