Show date of next price increase

Question

I need to express the date of the next price increase in a correlated subquery. so in the example below I am showing a date relecting a date in the past as a current date if we were running a rpt.

so if the date of 10-2-2010 is selected, when is the date of the next price increase - it should reveal 2011-01-01 based on information in the temp table example.

 create table #temp(
 item char (7),
 price money, 
 eff_dt datetime,
 end_dt datetime )
 
 insert into #temp values('9900123',37.37,'2011-01-22','2099-12-31')
 insert into #temp values('9900123',34.66,'2011-01-01','2011-01-21')
 insert into #temp values('9900123',30.89,'2010-12-21','2010-12-31')
 insert into #temp values('9900123',49.00,'2010-12-01','2010-12-20')
 insert into #temp values('9900123',61.30,'2010-10-01','2010-11-30')
 insert into #temp values('9900123',36.68,'2010-09-01','2010-09-30')
 insert into #temp values('9900123',30.89,'2010-08-22','2010-08-31')
 insert into #temp values('9900123',28.06,'2010-08-01','2010-08-21')
 insert into #temp values('9900123',36.68,'2010-07-20','2010-07-31')
 insert into #temp values('9900123',28.06,'2010-07-01','2010-07-19')
 
 
 select 
 curr.item
 ,curr.price
 ,curr.eff_dt
 ,curr.end_dt
 ,next.eff_dt as next_increase_date
 
  from #temp as curr
 join #temp as next
 on curr.item = next.item 
 and next.eff_dt = (SELECT min(EFF_DT) FROM (
                 SELECT new_prc.ITEM,
                        new_prc.EFF_DT
                   FROM #temp AS new_prc 
                     LEFT OUTER JOIN
                     #temp AS old_prc
                     ON old_prc.ITEM = new_prc.ITEM
                     AND old_prc.END_DT = DATEADD(d, -1, new_prc.eff_dt)
                  WHERE new_prc.PRice > old_prc.PRice
                     AND curr.ITEM = old_prc.ITEM  ) priceRises)
 
 where '2010-10-02' between curr.eff_dt and curr.end_dt

Answer 1

If you select the data you need based on the passed datetime parameter so only the records which are after this date are considered then you can include the row number into the mix such that the records are sub-numbered on the per item basis. This way, you can join your result with itself based on the off by 1 row number basis so the information about current and next row is combined into one record. Then you can simply compare the 2 and pick only the record which has the price increased when compared to the price of the "previous" record. If you need just first record selected then the query below should do it. Otherwise, you can comment out the top 1 part to get all records which experience the price increase when compared with previous period.

 declare @date datetime;
 set @date = '20101002'
 
 ;with records(RecordID, item, price, eff_dt, end_dt) as
 (
     select 
         row_number() over (partition by item order by eff_dt) RecordID,
         item, price, eff_dt, end_dt
         from #temp
         where end_dt >= @date
 )
     select top 1 
         a.item, a.price, a.eff_dt, a.end_dt, b.eff_dt next_increase_date
         from records a inner join records b
             on a.item = b.item and a.RecordID = b.RecordID - 1
         where a.price < b.price
         order by a.RecordID;

Oleg

Answer 2

0

How about this:

 CREATE PROC getPriceData (@someDate datetime)
 AS
 SELECT
 curr.item
 ,curr.price
 ,curr.eff_dt
 ,curr.end_dt
 ,(
     SELECT TOP 1 
       eff_dt 
     FROM 
       #temp next 
     WHERE 
       next.item=curr.item 
       AND 
       next.end_dt > curr.end_dt
     ORDER BY
       end_dt ASC
 ) as next_increase_date
 FROM #temp curr
 WHERE curr.end_dt <= @someDate

answered Mar 30, 2011 at 11:27 AM

Magnus Ahlkvist
22k ● 20 ● 41 ● 42

this is not the report...this is nested inside a query so only one element of the sproc - you're query also does not compare any prices...how would it ever know if it was a price increase?

Mar 30, 2011 at 11:33 AM siera_gld

Not sure I follow the requirements then. If you want each row, and the next price-change which represents an increase? If that's the case you could just add AND next.price > curr.price to the WHERE clause of the subquery.

Mar 30, 2011 at 12:11 PM Magnus Ahlkvist

(comments are locked)

	Follow @Ask_SSC
	Follow Ask SSC on Facebook
	Find Ask SSC on Google+
	Find us on LinkedIn

Show date of next price increase

Follow this question

Related Questions