question

siera_gld avatar image
siera_gld asked

Show date of next price increase

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
sub-querycorrelated
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@siera_gld Based on the query in your question, increase in price means increase from the previous period right? What I mean is that the Oct 2d 2010 has the highest price of all records (61.30), so the price never increased above that level, but because the price has changed from 30.89 in '2010-12-21' to '2010-12-31' period to 34.66 in the period starting from '2011-01-01', you need to reveal this record. So passing a parameter of '2010-10-02' will display one record and passing a parameter '2010-07-20' will also display one record with '2010-08-22' as the date because this is when the price which kept going down for 1 period jumped to 30.89 from the 28.06 in the period between '2010-08-01' and '2010-08-21'. Is my understanding correct?
0 Likes 0 ·
siera_gld avatar image siera_gld commented ·
Oleg - You're understanding I think is correct - the price does not have to surpass the highest point - it just has to be an increased price over the previous time period
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

siera_gld avatar image siera_gld commented ·
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?
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
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.
0 Likes 0 ·
Oleg avatar image
Oleg answered
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; <\!-- Begin Edit It is possible that the query above needs to be tweaked a bit depending on actual requirements. For example, if the @date parameter is passed with the value 20101002 then the result is this: item price eff_dt end_dt next_increase_date ------- ----- ---------- ---------- ------------------ 9900123 30.89 2010-12-21 2010-12-31 2011-01-01 This might not be desired because though the next increase date is calculated correctly, the values in other columns are shown for the the period when the price has increased when instead they should show the original, first record which fell into the category of happening after the passed @date parameter value, so it would probably be better if the results were like this instead: item price eff_dt end_dt next_increase_date ------- ----- ---------- ---------- ------------------ 9900123 61.30 2010-10-01 2010-11-30 2011-01-01 If this is the case then the tweak is very primitive, just include the first record into the bunch anyway and then use the min of eff date as the current, min of end date as current, max of eff date from the A record as the next increase date and display the price from the first A record. Here is the script to adjust the results: 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 a.item, max(case when a.RecordID = 1 then a.price else 0 end) price, min(a.eff_dt) eff_dt,min(a.end_dt) end_dt, max(a.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.RecordID = 1 or a.price < b.price group by a.item; End Edit--> Oleg
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.