Tricky SQL question... this one is a real puzzler.?

Question

Suppose I have a simple SQL table 'products' with four columns: (productId int unsigned, effectiveDate date, price decimal(10,2), quantityAvailable int unsigned) - this is an oversimplification of the real problem, just to illustrate my point.

let's say select * from products where productId=123 returns this:

123 | 2008-08-01 | 10 | 1000

123 | 2008-08-02 | 10 | 500

123 | 2008-08-03 | 20 | 250

123 | 2008-08-04 | 20 | 100

123 | 2008-08-05 | 10 | 2000

123 | 2008-08-06 | 10 | 1000

123 | 2008-08-07 | 10 | 500

It shows how the price and quantity available fluctuated over time.

My question is... What is the simplest query that could tell me... for any product (of course, for the single example above the answer is 2008-08-05) "when was the last date that the price changed to become equal to today's price"?

I started out with the following (note, the "?" character gets replaced by the desired productId, in this case 123, at query execution time):

select min(effectiveDate) from products where price = (select price from products where productId = ? and effectiveDate = (select max(effectiveDate) from products where productId = ?)) and productId = ?;

But that of course returns 2008-08-01 which is the wrong answer.

Is there a nice, short query that can return the correct answer?

Thanks

Answer 1

Well, when you update your table just change the effective date.

ID | LastDateChanged | Cost| Quantity

On Monday September 1, 2008, the table looks like this:

123 | 2008-01-09 | 12.37 | 1000

A week later, you update the price to 13.33. In your update statement, you change the price and the LastDateChanged column. It now looks like this:

123 | 2008-07-09 | 13.33 | 900

So when you query, you just do this:

SELECT LastDateChanged from Products Where ProductID=123

Then you get 2008-07-09.

If you want to keep a running tab of price changes, then you would need another table.

ADD:

I hope I understood your question correctly!

Answer 2

I dont know whic database you are using so I use plain SQL and not some ORACLE analytic function that could make this a lot shorter:

SELECT *

  FROM TEST a

WHERE

product = 123

AND dt = (SELECT MIN (dt)

               FROM TEST b

              WHERE price = a.price AND dt > (SELECT MAX (dt)

                                                FROM TEST c

                                               WHERE price != b.price));

This will work pretty well even if you dates have gaps and all sort of other scenarios, let you find out by testing it ;)

Answer 3

This is shortsighted preparation, you are better to have a different table for price and date of change, linked by ID. you could also have a table of date and ID for the last date only of each item. .

Answer 4

Well you could try this

SELECT  TOP 1 *

FROM your_table

WHERE effectiveDate  >

(

SELECT  TOP 1 effectiveDate  

FROM your_table

WHERE price NOT IN (SELECT price

FROM your_table

WHERE effectiveDate  = rtrim(year(GETDATE())) + '-' + right('0' + rtrim(month(GETDATE())),2) + '-' + right('0' + rtrim(day(GETDATE())),2)

)

AND effectiveDate  <> rtrim(year(GETDATE())) + '-' + right('0' + rtrim(month(GETDATE())),2) + '-' + right('0' + rtrim(day(GETDATE())),2)

AND ID = 123

ORDER BY effectiveDate  DESC

)

AND price IN (SELECT price

FROM your_table

WHERE effectiveDate  = rtrim(year(GETDATE())) + '-' + right('0' + rtrim(month(GETDATE())),2) + '-' + right('0' + rtrim(day(GETDATE())),2)

AND ID = 123

)

AND ID = 123

ORDER BY effectiveDate

Answer 5

I think a query could be created to return what you are looking for, and I think you are pretty close to nailing it down.

However, I think it would be more efficient, and better design, to have a a table called 'Product' (that would have a single instance of each product ID, as the PK) and another one called something like 'DailyPrice'. Then, you could have a field in the Product table called ClosingPrice, or something lke that, that you could use to join to the DailyPrice table. Then it would be easy to get what you are askin for - you could just do a JOIN on that field.

Good luck.

Answer 6

No easy way about this...

Best I can figure, first step would be to select all records up the most recent price change. I would do so like this (not testing any of this, so it may need some tweaking):

SELECT MIN(effdate) FROM products WHERE effdate > (SELECT MAX(effData) FROM products WHERE productid = ? and price <> (SELECT price FROM products WHERE productid=? price=todaysprice))and productid = ?

That right there should return it correctly, but in a perfect scenario, the table would be a bit more normalized. Or, data would only be entered when a change happened, not on a daily basis. In any event, the above query might...just might...work...Like I said, no testing involved though...and I may have missed something in there...  

Answer 7

The domain of the set of candidate answers is any tuple that has a price equal to the current price.  The tuple that satisfies your query is the one that has the most recent effective date in the set of candidate answers.  Therefore,

given a todayprice of 100 for productid 123:

SELECT TOP 1 * FROM PRODUCT

WHERE PRODUCTID=123

AND PRICE=100

ORDER BY EFFECTIVEDATE DESC

You didn't indicate which rdbms you are using.  The above should work in MS Access or SQL Server.  The concept should work in Oracle, but it's been some years since I've done anything in PL/SQL.