question

stephen.poulitsis avatar image
stephen.poulitsis asked

Nested while loop to calculate moving max values

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
updatewhile
βιβλίο1.jpg (302.5 KiB)
10 |1200

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

Scot Hauder avatar image
Scot Hauder answered
DECLARE @IntervalDays int = 5 UPDATE dm1 SET newMax = CASE WHEN dm1.n < @IntervalDays THEN NULL ELSE (SELECT MAX(dm2.tHigh) FROM zpData_Mod dm2 WHERE dm2.n > (dm1.n - @IntervalDays) AND dm2.n
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.

Dear Scot Thank you very much for your quick response. Trying to understand your compact code made me realise how much I have still to learn, and you are only half my age! Well Done! Can you help me a little further? a) Assuming the interval I am interested in calculating is 5 days, the first Max value that will update newMax will be on the 5th day. I would then like to calculate the previous 5 period high on the 6th day, the previous 5 period high on the 7th day and so forth. b) Why when I change the @IntervalDays variable from 5 to any other number, eg. 10 does the code return NULL for all values in newMax? And finally c) wouldn't it be easier to use n instead of tDate, given that the dates used in this database are weekdays and holidays are ommitted? Thanks again Regards Stephen
0 Likes 0 ·
Show more comments
stephen.poulitsis avatar image
stephen.poulitsis answered
Scot, as was to be expected, you were right! If the data contains dates as I had shown in the sample data then the code works like a charm. What I failed to explain was that the "real" data is stock/commodity/forex end of day data, where the dates refer only to weekdays (with a sprinkle of holidays interspersed). That is the reason I used the 'n'value - (something I had seen in Gabriel Prester's article - "Calculating moving averages with T-SQL, www.sqlservercentral.com). Unfortunately my understanding of SQL is like a short-sighted person trying to read a book - I get the general concept, but have to try really hard to understand some of the details - I have a long way to go! Using your code as a base I have come up with the following, which seems to work (it doesn't determine the max value for the first interval however, which isn't so much of a problem for what I want to further achieve): ---=====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) DECLARE @IntervalDays int = 11 UPDATE dm1 SET newMax =(SELECT MAX(CASE WHEN dm2.n < @intervaldays THEN NULL ELSE dm2.tHigh END) FROM zpData_Mod dm2 WHERE dm2.n > dm1.n - @IntervalDays AND dm2.n <= dm1.n AND dm2.InstrumentID = dm1.InstrumentID GROUP BY dm2.InstrumentID HAVING COUNT(tHigh) >= @IntervalDays) FROM zpData_mod dm1 SELECT * FROM zpData_mod ORDER BY InstrumentID, tDate What do you think? Always grateful for your input Be Well, Stephen
10 |1200

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

stephen.poulitsis avatar image
stephen.poulitsis answered
Scot, this is a sample of the data that I'm using[link text][1] [1]: /storage/temp/ 362-results_max.txt Remember I'm a copy-paste guy! I use the row-number to overcome the gaps in the dates. OK, I understand what you mean regarding the HAVING statement!

results_max.txt (159.8 KiB)
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.

I see now, you don't want the last 5 dates, you want the last 5 days with activity. I've edited my answer above.
0 Likes 0 ·
Dear Scot, sorry for the delayed response! Thanks so much for all your help!
0 Likes 0 ·

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.