HELP With Cursors 2

[ehenry]

I have a Cursor loop that performs a select statement 230 times for each Station ID I have. The select statement gets temperatures for Jan 1st over the past ten years for the current Station ID, Dumps it into a temporary table, then I take the average for Jan 1st over the 10 years and save it to a table.

What I want to do is add another loop to go through each day of the year for each station. Essentially I am trying to calculate the 10 year average temperature for each day of the year from 230 different stations.

Here is the code I have so far. I was wondering if there was a better way to do this or how to add a nested while loop?

DECLARE @StationID AS int
DECLARE @GetStationID AS CURSOR
DECLARE @Dte AS DATETIME
SET @Dte = '1/1/2025 00:00:00'

SET @GetStationID = CURSOR FOR
SELECT dbo.Trim(WBAN) FROM Station_Ref_ID

OPEN @GetStationID
FETCH NEXT
FROM @GetStationID INTO @StationID
WHILE @@FETCH_STATUS = 0
BEGIN

WHILE @Dte < '1/1/2026'
BEGIN

SELECT [Station ID], Dates, [Low Temp], [Avg Temp], [High Temp]
INTO TenYr
FROM Archive
WHERE (CAST(MONTH(Archive.Dates) AS VARCHAR(2)) +'/'+ CAST(DAY(Archive.Dates) AS VARCHAR(2))) = CAST(MONTH(@Dte) AS VARCHAR(2)) + '/' + CAST(DAY(@Dte) AS VARCHAR(2))
AND YEAR(Dates) BETWEEN 1999 AND 2008 AND [Station ID] Like @StationID

INSERT INTO TenYrData
([Station ID], Dates, [Low Temp], [Avg Temp], [High Temp])
SELECT @StationID, @Dte, ROUND(AVG([Low Temp]),2), ROUND(AVG([Avg Temp]),2), ROUND(AVG([High Temp]),2)
FROM TenYr

DROP TABLE TenYr

SET @Dte = DATEADD(DAY, 1, @Dte)

END

FETCH NEXT
FROM @GetStationID INTO @StationID
END
CLOSE @GetStationID
DEALLOCATE @GetStationID

 

[bborissov]

Could you please post some example data and what you want as a result (and do not post data for the whole 10 years )
I think that could be done just with recordset operations, w/o using cursor, but I need to see the data and desired result.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ehenry]

I Have an Archive table that is set up like below. The Unique ID is the Station ID & The date difference between the date and 1900. The only relevent columns are the station ID, Dates and the three temperature columns.

Unique ID Station ID ID Code Dates Low High Avg
301740085 3017 DEN40085 10/1/2009 0:00 33 56 44.5
301740086 3017 DEN40086 10/2/2009 0:00 26 60 43
301740087 3017 DEN40087 10/3/2009 0:00 31 67 49
301740088 3017 DEN40088 10/4/2009 0:00 39 60 49.5
301740089 3017 DEN40089 10/5/2009 0:00 35 60 47.5

For Station 3017 I need to Select the three temp columns For everey occurance of 10/1 over the past 10 years (Making 10 rows) Then take the Average of each and that would be the 10/1 Ten Year normal Temperature for Low Avg and High Temps. Then repeat this for each day of the year. So in a New table I would have something like this:
Station ID Dates Low High Avg
3017 1-Jan 35 54 48
3017 2-Jan 35 54 48
….
3017 31-Dec 31 42 38

This process needs to be repeated For each station. The result is the 10 year Normal High Low and Avg Temperature For each day of the year for each of the 230 stations.

Station ID Dates Low High Avg
3017 1-Jan 35 54 48
….
3017 31-Dec 31 42 38
3018 1-Jan 25 38 26
….
3018 31-Dec 34 46 41

 

[gmmastros]

Try this:

Select StationId, 
       Month(Dates) As MonthNumber, 
       Day(Dates) As DayNumber, 
       Avg(Low) As AverageLow, 
       Avg(High) As AverageHigh
From   YourTableName
Where  Dates >= DateAdd(Year, -10, GetDate()) 
       And Dates < GetDate()
