· 8 years ago · Nov 23, 2017, 06:40 AM
1use NOAA
2
3if object_id(N'dbo.station_daily_average', N'U') is null
4create table station_daily_average(
5 Station int
6 ,[Year] char(4)
7 ,[Month] char(2)
8 ,[Day] char(2)
9 ,WindDirection smallint
10 ,WindSpeed smallint
11 ,WindGust smallint
12 ,CloudCeiling smallint
13 ,Visibility int
14 ,Temperature decimal(5,2)
15 ,DewPoint decimal(8,2)
16 ,SeaLevelPressure decimal(6,2)
17 ,Altimeter decimal(6,2)
18 ,StationPressure decimal(6,2)
19 ,MaxTemp smallint
20 ,MinTemp smallint
21 ,PrecipTotal decimal(6,2)
22 ,PrecipTrace int
23 ,SnowDepth smallint
24 ,SnowTrace)
25
26if object_id(N'dbo.station_daily_median', N'U') is null
27create table station_daily_median(
28 Station int
29 ,[Year] char(4)
30 ,[Month] char(2)
31 ,[Day] char(2)
32 ,WindDirection smallint
33 ,WindSpeed smallint
34 ,WindGust smallint
35 ,CloudCeiling smallint
36 ,Visibility int
37 ,Temperature decimal(5,2)
38 ,DewPoint decimal(8,2)
39 ,SeaLevelPressure decimal(6,2)
40 ,Altimeter decimal(6,2)
41 ,StationPressure decimal(6,2)
42 ,MaxTemp smallint
43 ,MinTemp smallint
44 ,PrecipTotal decimal(6,2)
45 ,SnowDepth smallint)
46
47if object_id(N'dbo.station_monthly_average', N'U') is null
48create table station_monthly_average(
49 Station int
50 ,[Year] char(4)
51 ,[Month] char(2)
52 ,WindDirection smallint
53 ,WindSpeed smallint
54 ,WindGust smallint
55 ,CloudCeiling smallint
56 ,Visibility int
57 ,Temperature decimal(5,2)
58 ,DewPoint decimal(8,2)
59 ,SeaLevelPressure decimal(6,2)
60 ,Altimeter decimal(6,2)
61 ,StationPressure decimal(6,2)
62 ,MaxTemp smallint
63 ,MinTemp smallint
64 ,Precip decimal(6,2)
65 ,PrecipTraceDays int
66 ,SnowDepth smallint
67 ,SnowTraceDays int)
68
69if object_id(N'dbo.station_monthly_median', N'U') is null
70create table station_monthly_median(
71 Station int
72 ,[Year] char(4)
73 ,[Month] char(2)
74 ,WindDirection smallint
75 ,WindSpeed smallint
76 ,WindGust smallint
77 ,CloudCeiling smallint
78 ,Visibility int
79 ,Temperature decimal(5,2)
80 ,DewPoint decimal(8,2)
81 ,SeaLevelPressure decimal(6,2)
82 ,Altimeter decimal(6,2)
83 ,StationPressure decimal(6,2)
84 ,MaxTemp smallint
85 ,MinTemp smallint
86 ,Precip decimal(6,2)
87 ,SnowDepth smallint)
88
89if object_id(N'dbo.station_annual_average', N'U') is null
90create table station_annual_average(
91 Station int
92 ,[Year] char(4)
93 ,WindDirection smallint
94 ,WindSpeed smallint
95 ,WindGust smallint
96 ,CloudCeiling smallint
97 ,Visibility int
98 ,Temperature decimal(5,2)
99 ,DewPoint decimal(8,2)
100 ,SeaLevelPressure decimal(6,2)
101 ,Altimeter decimal(6,2)
102 ,StationPressure decimal(6,2)
103 ,MaxTemp smallint
104 ,MinTemp smallint
105 ,Precip decimal(6,2)
106 ,PrecipTraceDays int
107 ,SnowDepth smallint
108 ,SnowTraceDays int)
109
110if object_id(N'dbo.station_annual_median', N'U') is null
111create table station_annual_median(
112 Station int
113 ,[Year] char(4)
114 ,WindDirection smallint
115 ,WindSpeed smallint
116 ,WindGust smallint
117 ,CloudCeiling smallint
118 ,Visibility int
119 ,Temperature decimal(5,2)
120 ,DewPoint decimal(8,2)
121 ,SeaLevelPressure decimal(6,2)
122 ,Altimeter decimal(6,2)
123 ,StationPressure decimal(6,2)
124 ,MaxTemp smallint
125 ,MinTemp smallint
126 ,Precip decimal(6,2)
127 ,SnowDepth smallint)
128
129if object_id('tmpdb..station_average') is null
130create table #station_average(
131 Station int
132 ,[Year] char(4)
133 ,[Month] char(2)
134 ,[Day] char(2)
135 ,[Time] char(4)
136 ,WindDirection smallint
137 ,WindSpeed smallint
138 ,WindGust smallint
139 ,CloudCeiling smallint
140 ,Visibility int
141 ,Temperature decimal(5,2)
142 ,DewPoint decimal(8,2)
143 ,SeaLevelPressure decimal(6,2)
144 ,Altimeter decimal(6,2)
145 ,StationPressure decimal(6,2)
146 ,MaxTemp smallint
147 ,MinTemp smallint
148 ,Precip1Hour decimal(6,2)
149 ,Precip6Hour decimal(6,2)
150 ,Precip24Hour decimal(6,2)
151 ,PrecipOther decimal(6,2)
152 ,Trace bit
153 ,SnowDepth smallint
154 ,SnowTrace bit)
155
156go
157
158use NOAA
159
160declare @stationID int
161declare @stationMaxRows bigint
162
163set @stationMaxRows = (
164 select max(StationIncrementalID)
165 from station_data)
166
167set @stationID = 1
168
169while @stationID <= @stationMaxRows
170
171begin
172
173 if exists (select Station
174 from weather_data
175 where weather_data.Station = @stationID)
176
177 begin
178 declare @Year char(4)
179 declare @MaxYear char(4)
180
181 set @MaxYear = 2017
182
183 delete from #station_average
184
185 insert into #station_average
186 select Station
187 ,[Year]
188 ,[Month]
189 ,[Day]
190 ,[Time]
191 ,WindDirection
192 ,WindSpeed
193 ,WindGust
194 ,CloudCeiling
195 ,Visibility
196 ,Temperature
197 ,DewPoint
198 ,SeaLevelPressure
199 ,Altimeter
200 ,StationPressure
201 ,MaxTemp
202 ,MinTemp
203 ,Precip1Hour
204 ,Precip6Hour
205 ,Precip24Hour
206 ,PrecipOther
207 ,(
208 case when [1HourTrace] is not null or
209 [6HourTrace] is not null or
210 [24HourTrace] is not null or
211 [OtherTrace] is not null
212 then 1
213 else null
214 ) as Trace
215 ,SnowDepth
216 ,(
217 case when [SnowTrace] is not null
218 then 1
219 else null
220 ) as SnowTrace
221 from weather_data
222 where
223 weather_data.Station = @stationID
224
225 set @Year = (
226 select min([Year])
227 from #station_average)
228
229 while @year <= @MaxYear
230
231 begin
232
233 declare @month char(2)
234
235 set @month = 01
236
237 while @month <= 12
238
239 begin
240
241 declare @day char(2)
242
243 set @day = 01
244
245 while @day <= 31
246
247 begin
248 insert into station_daily_average
249 select Station
250 ,Year
251 ,Month
252 ,Day
253 ,avg(WindDirection)
254 ,avg(WindSpeed)
255 ,avg(WindGust)
256 ,avg(CloudCeiling)
257 ,avg(Visibility)
258 ,avg(Temperature)
259 ,avg(DewPoint)
260 ,avg(SeaLevelPressure)
261 ,avg(SeaLevelPressuure)
262 ,avg(Altimeter)
263 ,avg(StationPressure)
264 ,avg(MaxTemp)
265 ,avg(MinTemp)
266 ,(select sum(
267 Precip1Hour + Precip6Hour + Precip24Hour + PrecipOther
268 from #station_average
269 where
270 [Year] = @year and
271 [Month] = @month and
272 [Day] = @day)
273 as TotalPrecip
274 ,(
275 case when [Trace] is not null
276 then 1
277 else null
278 ) as PrecipTrace
279 ,avg(SnowDepth)
280 ,(
281 case when [SnowTrace] is not null
282 then 1
283 else null
284 ) as SnowTrace
285 from #station_average
286 where
287 #station_average.[Year] = @year and
288 #station_average.[Month] = @month and
289 #station_average.[Day] = @day
290
291 insert into station_daily_median
292 select Station
293 ,Year
294 ,Month
295 ,Day
296
297 set @day = @day + 1
298
299 end
300
301 insert into #station_daily
302 select *
303 from station_daily_average
304
305 insert into station_monthly_average
306 select Station
307 ,Year
308 ,Month
309 ,avg(WindDirection)
310 ,avg(WindSpeed)
311 ,avg(WindGust)
312 ,avg(CloudCeiling)
313 ,avg(Visibility)
314 ,avg(Temperature)
315 ,avg(DewPoint)
316 ,avg(SeaLevelPressure)
317 ,avg(SeaLevelPressuure)
318 ,avg(Altimeter)
319 ,avg(StationPressure)
320 ,avg(MaxTemp)
321 ,avg(MinTemp)
322 ,avg(select TotalPrecip
323 from station_daily_average
324 where station_daily_average.[Station] = @stationID and
325 where station_daily_average.[Year] = @year and
326 where station_daily_average.[Month] = @month)
327 as Precip
328 ,(
329 select count(*)
330 from station_daily_average
331
332 ,avg(SnowDepth)
333 from #station_average
334 where
335 #station_average.[Year] = @year and
336 #station_average.[month] = @month
337
338 end
339
340 set @month = @month + 1
341
342 end
343
344 insert into station_annual_average
345 select Station
346 ,Year
347 ,avg(WindDirection)
348 ,avg(WindSpeed)
349 ,avg(WindGust)
350 ,avg(CloudCeiling)
351 ,avg(Visibility)
352 ,avg(Temperature)
353 ,avg(DewPoint)
354 ,avg(SeaLevelPressure)
355 ,avg(SeaLevelPressuure)
356 ,avg(Altimeter)
357 ,avg(StationPressure)
358 ,avg(MaxTemp)
359 ,avg(MinTemp)
360 ,avg(select TotalPrecip
361 from station_daily_average
362 where station_daily_average.Station = stationID and
363 where station_daily_average.[Year] = @year)
364 as Precip
365 ,avg(SnowDepth)
366 from #station_average
367 where
368 year = @year
369
370 set @year = @year + 1
371
372 end
373
374 set @stationID = @stationID + 1
375 end
376
377 else
378
379 begin
380
381 set @stationID = @stationID + 1
382
383 end
384
385end
386
387drop table #station_average