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.
Requires Free Membership to View
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.
This was first published in February 2005
Join the conversationComment
Share
Comments
Results
Contribute to the conversation