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.
(comments are locked)
|
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.
Oleg
(comments are locked)
|
How about this:
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)
|
@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?
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