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.
asked Mar 30, 2011 at 11:09 AM in Default
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.
<!-- 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:
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:
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:
How about this:
answered Mar 30, 2011 at 11:27 AM