x

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

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.jpg (309.8 kB)
more ▼

asked Aug 18, 2012 at 02:40 PM in Default

avatar image

stephen.poulitsis
30 1 1 4

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
 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 <= dm1.n
            AND dm2.InstrumentID = dm1.InstrumentID
            GROUP BY dm2.InstrumentID) 
      END
 FROM zpData_mod dm1
 
 
 SELECT * FROM zpData_mod
 ORDER BY InstrumentID, tDate
more ▼

answered Aug 18, 2012 at 11:32 PM

avatar image

Scot Hauder
6.4k 13 16 22

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

Aug 21, 2012 at 03:44 PM stephen.poulitsis

You're welcome. I'm 42 and still have a lot to learn! Site defaulted me to 20 and increases each year..

A. I think I am doing that according to your desired results in the question. Here are the results from my answer:

 1 2012-01-01 10 NULL
 1 2012-01-02 15 NULL
 1 2012-01-03 9 NULL
 1 2012-01-04 8 NULL
 1 2012-01-05 10 15
 1 2012-01-06 9 15
 1 2012-01-07 10 10
 1 2012-01-08 11 11

B. Yes, all NULLs with this sample data

C. You could use n, I didn't because I didn't add that column to my zpData_Mod table. I don't think the fact it only contains weekdays would make it easier.

Aug 21, 2012 at 11:59 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Aug 22, 2012 at 10:49 AM

avatar image

stephen.poulitsis
30 1 1 4

(comments are locked)
10|1200 characters needed characters left

Scot, this is a sample of the data that I'm usinglink text

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 (163.6 kB)
more ▼

answered Aug 22, 2012 at 11:30 AM

avatar image

stephen.poulitsis
30 1 1 4

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.

Aug 22, 2012 at 10:45 PM Scot Hauder

Dear Scot, sorry for the delayed response! Thanks so much for all your help!

Aug 27, 2012 at 04:23 PM stephen.poulitsis
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x162
x7

asked: Aug 18, 2012 at 02:40 PM

Seen: 2567 times

Last Updated: Aug 27, 2012 at 04:23 PM

Copyright 2016 Redgate Software. Privacy Policy