MySQL Forums
Forum List  »  Newbie

How to get the date of each price change in the Northwind database?
Posted by: Montana Burr
Date: December 22, 2021 02:59PM

The Northwind database product cost history looks like this:

ProductID StartDate EndDate StandardCost
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

I want to get the start dates of each price change for each product. For example, the query should return "2012-05-30" and "2013-05-30" for ProductID=707.

I have this query:

SELECT t1.ProductID,t2.StartDate,ABS(t2.StandardCost - t1.StandardCost) AS costDifference
FROM productcosthistory t1
JOIN productcosthistory t2
ON t1.ProductID = t2.ProductID
WHERE ABS(t2.StandardCost - t1.StandardCost) > 0
ORDER BY ProductID,t2.StartDate

but for each product it returns duplicate start dates for each product. For example, it lists "2011-05-31" twice for product ID 707.

Options: ReplyQuote

Written By
How to get the date of each price change in the Northwind database?
December 22, 2021 02:59PM

This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.