Group By StationId, Month(Dates), Day(Dates)

Of course, you'll probably need to correct the table name and the column names, but I think it will return the information you are looking for.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I forgot the order by. I think you should add (as the last line)....

Order By StationId, Month(Dates), Day(Dates)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JeffModen]

EHenry,

Do you want a rolling 10 year average or just the 10 year fixed average?

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[ehenry]

The code posted above gives the same output as what I ran using a cursor to loop through the data, only that takes 1 second and mine took 7 hours!! Thanks for your help!

 

[gmmastros]

ehenry,

Thanks for posting that. I love hearing this type of feedback!

There are some things we could do to speed it up even further, but I suspect your are satisfied with the existing performance.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

ehenry, now you know why so many of us are anti-cursor!

"NOTHING is more important in a database than integrity." ESquared

 

[ehenry]

So I took the code you provided above and duped the results into a table From there i want to select only data in a range from DATEADD(DAY,-7,GETDATE()) to DATEADD(DAY,14,GETDATE())

I ran into a problem when there is a change in month in the date range. I use the following select statement and get no results.

SELECT [Station ID], MonthNumber, DayNumber, TenYrLow, TenYrAvg, TenYrHigh
From TenYr
WHERE MonthNumber BETWEEN MONTH(DATEADD(DAY, -8, GETDATE()) AND MONTH(DATEADD(DAY, 14, GETDATE()))
AND DayNumber BETWEEN Day(DATEADD(DAY, -8, GETDATE()) AND Day(DATEADD(DAY, 14, GETDATE()))
ORDER BY [Station ID], MonthNumber, DayNumber

This returns no results. Since it cannot select from say the 25th day to the 15th day.

This also raises the same issue when the date range is in between two years.

My solution was to concatenate the monthNumber/dayNumber/YEAR(GETDATE()) into a new date column.

And for the year change I added an if statement if the current date is > 12/15/+YEAR(GETDATE()) then it re-appends the same data and updates the null values in the Date column to have monthNumber/dayNumber/YEAR(DATEADD(YEAR,1,GETDATE()))

Is there a better way of handling this issue?

 

[gmmastros]

In situations like this, I like to work with the date data type as much as possible because it's easier than any other method.

This is also a good example where a numbers table in your database would help make the query easier to understand and faster to execute. There is a "poor man's" number table in the master database. For example:

[tt][blue]Select Number From master..spt_values Where Type = 'p'[/blue][/tt]

We can use this numbers table to get a range of dates (nevermind that there is also year information attached to it).

Select   Top 21 DateAdd(Day, Number-7, GetDate())
From     master..spt_values 
Where    Type = 'p'
Order BY Number

Notice that since we are doing date math, this will work for leap years, spanning month boundaries and spanning year boundaries.

Now, we can also get month and day values too.

Select Top 21 
       Month(DateAdd(Day, Number-7, GetDate())) As MonthNumber, 
       Day(DateAdd(Day, Number-7, GetDate())) As DayNumber
From   master..spt_values 
Where  Type = 'p'
Order BY Number

As you can see, we have the proper month number and day number returned by this query. Now we can make this a derived table and join it to you TenYr table, like this:

SELECT [Station ID], TenYr.MonthNumber, TenYr.DayNumber, TenYrLow, TenYrAvg, TenYrHigh
From   TenYr
       Inner Join (
         Select Top [!]21[/!] 
                Month(DateAdd(Day, Number-[!]7[/!], GetDate())) As MonthNumber, 
                Day(DateAdd(Day, Number-[!]7[/!], GetDate())) As DayNumber
         From   master..spt_values 
         Where  Type = 'p'
         Order BY Number
         ) As DateRange
         On TenYr.MonthNumber = DateRange.MonthNumber
         And TenYr.DayNumber = DateRange.DayNumber
ORDER BY [Station ID], TenYr.MonthNumber, TenYr.DayNumber

Notice the "magic numbers" embedded in this query. These numbers control how many days worth of data to return (the 21) and how far back (the 7) to go.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom