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

stephen.poulitsis gravatar image

stephen.poulitsis
30 1 1 2

(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

Scot Hauder gravatar image

Scot Hauder
6.1k 13 15 18

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

stephen.poulitsis gravatar image

stephen.poulitsis
30 1 1 2

(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

stephen.poulitsis gravatar image

stephen.poulitsis
30 1 1 2

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x133
x7

asked: Aug 18, 2012 at 02:40 PM

Seen: 1937 times

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