Hi, I have a products table that contains pricing by date for each product. My fields are PriceID (key), ProductName,...
PriceDate, and Price. What I want is to query for the date that any price went over a given percentage. So if I did a query on 01/01/2000 and 5% within 30 days, I want the results to show each product and the date that product's price increased at least 5%.
My ending fields should be ProductName, StartingDate, StartingPrice, IncreaseDate, IncreasePrice. Even though my table could have many entries for one product within the given timeframe, I only want to see the first date the increase occurred to keep the recordset small.
One problem will be to determine the StartingDate and StartingPrice for each product. To see why this is tricky, consider the following rows in your table:
PriceID ProductName PriceDate Price 536 widget 1999-12-15 9.37 537 doodad 2000-01-01 5.00 538 widget 2000-01-15 10.00 539 doodad 2000-02-01 6.00
If you specify a starting date of 01/01/2000 and a range of 30 days, then you can see that the DOODAD started on that day (#537) with a price of 5.00 and did not increase this price until 31 days later, so it does not qualify for the report. Unless, that is, you consider the change (#537) to be a change within the time period.
The WIDGET, on the other hand, definitely had a price increase (#538) within that time period. The increase was (10.00-9.37)/9.37 = 6.7%, so it does qualify for the report. But how did we know that its starting price was 9.37? We have to take the latest price prior to the time period.
So to get the StartingDate, we need a subquery, which will find the highest PriceDate before the time period. Then, since you only want one increase, we need the lowest PriceDate of any price change that occurs within the time period, so that's another subquery.
select ProductName , StartingDate , StartingPrice , IncreaseDate , IncreasePrice from ( select PriceID , PriceDate as StartingDate , Price as StartingPrice from products as P1 where PriceDate = ( select max(PriceDate) from products where PriceID = P1.PriceID and PriceDate < '2000-01-01' ) ) as S inner join ( select PriceID , PriceDate as IncreaseDate , Price as IncreasePrice from products as P2 where PriceDate = ( select min(PriceDate) from products where PriceID = P2.PriceID and PriceDate >= '2000-01-01' and PriceDate < '2000-01-01' plus 30 days ) ) as E on S.PriceID = E.PriceID where 100 * ( IncreasePrice - StartingPrice ) / StartingPrice >= 5
An alternative to this rather unwieldy (and probably slow) query would be to do the calculation in an application program. Simply return all rows prior to '2000-01-01' plus 30 days (note: databases vary in the actual SQL for date arithmetic) and use an ORDER BY. Then, program logic can detect the increases and produce the report with only one pass of the data. This assumes that there are not too many changes in the table to make returning all rows prior to '2000-01-01' plus 30 days prohibitive.
Dig Deeper on Oracle DBA jobs, training and certification
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.