question

I would like to thank you in advance for the opportunity to help solve my problem. I am an sql neewbie, with the following problem: The table zpData contains the daily high price for a specific number of days (eg. 100) for a specific number of instruments (stocks, commodities, etc) - about 15000 instruments. It has been modified (zpData_mod) so that each instrument is ordered by date, using the variable n. ---=====Setup working table for MAXx SELECT ROW_NUMBER() OVER (PARTITION BY InstrumentID ORDER BY tdate)n ,InstrumentID ,tDate ,tHigh ,CAST (NULL AS DECIMAL(12,5)) [newMax] INTO zpData_mod FROM zpData CREATE CLUSTERED INDEX ix_n ON zpData_mod (InstrumentID, tdate) I am trying to achieve the following: After a defined interval (eg. 14 days), I want to calculate the maximum high price during the preceding 14 days. Following this, for the rest of the dates in the table relating to the instrument I want to calculate the maximum high price on the specified interval - a type of "moving" calculation. For example, if the interval is 5 days this would be the result I am aiming to obtain (for each instrument) ![alt text][1] I have tried to achieve the above result using a nested while loop which utilizes a select and update query. The inner loop obtains the first maximum high for each instrument and the update query updates the newMax column of the zpData_mod table with the result. This seems to works correctly. Although it finds the required value, when I redirect the data to a seperate table (using insert into...), the maximum for each period is duplicated by the maximum number assigned to the inner loop. The outer loop, aims at repeating this process for the rest of the dates for each instrument. The problem is that the calculation is performed correctly, but in the zpData_mod table, the newMax field displays the data of the last iteration. All the previous data is not visible. Again, when I redirect the data to another table, I see that the calculations are performed as I had hoped they would be. Here is the rest of the code: Note: I have set the counters to work on a small number of data in the table for testing purposes. ---declare variables needed DECLARE @calx_intervals int DECLARE @prev_instrumentID int DECLARE @next_max decimal(12,5) DECLARE @new_max decimal(12,5) DECLARE @counter_i int DECLARE @counter_j int DECLARE @omega_i int DECLARE @omega_j int DECLARE @start int ---Setup the default intervals SET @calx_intervals =14 SET @omega_i = 3 SET @omega_j = 2 SET @counter_i = 0 ---Set Up External Counter WHILE @counter_i < @omega_i BEGIN SET @counter_j = 0 SET @prev_instrumentID = 1 ---Set Up Internal Counter WHILE @counter_j < @omega_j BEGIN ---Calculate the maximum value for the @calx_interval SELECT @next_max = max(CASE WHEN @prev_instrumentID = instrumentID THEN CASE WHEN n<=@calx_intervals + @counter_i and n>@counter_i THEN tHigh ELSE 1 END ELSE 2 END) FROM zpData_mod ---Update the specified n row with the max value UPDATE zpData_mod SET @new_max = newmax = CASE ---WHEN n<@calx_intervals then null WHEN n=@calx_intervals + @counter_i THEN @next_max END FROM zpData_mod WHERE @prev_instrumentID=instrumentID ---- ``INSERT INTO zpData_mod2 (N, instrumentID, THIGH, newmax) ---- SELECT n, instrumentid, thigh, newmax ---- FROM zpData_mod ---- WHERE newmax is not null SET @prev_instrumentID = @prev_instrumentID + 1 SET @counter_j = @counter_j + 1 END SET @counter_i = @counter_i + 1 END select * FROM zpData_mod What am I doing wrong? [1]: /storage/temp/357-βιβλίο1.jpg