Problem solve Get help with specific problems with your technologies, process and projects.

When did a product price increase a given percentage

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
  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 last published in February 2005

Dig Deeper on Oracle DBA jobs, training and certification



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.