· 8 years ago · Dec 10, 2017, 06:38 PM
1use NOAA
2
3if object_id(N'dbo.station_daily', N'U') is null
4create table station_daily(
5 Station int
6 ,[Year] char(4)
7 ,[Month] char(2)
8 ,[Day] char(2)
9 ,Sunrise char(4)
10 ,Sunset char(4)
11 ,WindDirection decimal (6,2)
12 ,WindSpeed decimal (6,2)
13 ,WindGust decimal (6,2)
14 ,CloudCeiling decimal (6,2)
15 ,Visibility int
16 ,Temperature decimal(5,2)
17 ,DewPoint decimal(8,2)
18 ,SeaLevelPressure decimal(6,2)
19 ,Altimeter decimal(6,2)
20 ,StationPressure decimal(6,2)
21 ,MaxTemp decimal (6,2)
22 ,MinTemp decimal (6,2)
23 ,DayMaxTemp decimal (6,2)
24 ,DayMinTemp decimal (6,2)
25 ,DayAvgTemp decimal (6,2)
26 ,NightMaxTemp decimal (6,2)
27 ,NightMinTemp decimal (6,2)
28 ,NightAvgTemp decimal (6,2)
29 ,PrecipTotal decimal(6,2)
30 ,PrecipTrace bit
31 ,SnowDepth decimal (6,2)
32 ,SnowTrace bit
33 ,foreign key (Station) references station_data(StationIncrementalID)
34 ) on WeatherDataReverse_PS (Station)
35
36if object_id(N'dbo.station_daily_median', N'U') is null
37create table station_daily_median(
38 Station int
39 ,[Year] char(4)
40 ,[Month] char(2)
41 ,[Day] char(2)
42 ,WindDirection decimal (6,2)
43 ,WindSpeed decimal (6,2)
44 ,WindGust decimal (6,2)
45 ,CloudCeiling decimal (6,2)
46 ,Visibility decimal(6,2)
47 ,Temperature decimal(5,2)
48 ,DewPoint decimal(8,2)
49 ,SeaLevelPressure decimal(6,2)
50 ,Altimeter decimal(6,2)
51 ,StationPressure decimal(6,2)
52 ,MaxTemp decimal (6,2)
53 ,MinTemp decimal (6,2)
54 ,DayMaxTemp decimal (6,2)
55 ,DayMinTemp decimal (6,2)
56 ,NightMaxTemp decimal (6,2)
57 ,NightMinTemp decimal (6,2)
58 ,PrecipTotal decimal(6,2)
59 ,SnowDepth decimal (6,2)
60 ,foreign key (Station) references station_data(StationIncrementalID)
61 ) on WeatherDataReverse_PS (Station)
62
63if object_id(N'dbo.station_monthly_average', N'U') is null
64create table station_monthly_average(
65 Station int
66 ,[Year] char(4)
67 ,[Month] char(2)
68 ,MinSunrise char(4)
69 ,MaxSunrise char(4)
70 ,AvgSunrise char(4)
71 ,MinSunset char(4)
72 ,MaxSunset char(4)
73 ,AvgSunset char(4)
74 ,WindDirection decimal (6,2)
75 ,WindSpeed decimal (6,2)
76 ,WindGust decimal (6,2)
77 ,CloudCeiling decimal (6,2)
78 ,Visibility decimal(6,2)
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 decimal (6,2)
85 ,MinTemp decimal (6,2)
86 ,DayMaxTemp decimal (6,2)
87 ,DayMinTemp decimal (6,2)
88 ,DayAvgTemp decimal (6,2)
89 ,NightMaxTemp decimal (6,2)
90 ,NightMinTemp decimal (6,2)
91 ,NightAvgTemp decimal (6,2)
92 ,Precip decimal(6,2)
93 ,PrecipTraceDays int
94 ,SnowDepth decimal (6,2)
95 ,SnowTraceDays decimal(6,2)
96 ,foreign key (Station) references station_data(StationIncrementalID)
97 ) on WeatherDataReverse_PS (Station)
98
99if object_id(N'dbo.station_monthly_median', N'U') is null
100create table station_monthly_median(
101 Station int
102 ,[Year] char(4)
103 ,[Month] char(2)
104 ,WindDirection decimal (6,2)
105 ,WindSpeed decimal (6,2)
106 ,WindGust decimal (6,2)
107 ,CloudCeiling decimal (6,2)
108 ,Visibility decimal(6,2)
109 ,Temperature decimal(5,2)
110 ,DewPoint decimal(8,2)
111 ,SeaLevelPressure decimal(6,2)
112 ,Altimeter decimal(6,2)
113 ,StationPressure decimal(6,2)
114 ,MaxTemp decimal (6,2)
115 ,MinTemp decimal (6,2)
116 ,DayMaxTemp decimal (6,2)
117 ,DayMinTemp decimal (6,2)
118 ,DayMedianTemp decimal (6,2)
119 ,NightMaxTemp decimal (6,2)
120 ,NightMinTemp decimal (6,2)
121 ,NightMedianTemp decimal (6,2)
122 ,Precip decimal(6,2)
123 ,SnowDepth decimal (6,2)
124 ,foreign key (Station) references station_data(StationIncrementalID)
125 ) on WeatherDataReverse_PS (Station)
126
127if object_id(N'dbo.station_annual_average', N'U') is null
128create table station_annual_average(
129 Station int
130 ,[Year] char(4)
131 ,MinSunrise char(4)
132 ,MaxSunrise char(4)
133 ,AvgSunrise char(4)
134 ,MinSunset char(4)
135 ,MaxSunset char(4)
136 ,AvgSunset char(4)
137 ,WindDirection decimal (6,2)
138 ,WindSpeed decimal (6,2)
139 ,WindGust decimal (6,2)
140 ,CloudCeiling decimal (6,2)
141 ,Visibility decimal(6,2)
142 ,Temperature decimal(5,2)
143 ,DewPoint decimal(8,2)
144 ,SeaLevelPressure decimal(6,2)
145 ,Altimeter decimal(6,2)
146 ,StationPressure decimal(6,2)
147 ,MaxTemp decimal (6,2)
148 ,MinTemp decimal (6,2)
149 ,DayMaxTemp decimal (6,2)
150 ,DayMinTemp decimal (6,2)
151 ,DayAvgTemp decimal (6,2)
152 ,NightMaxTemp decimal (6,2)
153 ,NightMinTemp decimal (6,2)
154 ,NightAvgTemp decimal (6,2)
155 ,Precip decimal(6,2)
156 ,PrecipTraceDays decimal(6,2)
157 ,SnowDepth decimal (6,2)
158 ,SnowTraceDays decimal(6,2)
159 ,foreign key (Station) references station_data(StationIncrementalID)
160 ) on WeatherDataReverse_PS (Station)
161
162if object_id(N'dbo.station_annual_average', N'U') is null
163create table station_overall_average(
164 Station int
165 ,MinSunrise char(4)
166 ,MaxSunrise char(4)
167 ,AvgSunrise char(4)
168 ,MinSunset char(4)
169 ,MaxSunset char(4)
170 ,AvgSunset char(4)
171 ,WindDirection decimal (6,2)
172 ,MinWindDirection decimal (6,2)
173 ,MaxWindDirection decimal (6,2)
174 ,WindSpeed decimal (6,2)
175 ,MinWindSpeed decimal (6,2)
176 ,WindGust decimal (6,2)
177 ,MaxWindGust decimal (6,2)
178 ,MinWindGust decimal (6,2)
179 ,CloudCeiling decimal (6,2)
180 ,MinCloudCeiling decimal (6,2)
181 ,MaxCloudCeiling decimal (6,2)
182 ,Visibility decimal(6,2)
183 ,MinVisibility decimal(6,2)
184 ,MaxVisibility decimal(6,2)
185 ,Temperature decimal(5,2)
186 ,DewPoint decimal(8,2)
187 ,MinDewPoint decimal(8,2)
188 ,MaxDewPoint decimal(8,2)
189 ,SeaLevelPressure decimal(6,2)
190 ,MinSeaLevelPressure decimal(6,2)
191 ,MaxSeaLevelPressure decimal(6,2)
192 ,Altimeter decimal(6,2)
193 ,MinAltimeter decimal(6,2)
194 ,MaxAltimeter decimal(6,2)
195 ,StationPressure decimal(6,2)
196 ,MinStationPressure decimal(6,2)
197 ,MaxStationPressure decimal(6,2)
198 ,MaxTemp decimal (6,2)
199 ,MinTemp decimal (6,2)
200 ,DayMaxTemp decimal (6,2)
201 ,DayMinTemp decimal (6,2)
202 ,DayAvgTemp decimal (6,2)
203 ,NightMaxTemp decimal (6,2)
204 ,NightMinTemp decimal (6,2)
205 ,NightAvgTemp decimal (6,2)
206 ,Precip decimal(6,2)
207 ,MinPrecip decimal(6,2)
208 ,MaxPrecip decimal(6,2)
209 ,SnowDepth decimal (6,2)
210 ,MinSnowDepth decimal (6,2)
211 ,MaxSnowDepth decimal (6,2)
212 ,foreign key (Station) references station_data(StationIncrementalID)
213 ) on [THIRDRARY]
214
215if object_id(N'dbo.station_annual_average', N'U') is null
216create table station_averages_by_day(
217 Station int
218 ,Month int
219 ,Day int
220 ,MinSunrise char(4)
221 ,MaxSunrise char(4)
222 ,AvgSunrise char(4)
223 ,MinSunset char(4)
224 ,MaxSunset char(4)
225 ,AvgSunset char(4)
226 ,WindDirection decimal (6,2)
227 ,MinWindDirection decimal (6,2)
228 ,MaxWindDirection decimal (6,2)
229 ,WindSpeed decimal (6,2)
230 ,MinWindSpeed decimal (6,2)
231 ,WindGust decimal (6,2)
232 ,MaxWindGust decimal (6,2)
233 ,MinWindGust decimal (6,2)
234 ,CloudCeiling decimal (6,2)
235 ,MinCloudCeiling decimal (6,2)
236 ,MaxCloudCeiling decimal (6,2)
237 ,Visibility decimal(6,2)
238 ,MinVisibility decimal(6,2)
239 ,MaxVisibility decimal(6,2)
240 ,Temperature decimal(5,2)
241 ,DewPoint decimal(8,2)
242 ,MinDewPoint decimal(8,2)
243 ,MaxDewPoint decimal(8,2)
244 ,SeaLevelPressure decimal(6,2)
245 ,MinSeaLevelPressure decimal(6,2)
246 ,MaxSeaLevelPressure decimal(6,2)
247 ,Altimeter decimal(6,2)
248 ,MinAltimeter decimal(6,2)
249 ,MaxAltimeter decimal(6,2)
250 ,StationPressure decimal(6,2)
251 ,MinStationPressure decimal(6,2)
252 ,MaxStationPressure decimal(6,2)
253 ,MaxTemp decimal (6,2)
254 ,MinTemp decimal (6,2)
255 ,DayMaxTemp decimal (6,2)
256 ,DayMinTemp decimal (6,2)
257 ,DayAvgTemp decimal (6,2)
258 ,NightMaxTemp decimal (6,2)
259 ,NightMinTemp decimal (6,2)
260 ,NightAvgTemp decimal (6,2)
261 ,Precip decimal(6,2)
262 ,MinPrecip decimal(6,2)
263 ,MaxPrecip decimal(6,2)
264 ,SnowDepth decimal (6,2)
265 ,MinSnowDepth decimal (6,2)
266 ,MaxSnowDepth decimal (6,2)
267 ,foreign key (Station) references station_data(StationIncrementalID)
268 ) on WeatherDataReverse_PS (Station)
269
270if object_id(N'dbo.station_annual_average', N'U') is null
271create table station_averages_by_month(
272 Station int
273 ,Month int
274 ,MinSunrise char(4)
275 ,MaxSunrise char(4)
276 ,AvgSunrise char(4)
277 ,MinSunset char(4)
278 ,MaxSunset char(4)
279 ,AvgSunset char(4)
280 ,WindDirection decimal (6,2)
281 ,MinWindDirection decimal (6,2)
282 ,MaxWindDirection decimal (6,2)
283 ,WindSpeed decimal (6,2)
284 ,MinWindSpeed decimal (6,2)
285 ,WindGust decimal (6,2)
286 ,MaxWindGust decimal (6,2)
287 ,MinWindGust decimal (6,2)
288 ,CloudCeiling decimal (6,2)
289 ,MinCloudCeiling decimal (6,2)
290 ,MaxCloudCeiling decimal (6,2)
291 ,Visibility decimal(6,2)
292 ,MinVisibility decimal(6,2)
293 ,MaxVisibility decimal(6,2)
294 ,Temperature decimal(5,2)
295 ,DewPoint decimal(8,2)
296 ,MinDewPoint decimal(8,2)
297 ,MaxDewPoint decimal(8,2)
298 ,SeaLevelPressure decimal(6,2)
299 ,MinSeaLevelPressure decimal(6,2)
300 ,MaxSeaLevelPressure decimal(6,2)
301 ,Altimeter decimal(6,2)
302 ,MinAltimeter decimal(6,2)
303 ,MaxAltimeter decimal(6,2)
304 ,StationPressure decimal(6,2)
305 ,MinStationPressure decimal(6,2)
306 ,MaxStationPressure decimal(6,2)
307 ,MaxTemp decimal (6,2)
308 ,MinTemp decimal (6,2)
309 ,DayMaxTemp decimal (6,2)
310 ,DayMinTemp decimal (6,2)
311 ,DayAvgTemp decimal (6,2)
312 ,NightMaxTemp decimal (6,2)
313 ,NightMinTemp decimal (6,2)
314 ,NightAvgTemp decimal (6,2)
315 ,Precip decimal(6,2)
316 ,MinPrecip decimal(6,2)
317 ,MaxPrecip decimal(6,2)
318 ,SnowDepth decimal (6,2)
319 ,MinSnowDepth decimal (6,2)
320 ,MaxSnowDepth decimal (6,2)
321 ,foreign key (Station) references station_data(StationIncrementalID)
322 ) on WeatherDataReverse_PS (Station)
323
324if object_id(N'dbo.station_annual_median', N'U') is null
325create table station_annual_median(
326 Station int
327 ,[Year] char(4)
328 ,WindDirection decimal (6,2)
329 ,WindSpeed decimal (6,2)
330 ,WindGust decimal (6,2)
331 ,CloudCeiling decimal (6,2)
332 ,Visibility decimal(6,2)
333 ,Temperature decimal(5,2)
334 ,DewPoint decimal(8,2)
335 ,SeaLevelPressure decimal(6,2)
336 ,Altimeter decimal(6,2)
337 ,StationPressure decimal(6,2)
338 ,MaxTemp decimal (6,2)
339 ,MinTemp decimal (6,2)
340 ,DayMaxTemp decimal (6,2)
341 ,DayMinTemp decimal (6,2)
342 ,DayMedianTemp decimal (6,2)
343 ,NightMaxTemp decimal (6,2)
344 ,NightMinTemp decimal (6,2)
345 ,NightMedianTemp decimal (6,2)
346 ,Precip decimal(6,2)
347 ,SnowDepth decimal (6,2)
348 ,foreign key (Station) references station_data(StationIncrementalID)
349 ) on [SECONDARY]
350
351if object_id('tmpdb..station_average') is null
352create table #station_average(
353 Station int
354 ,[Year] char(4)
355 ,[Month] char(2)
356 ,[Day] char(2)
357 ,[Time] char(4)
358 ,WindDirection decimal (6,2)
359 ,WindSpeed decimal (6,2)
360 ,WindGust decimal (6,2)
361 ,CloudCeiling decimal (6,2)
362 ,Visibility decimal(6,2)
363 ,Temperature decimal(5,2)
364 ,DewPoint decimal(8,2)
365 ,SeaLevelPressure decimal(6,2)
366 ,Altimeter decimal(6,2)
367 ,StationPressure decimal(6,2)
368 ,MaxTemp decimal (6,2)
369 ,MinTemp decimal (6,2)
370 ,Precip1Hour decimal(6,2)
371 ,Precip6Hour decimal(6,2)
372 ,Precip24Hour decimal(6,2)
373 ,PrecipOther decimal(6,2)
374 ,Trace bit
375 ,SnowDepth decimal (6,2)
376 ,SnowTrace bit)
377
378 create clustered index IDX_temp_station_year on #station_average([Year])
379 create index IDX_temp_station_other on #station_average([Month],[Day],[Time])
380
381if object_id('tmpdb..station_daily') is null
382create table #station_daily(
383 Station int
384 ,[Year] char(4)
385 ,[Month] char(2)
386 ,[Day] char(2)
387 ,Sunrise char(4)
388 ,Sunset char(4)
389 ,WindDirection decimal (6,2)
390 ,WindSpeed decimal (6,2)
391 ,WindGust decimal (6,2)
392 ,CloudCeiling decimal (6,2)
393 ,Visibility decimal(6,2)
394 ,Temperature decimal(5,2)
395 ,DewPoint decimal(8,2)
396 ,SeaLevelPressure decimal(6,2)
397 ,Altimeter decimal(6,2)
398 ,StationPressure decimal(6,2)
399 ,MaxTemp decimal (6,2)
400 ,MinTemp decimal (6,2)
401 ,DayMaxTemp decimal (6,2)
402 ,DayMinTemp decimal (6,2)
403 ,DayAvgTemp decimal (6,2)
404 ,NightMaxTemp decimal (6,2)
405 ,NightMinTemp decimal (6,2)
406 ,NightAvgTemp decimal (6,2)
407 ,PrecipTotal decimal(6,2)
408 ,PrecipTrace bit
409 ,SnowDepth decimal (6,2)
410 ,SnowTrace bit)
411
412 create clustered index IDX_temp_daily_year on #station_average([Year])
413 create index IDX_temp_daily_other on #station_average([Month],[Day])
414
415if object_id('tmpdb..station_sunrise') is null
416create table #station_sunrise(
417 Sunrise datetime)
418
419if object_id('tmpdb..station_sunset') is null
420create table #station_sunset(
421 Sunset datetime)
422
423go
424
425use NOAA
426
427declare @stationID int
428declare @stationMaxRows bigint
429
430set @stationMaxRows = (
431 select max(StationIncrementalID)
432 from station_data)
433
434set @stationID = 1
435
436while @stationID <= @stationMaxRows
437
438begin
439
440 if exists (select Station
441 from weather_data
442 where weather_data.Station = @stationID)
443
444 begin
445
446 DBCC SHRINKFILE (NOAA_log, 1)
447 DBCC SHRINKFILE (NOAA_backup_log, 1)
448
449 declare @Year char(4)
450 declare @MaxYear char(4)
451
452 delete from #station_average
453 delete from #station_daily
454
455 insert into #station_average
456 select Station
457 ,[Year]
458 ,[Month]
459 ,[Day]
460 ,[Time]
461 ,WindDirection
462 ,WindSpeed
463 ,WindGust
464 ,CloudCeiling
465 ,Visibility
466 ,Temperature
467 ,DewPoint
468 ,SeaLevelPressure
469 ,Altimeter
470 ,StationPressure
471 ,MaxTemp
472 ,MinTemp
473 ,Precip1Hour
474 ,Precip6Hour
475 ,Precip24Hour
476 ,PrecipOther
477 ,(
478 case when [1HourTrace] is not null then 1
479 when [6HourTrace] is not null then 1
480 when [24HourTrace] is not null then 1
481 when [OtherTrace] is not null then 1
482 else null
483 end
484 ) as Trace
485 ,SnowDepth
486 ,(
487 case when [SnowTrace] is not null
488 then 1
489 else null
490 end
491 ) as SnowTrace
492 from weather_data
493 where
494 weather_data.Station = @stationID
495
496 set @Year = (
497 select min([Year])
498 from #station_average)
499
500 set @MaxYear = (
501 select max([Year])
502 from #station_average)
503
504 while @year <= @MaxYear
505
506 begin
507
508 declare @month char(2)
509
510 set @month = (select min(Month)
511 from #station_average
512 where #station_average.Year = @year)
513
514 while @month <= (select max(Month)
515 from #station_average
516 where #station_average.Year = @year)
517
518 begin
519
520 declare @day char(2)
521
522 set @day = (select min(Day)
523 from #station_average
524 where #station_average.Year = @year and
525 #station_average.Month = @month)
526
527 while @day <= (select max(Day)
528 from #station_average
529 where #station_average.Year = @year and
530 #station_average.Month = @month)
531
532 begin
533
534 delete from #station_sunrise
535 delete from #station_sunset
536
537 declare @date varchar(11)
538 declare @offset int
539 declare @lat real
540 declare @long real
541
542 set @date = concat(@month,'/',@day,'/',@year)
543 set @offset = dbo.udf_getUTCOffSet()
544 set @lat = (select Latitude
545 from station_data
546 where station_data.StationIncrementalID = @stationID)
547 set @long = (select Longitude
548 from station_data
549 where station_data.StationIncrementalID = @stationID)
550
551 case when @lat, @long are not null
552 then
553 insert into #station_sunrise
554 exec DetermineSunrise
555 @date
556 ,@lat
557 ,@long
558 ,@offset
559
560 insert into #station_sunset
561 exec DetermineSunset
562 @date
563 ,@lat
564 ,@long
565 ,@offset
566 else ''
567 end
568
569 declare @sunrise char(4)
570 declare @sunset char(4)
571
572 set @sunrise = (
573 select replace(convert(char(4),(select top 1 [Sunrise] from #station_sunrise),108), ':', '')
574 )
575 set @sunset = (
576 select replace(convert(char(4),(select top 1 [Sunset] from #station_sunset),108), ':', '')
577 )
578
579 insert into station_daily
580 select Station
581 ,[Year]
582 ,[Month]
583 ,[Day]
584 ,@sunrise as Sunrise
585 ,@sunset as Sunset
586 ,avg(WindDirection)
587 ,avg(WindSpeed)
588 ,avg(WindGust)
589 ,avg(CloudCeiling)
590 ,avg(Visibility)
591 ,avg(Temperature)
592 ,avg(DewPoint)
593 ,avg(SeaLevelPressure)
594 ,avg(Altimeter)
595 ,avg(StationPressure)
596 ,avg(MaxTemp) as MaxTemp
597 ,avg(MinTemp) as MinTemp
598 ,(select max(
599 Temperature)
600 from #station_average
601 where
602 #station_average.[Year] = @year and
603 #station_average.[Month] = @month and
604 #station_average.[Day] = @day
605 and
606 #station_average.[Time] between @sunrise and @sunset) as DayMaxTemp
607 ,(select min(
608 Temperature)
609 from #station_average
610 where
611 #station_average.[Year] = @year and
612 #station_average.[Month] = @month and
613 #station_average.[Day] = @day
614 and
615 #station_average.[Time] between @sunrise and @sunset) as DayMinTemp
616 ,(select avg(
617 Temperature)
618 from #station_average
619 where
620 #station_average.[Year] = @year and
621 #station_average.[Month] = @month and
622 #station_average.[Day] = @day
623 and
624 #station_average.[Time] between @sunrise and @sunset) as DayAvgTemp
625 ,(select max(
626 Temperature)
627 from #station_average
628 where
629 #station_average.[Year] = @year and
630 #station_average.[Month] = @month and
631 #station_average.[Day] = @day
632 and
633 #station_average.[Time] < @sunrise or
634 #station_average.[Time] > @sunset) as NightMaxTemp
635 ,(select min(
636 Temperature)
637 from #station_average
638 where
639 #station_average.[Year] = @year and
640 #station_average.[Month] = @month and
641 #station_average.[Day] = @day
642 and
643 #station_average.[Time] < @sunrise or
644 #station_average.[Time] > @sunset) as NightMinTemp
645 ,(select avg(
646 Temperature)
647 from #station_average
648 where
649 #station_average.[Year] = @year and
650 #station_average.[Month] = @month and
651 #station_average.[Day] = @day
652 and
653 #station_average.[Time] < @sunrise or
654 #station_average.[Time] > @sunset) as NightAvgTemp
655 ,(select sum(
656 Precip1Hour + Precip6Hour + Precip24Hour + PrecipOther
657 )
658 from #station_average
659 where
660 [Year] = @year and
661 [Month] = @month and
662 [Day] = @day)
663 as TotalPrecip
664 ,(
665 case when [Trace] is not null
666 then 1
667 else null
668 end
669 ) as PrecipTrace
670 ,avg(SnowDepth)
671 ,(
672 case when [SnowTrace] is not null
673 then 1
674 else null
675 end
676 ) as SnowTrace
677 from #station_average
678 where
679 #station_average.[Year] = @year and
680 #station_average.[Month] = @month and
681 #station_average.[Day] = @day
682
683 insert into #station_daily
684 select *
685 from station_daily
686 where
687 station_daily.[Year] = @year and
688 station_daily.[Month] = @month and
689 station_daily.[Day] = @day
690
691 declare @c bigint = (select count(*)
692 from #station_average
693 where #station_average.[Year] = @year and
694 #station_average.[Month] = @month and
695 #station_average.[Day] = @day)
696
697 insert into station_daily_median
698 select [Station] as [Station]
699 ,[Year] as [Year]
700 ,[Month] as [Month]
701 ,[Day] as [Day]
702 ,(
703 select avg(1.0 * [WindDirection])
704 from (
705 select WindDirection
706 from #station_average
707 where #station_average.[Year] = @year and
708 #station_average.[Month] = @month and
709 #station_average.[Day] = @day
710 order by [WindDirection]
711 offset (@c - 1) / 2 rows
712 fetch next 1 + (1 - @c % 2) rows only) as WindDirection
713 ) as WindDirection
714 ,(
715 select avg(1.0 * [WindSpeed])
716 from (
717 select WindSpeed
718 from #station_average
719 where #station_average.[Year] = @year and
720 #station_average.[Month] = @month and
721 #station_average.[Day] = @day
722 order by [WindSpeed]
723 offset (@c - 1) / 2 rows
724 fetch next 1 + (1 - @c % 2) rows only) as WindSpeed
725 ) as WindSpeed
726 ,(
727 select avg(1.0 * [WindGust])
728 from (
729 select WindGust
730 from #station_average
731 where #station_average.[Year] = @year and
732 #station_average.[Month] = @month and
733 #station_average.[Day] = @day
734 order by [WindGust]
735 offset (@c - 1) / 2 rows
736 fetch next 1 + (1 - @c % 2) rows only) as WindGust
737 ) as WindGust
738 ,(
739 select avg(1.0 * [CloudCeiling])
740 from (
741 select CloudCeiling
742 from #station_average
743 where #station_average.[Year] = @year and
744 #station_average.[Month] = @month and
745 #station_average.[Day] = @day
746 order by [CloudCeiling]
747 offset (@c - 1) / 2 rows
748 fetch next 1 + (1 - @c % 2) rows only) as CloudCeiling
749 ) as CloudCeiling
750 ,(
751 select avg(1.0 * [Visibility])
752 from (
753 select Visibility
754 from #station_average
755 where #station_average.[Year] = @year and
756 #station_average.[Month] = @month and
757 #station_average.[Day] = @day
758 order by [Visibility]
759 offset (@c - 1) / 2 rows
760 fetch next 1 + (1 - @c % 2) rows only) as Visibility
761 ) as Visibility
762 ,(
763 select avg(1.0 * [Temperature])
764 from (
765 select Temperature
766 from #station_average
767 where #station_average.[Year] = @year and
768 #station_average.[Month] = @month and
769 #station_average.[Day] = @day
770 order by [Temperature]
771 offset (@c - 1) / 2 rows
772 fetch next 1 + (1 - @c % 2) rows only) as Temperature
773 ) as Temperature
774 ,(
775 select avg(1.0 * [DewPoint])
776 from (
777 select DewPoint
778 from #station_average
779 where #station_average.[Year] = @year and
780 #station_average.[Month] = @month and
781 #station_average.[Day] = @day
782 order by [DewPoint]
783 offset (@c - 1) / 2 rows
784 fetch next 1 + (1 - @c % 2) rows only) as Dewpoint
785 ) as Dewpoint
786 ,(
787 select avg(1.0 * [SeaLevelPressure])
788 from (
789 select SeaLevelPressure
790 from #station_average
791 where #station_average.[Year] = @year and
792 #station_average.[Month] = @month and
793 #station_average.[Day] = @day
794 order by [SeaLevelPressure]
795 offset (@c - 1) / 2 rows
796 fetch next 1 + (1 - @c % 2) rows only) as SeaLevelPressure
797 ) as SeaLevelPressure
798 ,(
799 select avg(1.0 * [Altimeter])
800 from (
801 select Altimeter
802 from #station_average
803 where #station_average.[Year] = @year and
804 #station_average.[Month] = @month and
805 #station_average.[Day] = @day
806 order by [Altimeter]
807 offset (@c - 1) / 2 rows
808 fetch next 1 + (1 - @c % 2) rows only) as Altimeter
809 ) as Altimeter
810 ,(
811 select avg(1.0 * [StationPressure])
812 from (
813 select StationPressure
814 from #station_average
815 where #station_average.[Year] = @year and
816 #station_average.[Month] = @month and
817 #station_average.[Day] = @day
818 order by [StationPressure]
819 offset (@c - 1) / 2 rows
820 fetch next 1 + (1 - @c % 2) rows only) as StationPressure
821 ) as StationPressure
822 ,(
823 select avg(1.0 * [MaxTemp])
824 from (
825 select MaxTemp
826 from #station_average
827 where #station_average.[Year] = @year and
828 #station_average.[Month] = @month and
829 #station_average.[Day] = @day
830 order by [MaxTemp]
831 offset (@c - 1) / 2 rows
832 fetch next 1 + (1 - @c % 2) rows only) as MaxTemp
833 ) as MaxTemp
834 ,(
835 select avg(1.0 * [MinTemp])
836 from (
837 select MinTemp
838 from #station_average
839 where #station_average.[Year] = @year and
840 #station_average.[Month] = @month and
841 #station_average.[Day] = @day
842 order by [MinTemp]
843 offset (@c - 1) / 2 rows
844 fetch next 1 + (1 - @c % 2) rows only) as MinTemp
845 ) as MinTemp
846 ,(select sum(
847 Precip1Hour + Precip6Hour + Precip24Hour + PrecipOther
848 )
849 from #station_average
850 where
851 [Year] = @year and
852 [Month] = @month and
853 [Day] = @day)
854 as TotalPrecip
855 ,(
856 select avg(1.0 * [SnowDepth])
857 from (
858 select SnowDepth
859 from #station_average
860 where #station_average.[Year] = @year and
861 #station_average.[Month] = @month and
862 #station_average.[Day] = @day
863 order by [SnowDepth]
864 offset (@c - 1) / 2 rows
865 fetch next 1 + (1 - @c % 2) rows only) as SnowDepth
866 ) as SnowDepth
867 from #station_average
868 where
869 #station_average.[Year] = @year and
870 #station_average.[Month] = @month and
871 #station_average.[Day] = @day
872
873 set @day = @day + 1
874
875 end
876
877 insert into station_monthly_average
878 select Station
879 ,Year
880 ,Month
881 ,(select min(Sunrise)
882 from #station_daily
883 where #station_daily.[Station] = @stationID and
884 #station_daily.[Year] = @year and
885 #station_daily.[Month] = @month) as MinSunrise
886 ,(select max(Sunrise)
887 from #station_daily
888 where #station_daily.[Station] = @stationID and
889 #station_daily.[Year] = @year and
890 #station_daily.[Month] = @month) as MaxSunrise
891 ,(select avg(Sunrise)
892 from #station_daily
893 where #station_daily.[Station] = @stationID and
894 #station_daily.[Year] = @year and
895 #station_daily.[Month] = @month) as AvgSunrise
896 ,(select min(Sunset)
897 from #station_daily
898 where #station_daily.[Station] = @stationID and
899 #station_daily.[Year] = @year and
900 #station_daily.[Month] = @month) as MinSunset
901 ,(select max(Sunset)
902 from #station_daily
903 where #station_daily.[Station] = @stationID and
904 #station_daily.[Year] = @year and
905 #station_daily.[Month] = @month) as MaxSunset
906 ,(select avg(Sunset)
907 from #station_daily
908 where #station_daily.[Station] = @stationID and
909 #station_daily.[Year] = @year and
910 #station_daily.[Month] = @month) as AvgSunset
911 ,avg(WindDirection)
912 ,avg(WindSpeed)
913 ,avg(WindGust)
914 ,avg(CloudCeiling)
915 ,avg(Visibility)
916 ,avg(Temperature)
917 ,avg(DewPoint)
918 ,avg(SeaLevelPressure)
919 ,avg(Altimeter)
920 ,avg(StationPressure)
921 ,avg(MaxTemp)
922 ,avg(MinTemp)
923 ,(select max(
924 Temperature)
925 from #station_average
926 where
927 #station_average.[Year] = @year and
928 #station_average.[Month] = @month
929 and
930 #station_average.[Time] between @sunrise and @sunset) as DayMaxTemp
931 ,(select min(
932 Temperature)
933 from #station_average
934 where
935 #station_average.[Year] = @year and
936 #station_average.[Month] = @month
937 and
938 #station_average.[Time] between @sunrise and @sunset) as DayMinTemp
939 ,(select avg(
940 Temperature)
941 from #station_average
942 where
943 #station_average.[Year] = @year and
944 #station_average.[Month] = @month
945 and
946 #station_average.[Time] between @sunrise and @sunset) as DayAvgTemp
947 ,(select max(
948 Temperature)
949 from #station_average
950 where
951 #station_average.[Year] = @year and
952 #station_average.[Month] = @month
953 and
954 #station_average.[Time] < @sunrise or
955 #station_average.[Time] > @sunset) as NightMaxTemp
956 ,(select min(
957 Temperature)
958 from #station_average
959 where
960 #station_average.[Year] = @year and
961 #station_average.[Month] = @month
962 and
963 #station_average.[Time] < @sunrise or
964 #station_average.[Time] > @sunset) as NightMinTemp
965 ,(select avg(
966 Temperature)
967 from #station_average
968 where
969 #station_average.[Year] = @year and
970 #station_average.[Month] = @month
971 and
972 #station_average.[Time] < @sunrise or
973 #station_average.[Time] > @sunset) as NightAvgTemp
974 ,(select avg(PrecipTotal)
975 from #station_daily
976 where #station_daily.[Station] = @stationID and
977 #station_daily.[Year] = @year and
978 #station_daily.[Month] = @month)
979 as Precip
980 ,(
981 select count(*)
982 from #station_daily
983 where PrecipTrace = 1 and
984 #station_daily.[Station] = @stationID and
985 #station_daily.[Year] = @year and
986 #station_daily.[Month] = @month)
987 as PrecipTraceDays
988 ,avg(SnowDepth)
989 ,(
990 select count(*)
991 from #station_daily
992 where SnowTrace = 1 and
993 #station_daily.[Station] = @stationID and
994 #station_daily.[Year] = @year and
995 #station_daily.[Month] = @month)
996 as SnowTraceDays
997 from #station_average
998 where
999 #station_average.[Year] = @year and
1000 #station_average.[month] = @month
1001
1002 declare @d bigint = (select count(*)
1003 from #station_average
1004 where #station_average.[Year] = @year and
1005 #station_average.[Month] = @month)
1006
1007
1008 insert into station_monthly_median
1009 select Station
1010 ,Year
1011 ,Month
1012 ,(
1013 select avg(1.0 * [WindDirection])
1014 from (
1015 select WindDirection
1016 from #station_average
1017 where #station_average.[Year] = @year and
1018 #station_average.[Month] = @month
1019 order by [WindDirection]
1020 offset (@d - 1) / 2 rows
1021 fetch next 1 + (1 - @d % 2) rows only) x
1022 ) as WindDirection
1023 ,(
1024 select avg(1.0 * [WindSpeed])
1025 from (
1026 select WindSpeed
1027 from #station_average
1028 where #station_average.[Year] = @year and
1029 #station_average.[Month] = @month
1030 order by [WindSpeed]
1031 offset (@d - 1) / 2 rows
1032 fetch next 1 + (1 - @d % 2) rows only) x
1033 ) as WindSpeed
1034 ,(
1035 select avg(1.0 * [WindGust])
1036 from (
1037 select WindGust
1038 from #station_average
1039 where #station_average.[Year] = @year and
1040 #station_average.[Month] = @month
1041 order by [WindGust]
1042 offset (@d - 1) / 2 rows
1043 fetch next 1 + (1 - @d % 2) rows only) x
1044 ) as WindGust
1045 ,(
1046 select avg(1.0 * [CloudCeiling])
1047 from (
1048 select CloudCeiling
1049 from #station_average
1050 where #station_average.[Year] = @year and
1051 #station_average.[Month] = @month
1052 order by [CloudCeiling]
1053 offset (@d - 1) / 2 rows
1054 fetch next 1 + (1 - @d % 2) rows only) x
1055 ) as CloudCeiling
1056 ,(
1057 select avg(1.0 * [Visibility])
1058 from (
1059 select Visibility
1060 from #station_average
1061 where #station_average.[Year] = @year and
1062 #station_average.[Month] = @month
1063 order by [Visibility]
1064 offset (@d - 1) / 2 rows
1065 fetch next 1 + (1 - @d % 2) rows only) x
1066 ) as Visibility
1067 ,(
1068 select avg(1.0 * [Temperature])
1069 from (
1070 select Temperature
1071 from #station_average
1072 where #station_average.[Year] = @year and
1073 #station_average.[Month] = @month
1074 order by [Temperature]
1075 offset (@d - 1) / 2 rows
1076 fetch next 1 + (1 - @d % 2) rows only) x
1077 ) as Temperature
1078 ,(
1079 select avg(1.0 * [DewPoint])
1080 from (
1081 select DewPoint
1082 from #station_average
1083 where #station_average.[Year] = @year and
1084 #station_average.[Month] = @month
1085 order by [DewPoint]
1086 offset (@d - 1) / 2 rows
1087 fetch next 1 + (1 - @d % 2) rows only) x
1088 ) as Dewpoint
1089 ,(
1090 select avg(1.0 * [SeaLevelPressure])
1091 from (
1092 select SeaLevelPressure
1093 from #station_average
1094 where #station_average.[Year] = @year and
1095 #station_average.[Month] = @month
1096 order by [SeaLevelPressure]
1097 offset (@d - 1) / 2 rows
1098 fetch next 1 + (1 - @d % 2) rows only) x
1099 ) as SeaLevelPressure
1100 ,(
1101 select avg(1.0 * [Altimeter])
1102 from (
1103 select Altimeter
1104 from #station_average
1105 where #station_average.[Year] = @year and
1106 #station_average.[Month] = @month
1107 order by [Altimeter]
1108 offset (@d - 1) / 2 rows
1109 fetch next 1 + (1 - @d % 2) rows only) x
1110 ) as Altimeter
1111 ,(
1112 select avg(1.0 * [StationPressure])
1113 from (
1114 select StationPressure
1115 from #station_average
1116 where #station_average.[Year] = @year and
1117 #station_average.[Month] = @month
1118 order by [StationPressure]
1119 offset (@d - 1) / 2 rows
1120 fetch next 1 + (1 - @d % 2) rows only) x
1121 ) as StationPressure
1122 ,(
1123 select avg(1.0 * [MaxTemp])
1124 from (
1125 select MaxTemp
1126 from #station_average
1127 where #station_average.[Year] = @year and
1128 #station_average.[Month] = @month
1129 order by [MaxTemp]
1130 offset (@d - 1) / 2 rows
1131 fetch next 1 + (1 - @d % 2) rows only) x
1132 ) as MaxTemp
1133 ,(
1134 select avg(1.0 * [MinTemp])
1135 from (
1136 select MinTemp
1137 from #station_average
1138 where #station_average.[Year] = @year and
1139 #station_average.[Month] = @month
1140 order by [MinTemp]
1141 offset (@d - 1) / 2 rows
1142 fetch next 1 + (1 - @d % 2) rows only) x
1143 ) as MinTemp
1144 ,(
1145 select avg(1.0 * [PrecipTotal])
1146 from (
1147 select PrecipTotal
1148 from #station_daily
1149 where #station_daily.[Year] = @year and
1150 #station_daily.[Month] = @month
1151 order by [PrecipTotal]
1152 offset (@d - 1) / 2 rows
1153 fetch next 1 + (1 - @d % 2) rows only) x
1154 ) as Precip
1155 ,(
1156 select avg(1.0 * [SnowDepth])
1157 from (
1158 select SnowDepth
1159 from #station_average
1160 where #station_average.[Year] = @year and
1161 #station_average.[Month] = @month
1162 order by [SnowDepth]
1163 offset (@d - 1) / 2 rows
1164 fetch next 1 + (1 - @d % 2) rows only) x
1165 ) as SnowDepth
1166 from #station_average
1167 where
1168 #station_average.[Year] = @year and
1169 #station_average.[Month] = @month
1170
1171 set @month = @month + 1
1172
1173 end
1174
1175 insert into station_annual_average
1176 select [Station]
1177 ,[Year]
1178 ,(select min(Sunrise)
1179 from #station_daily
1180 where #station_daily.[Station] = @stationID and
1181 #station_daily.[Year] = @year) as MinSunrise
1182 ,(select max(Sunrise)
1183 from #station_daily
1184 where #station_daily.[Station] = @stationID and
1185 #station_daily.[Year] = @year) as MaxSunrise
1186 ,(select avg(Sunrise)
1187 from #station_daily
1188 where #station_daily.[Station] = @stationID and
1189 #station_daily.[Year] = @year) as AvgSunrise
1190 ,(select min(Sunset)
1191 from #station_daily
1192 where #station_daily.[Station] = @stationID and
1193 #station_daily.[Year] = @year) as MinSunset
1194 ,(select max(Sunset)
1195 from #station_daily
1196 where #station_daily.[Station] = @stationID and
1197 #station_daily.[Year] = @year) as MaxSunset
1198 ,(select avg(Sunset)
1199 from #station_daily
1200 where #station_daily.[Station] = @stationID and
1201 #station_daily.[Year] = @year) as AvgSunset
1202 ,avg(WindDirection)
1203 ,avg(WindSpeed)
1204 ,avg(WindGust)
1205 ,avg(CloudCeiling)
1206 ,avg(Visibility)
1207 ,avg(Temperature)
1208 ,avg(DewPoint)
1209 ,avg(SeaLevelPressure)
1210 ,avg(Altimeter)
1211 ,avg(StationPressure)
1212 ,avg(MaxTemp)
1213 ,avg(MinTemp)
1214 ,(select max(
1215 Temperature)
1216 from #station_average
1217 where
1218 #station_average.[Year] = @year
1219 and
1220 #station_average.[Time] between @sunrise and @sunset) as DayMaxTemp
1221 ,(select min(
1222 Temperature)
1223 from #station_average
1224 where
1225 #station_average.[Year] = @year
1226 and
1227 #station_average.[Time] between @sunrise and @sunset) as DayMinTemp
1228 ,(select avg(
1229 Temperature)
1230 from #station_average
1231 where
1232 #station_average.[Year] = @year
1233 and
1234 #station_average.[Time] between @sunrise and @sunset) as DayAvgTemp
1235 ,(select max(
1236 Temperature)
1237 from #station_average
1238 where
1239 #station_average.[Year] = @year
1240 and
1241 #station_average.[Time] < @sunrise or
1242 #station_average.[Time] > @sunset) as NightMaxTemp
1243 ,(select min(
1244 Temperature)
1245 from #station_average
1246 where
1247 #station_average.[Year] = @year
1248 and
1249 #station_average.[Time] < @sunrise or
1250 #station_average.[Time] > @sunset) as NightMinTemp
1251 ,(select avg(
1252 Temperature)
1253 from #station_average
1254 where
1255 #station_average.[Year] = @year
1256 and
1257 #station_average.[Time] < @sunrise or
1258 #station_average.[Time] > @sunset) as NightAvgTemp
1259 ,(select avg(PrecipTotal)
1260 from #station_daily
1261 where #station_daily.Station = @stationID and
1262 #station_daily.[Year] = @year)
1263 as Precip
1264 ,(
1265 select count(*)
1266 from #station_daily
1267 where PrecipTrace = 1 and
1268 #station_daily.[Station] = @stationID and
1269 #station_daily.[Year] = @year and
1270 #station_daily.[Month] = @month)
1271 as PrecipTraceDays
1272 ,avg(SnowDepth)
1273 ,(
1274 select count(*)
1275 from #station_daily
1276 where SnowTrace = 1 and
1277 #station_daily.[Station] = @stationID and
1278 #station_daily.[Year] = @year and
1279 #station_daily.[Month] = @month)
1280 as SnowTraceDays
1281 from #station_average
1282 where
1283 year = @year
1284
1285 declare @e bigint = (select count(*)
1286 from #station_average
1287 where #station_average.[Year] = @year)
1288
1289
1290 insert into station_annual_median
1291 select Station
1292 ,Year
1293 ,(
1294 select avg(1.0 * [WindDirection])
1295 from (
1296 select WindDirection
1297 from #station_average
1298 where #station_average.[Year] = @year
1299 order by [WindDirection]
1300 offset (@e - 1) / 2 rows
1301 fetch next 1 + (1 - @e % 2) rows only) x
1302 ) as WindDirection
1303 ,(
1304 select avg(1.0 * [WindSpeed])
1305 from (
1306 select WindSpeed
1307 from #station_average
1308 where #station_average.[Year] = @year
1309 order by [WindSpeed]
1310 offset (@e - 1) / 2 rows
1311 fetch next 1 + (1 - @e % 2) rows only) x
1312 ) as WindSpeed
1313 ,(
1314 select avg(1.0 * [WindGust])
1315 from (
1316 select WindGust
1317 from #station_average
1318 where #station_average.[Year] = @year
1319 order by [WindGust]
1320 offset (@e - 1) / 2 rows
1321 fetch next 1 + (1 - @e % 2) rows only) x
1322 ) as WindGust
1323 ,(
1324 select avg(1.0 * [CloudCeiling])
1325 from (
1326 select CloudCeiling
1327 from #station_average
1328 where #station_average.[Year] = @year
1329 order by [CloudCeiling]
1330 offset (@e - 1) / 2 rows
1331 fetch next 1 + (1 - @e % 2) rows only) x
1332 ) as CloudCeiling
1333 ,(
1334 select avg(1.0 * [Visibility])
1335 from (
1336 select Visibility
1337 from #station_average
1338 where #station_average.[Year] = @year
1339 order by [Visibility]
1340 offset (@e - 1) / 2 rows
1341 fetch next 1 + (1 - @e % 2) rows only) x
1342 ) as Visibility
1343 ,(
1344 select avg(1.0 * [Temperature])
1345 from (
1346 select Temperature
1347 from #station_average
1348 where #station_average.[Year] = @year
1349 order by [Temperature]
1350 offset (@e - 1) / 2 rows
1351 fetch next 1 + (1 - @e % 2) rows only) x
1352 ) as Temperature
1353 ,(
1354 select avg(1.0 * [DewPoint])
1355 from (
1356 select DewPoint
1357 from #station_average
1358 where #station_average.[Year] = @year
1359 order by [DewPoint]
1360 offset (@e - 1) / 2 rows
1361 fetch next 1 + (1 - @e % 2) rows only) x
1362 ) as Dewpoint
1363 ,(
1364 select avg(1.0 * [SeaLevelPressure])
1365 from (
1366 select SeaLevelPressure
1367 from #station_average
1368 where #station_average.[Year] = @year
1369 order by [SeaLevelPressure]
1370 offset (@e - 1) / 2 rows
1371 fetch next 1 + (1 - @e % 2) rows only) x
1372 ) as SeaLevelPressure
1373 ,(
1374 select avg(1.0 * [Altimeter])
1375 from (
1376 select Altimeter
1377 from #station_average
1378 where #station_average.[Year] = @year
1379 order by [Altimeter]
1380 offset (@e - 1) / 2 rows
1381 fetch next 1 + (1 - @e % 2) rows only) x
1382 ) as Altimeter
1383 ,(
1384 select avg(1.0 * [StationPressure])
1385 from (
1386 select StationPressure
1387 from #station_average
1388 where #station_average.[Year] = @year
1389 order by [StationPressure]
1390 offset (@e - 1) / 2 rows
1391 fetch next 1 + (1 - @e % 2) rows only) x
1392 ) as StationPressure
1393 ,(
1394 select avg(1.0 * [MaxTemp])
1395 from (
1396 select MaxTemp
1397 from #station_average
1398 where #station_average.[Year] = @year
1399 order by [MaxTemp]
1400 offset (@e - 1) / 2 rows
1401 fetch next 1 + (1 - @e % 2) rows only) x
1402 ) as MaxTemp
1403 ,(
1404 select avg(1.0 * [MinTemp])
1405 from (
1406 select MinTemp
1407 from #station_average
1408 where #station_average.[Year] = @year
1409 order by [MinTemp]
1410 offset (@e - 1) / 2 rows
1411 fetch next 1 + (1 - @e % 2) rows only) x
1412 ) as MinTemp
1413 ,(
1414 select avg(1.0 * [PrecipTotal])
1415 from (
1416 select PrecipTotal
1417 from #station_daily
1418 where #station_daily.[Year] = @year
1419 order by [PrecipTotal]
1420 offset (@e - 1) / 2 rows
1421 fetch next 1 + (1 - @e % 2) rows only) x
1422 ) as Precip
1423 ,(
1424 select avg(1.0 * [SnowDepth])
1425 from (
1426 select SnowDepth
1427 from #station_average
1428 where #station_average.[Year] = @year
1429 order by [SnowDepth]
1430 offset (@e - 1) / 2 rows
1431 fetch next 1 + (1 - @e % 2) rows only) x
1432 ) as SnowDepth
1433 from #station_average
1434 where
1435 #station_average.[Year] = @year
1436
1437 set @year = @year + 1
1438
1439 end
1440
1441 insert into station_overall_average
1442 select Station as Station
1443 ,(select min(Sunrise)
1444 from #station_daily) as MinSunrise
1445 ,(select max(Sunrise)
1446 from #station_daily) as MaxSunrise
1447 ,(select avg(Sunrise)
1448 from #station_daily) as AvgSunrise
1449 ,(select min(Sunset)
1450 from #station_daily) as MinSunset
1451 ,(select max(Sunset)
1452 from #station_daily) as MaxSunset
1453 ,(select avg(Sunset)
1454 from #station_daily) as AvgSunset
1455 ,avg(WindDirection) as WindDirection
1456 ,min(WindDirection) as MinWindDirection
1457 ,max(WindDirection) as MaxWindDirection
1458 ,avg(WindSpeed) as WindSpeed
1459 ,min(WindSpeed) as MinWindSpeed
1460 ,max(WindSpeed) as MaxWindSpeed
1461 ,avg(WindGust) as WindGust
1462 ,min(WindGust) as MinWindGust
1463 ,max(WindGust) as MaxWindGust
1464 ,avg(CloudCeiling) as CloudCeiling
1465 ,min(CloudCeiling) as MinCloudCeiling
1466 ,max(CloudCeiling) as MaxCloudCeiling
1467 ,avg(Visibility) as Visibility
1468 ,min(Visibility) as MinVisibility
1469 ,max(Visibility) as MaxVisibility
1470 ,avg(Temperature) as Temperature
1471 ,avg(DewPoint) as Dewpoint
1472 ,min(DewPoint) as MinDewPoint
1473 ,max(DewPoint) as MaxDewPoint
1474 ,avg(SeaLevelPressure) as SeaLevelPressure
1475 ,min(SeaLevelPressure) as MinSeaLevelPressure
1476 ,max(SeaLevelPressure) as MaxSeaLevelPressure
1477 ,avg(Altimeter) as Altimeter
1478 ,min(Altimeter) as MinAltimeter
1479 ,max(Altimeter) as MaxAltimeter
1480 ,avg(StationPressure) as StationPressure
1481 ,min(StationPressure) as MinStationPressure
1482 ,max(StationPressure) as MaxStationPressure
1483 ,max(Temperature) as MaxTemp
1484 ,min(Temperature) as MinTemp
1485 ,(select max(DayMaxTemp)
1486 from #station_daily) as DayMaxTemp
1487 ,(select min(DayMaxTemp)
1488 from #station_daily) as DayMinTemp
1489 ,(select avg(DayMaxTemp)
1490 from #station_daily) as DayAvgTemp
1491 ,(select max(NightMaxTemp)
1492 from #station_daily) as NightMaxTemp
1493 ,(select min(NightMaxTemp)
1494 from #station_daily) as NightMinTemp
1495 ,(select avg(NightMaxTemp)
1496 from #station_daily) as NightAvgTemp
1497 ,(select avg(PrecipTotal)
1498 from #station_daily) as Precip
1499 ,(select min(PrecipTotal)
1500 from #station_daily) as MinPrecip
1501 ,(select max(PrecipTotal)
1502 from #station_daily) as MaxPrecip
1503 ,(select avg(SnowDepth)
1504 from #station_daily) as SnowDepth
1505 ,(select min(SnowDepth)
1506 from #station_daily) as MinSnowDepth
1507 ,(select max(SnowDepth)
1508 from #station_daily) as MaxSnowDepth
1509 from #station_average
1510 where #station_average.Station = @stationID
1511
1512 declare @bydayMonth int
1513 declare @bydayMonthMax int
1514
1515 set @bydayMonth = (select min(Month)
1516 from #station_average
1517 where #station_average.Station = @stationID)
1518
1519 set @bydayMonthMax = (select max(Month)
1520 from #station_average
1521 where #station_average.Station = @stationID)
1522
1523 while @bydayMonth <= @bydayMonthMax
1524
1525 begin
1526
1527 declare @bydayDay int
1528 declare @bydayDayMax int
1529
1530 set @bydayDay = (select min(Day)
1531 from #station_average
1532 where #station_average.Station = @stationID and
1533 #station_average.Month = @bydayMonth)
1534
1535 set @bydayDayMax = (select max(Day)
1536 from #station_average
1537 where #station_average.Station = @stationID and
1538 #station_average.Month = @bydayMonth)
1539
1540 while @bydayDay <= @bydayDayMax
1541
1542 begin
1543
1544 insert into station_averages_by_day
1545 select Station as Station
1546 ,@bydayMonth as Month
1547 ,@bydayDay as Day
1548 ,(select min(Sunrise)
1549 from #station_daily
1550 where #station_daily.Month = @bydayMonth and
1551 #station_daily.Day = @bydayDay) as MinSunrise
1552 ,(select max(Sunrise)
1553 from #station_daily
1554 where #station_daily.Month = @bydayMonth and
1555 #station_daily.Day = @bydayDay) as MaxSunrise
1556 ,(select avg(Sunrise)
1557 from #station_daily
1558 where #station_daily.Month = @bydayMonth and
1559 #station_daily.Day = @bydayDay) as AvgSunrise
1560 ,(select min(Sunset)
1561 from #station_daily
1562 where #station_daily.Month = @bydayMonth and
1563 #station_daily.Day = @bydayDay) as MinSunset
1564 ,(select max(Sunset)
1565 from #station_daily
1566 where #station_daily.Month = @bydayMonth and
1567 #station_daily.Day = @bydayDay) as MaxSunset
1568 ,(select avg(Sunset)
1569 from #station_daily
1570 where #station_daily.Month = @bydayMonth and
1571 #station_daily.Day = @bydayDay) as AvgSunset
1572 ,avg(WindDirection) as WindDirection
1573 ,min(WindDirection) as MinWindDirection
1574 ,max(WindDirection) as MaxWindDirection
1575 ,avg(WindSpeed) as WindSpeed
1576 ,min(WindSpeed) as MinWindSpeed
1577 ,max(WindSpeed) as MaxWindSpeed
1578 ,avg(WindGust) as WindGust
1579 ,min(WindGust) as MinWindGust
1580 ,max(WindGust) as MaxWindGust
1581 ,avg(CloudCeiling) as CloudCeiling
1582 ,min(CloudCeiling) as MinCloudCeiling
1583 ,max(CloudCeiling) as MaxCloudCeiling
1584 ,avg(Visibility) as Visibility
1585 ,min(Visibility) as MinVisibility
1586 ,max(Visibility) as MaxVisibility
1587 ,avg(Temperature) as Temperature
1588 ,avg(DewPoint) as Dewpoint
1589 ,min(DewPoint) as MinDewPoint
1590 ,max(DewPoint) as MaxDewPoint
1591 ,avg(SeaLevelPressure) as SeaLevelPressure
1592 ,min(SeaLevelPressure) as MinSeaLevelPressure
1593 ,max(SeaLevelPressure) as MaxSeaLevelPressure
1594 ,avg(Altimeter) as Altimeter
1595 ,min(Altimeter) as MinAltimeter
1596 ,max(Altimeter) as MaxAltimeter
1597 ,avg(StationPressure) as StationPressure
1598 ,min(StationPressure) as MinStationPressure
1599 ,max(StationPressure) as MaxStationPressure
1600 ,max(Temperature) as MaxTemp
1601 ,min(Temperature) as MinTemp
1602 ,(select max(DayMaxTemp)
1603 from #station_daily
1604 where #station_daily.Month = @bydayMonth and
1605 #station_daily.Day = @bydayDay) as DayMaxTemp
1606 ,(select min(DayMaxTemp)
1607 from #station_daily
1608 where #station_daily.Month = @bydayMonth and
1609 #station_daily.Day = @bydayDay) as DayMinTemp
1610 ,(select avg(DayMaxTemp)
1611 from #station_daily
1612 where #station_daily.Month = @bydayMonth and
1613 #station_daily.Day = @bydayDay) as DayAvgTemp
1614 ,(select max(NightMaxTemp)
1615 from #station_daily
1616 where #station_daily.Month = @bydayMonth and
1617 #station_daily.Day = @bydayDay) as NightMaxTemp
1618 ,(select min(NightMaxTemp)
1619 from #station_daily
1620 where #station_daily.Month = @bydayMonth and
1621 #station_daily.Day = @bydayDay) as NightMinTemp
1622 ,(select avg(NightMaxTemp)
1623 from #station_daily
1624 where #station_daily.Month = @bydayMonth and
1625 #station_daily.Day = @bydayDay) as NightAvgTemp
1626 ,(select avg(PrecipTotal)
1627 from #station_daily
1628 where #station_daily.Month = @bydayMonth and
1629 #station_daily.Day = @bydayDay) as Precip
1630 ,(select min(PrecipTotal)
1631 from #station_daily
1632 where #station_daily.Month = @bydayMonth and
1633 #station_daily.Day = @bydayDay) as MinPrecip
1634 ,(select max(PrecipTotal)
1635 from #station_daily
1636 where #station_daily.Month = @bydayMonth and
1637 #station_daily.Day = @bydayDay) as MaxPrecip
1638 ,(select avg(SnowDepth)
1639 from #station_daily
1640 where #station_daily.Month = @bydayMonth and
1641 #station_daily.Day = @bydayDay) as SnowDepth
1642 ,(select min(SnowDepth)
1643 from #station_daily
1644 where #station_daily.Month = @bydayMonth and
1645 #station_daily.Day = @bydayDay) as MinSnowDepth
1646 ,(select max(SnowDepth)
1647 from #station_daily
1648 where #station_daily.Month = @bydayMonth and
1649 #station_daily.Day = @bydayDay) as MaxSnowDepth
1650 from #station_average
1651 where #station_average.Station = @stationID and
1652 #station_average.Month = @bydayMonth and
1653 #station_average.Day = @bydayDay
1654
1655
1656 set @bydayDay = @bydayDay + 1
1657
1658 end
1659
1660 insert into station_averages_by_month
1661 select Station as Station
1662 ,@bydayMonth as Month
1663 ,(select min(Sunrise)
1664 from #station_daily
1665 where #station_daily.Month = @bydayMonth) as MinSunrise
1666 ,(select max(Sunrise)
1667 from #station_daily
1668 where #station_daily.Month = @bydayMonth) as MaxSunrise
1669 ,(select avg(Sunrise)
1670 from #station_daily
1671 where #station_daily.Month = @bydayMonth) as AvgSunrise
1672 ,(select min(Sunset)
1673 from #station_daily
1674 where #station_daily.Month = @bydayMonth) as MinSunset
1675 ,(select max(Sunset)
1676 from #station_daily
1677 where #station_daily.Month = @bydayMonth) as MaxSunset
1678 ,(select avg(Sunset)
1679 from #station_daily
1680 where #station_daily.Month = @bydayMonth) as AvgSunset
1681 ,avg(WindDirection) as WindDirection
1682 ,min(WindDirection) as MinWindDirection
1683 ,max(WindDirection) as MaxWindDirection
1684 ,avg(WindSpeed) as WindSpeed
1685 ,min(WindSpeed) as MinWindSpeed
1686 ,max(WindSpeed) as MaxWindSpeed
1687 ,avg(WindGust) as WindGust
1688 ,min(WindGust) as MinWindGust
1689 ,max(WindGust) as MaxWindGust
1690 ,avg(CloudCeiling) as CloudCeiling
1691 ,min(CloudCeiling) as MinCloudCeiling
1692 ,max(CloudCeiling) as MaxCloudCeiling
1693 ,avg(Visibility) as Visibility
1694 ,min(Visibility) as MinVisibility
1695 ,max(Visibility) as MaxVisibility
1696 ,avg(Temperature) as Temperature
1697 ,avg(DewPoint) as Dewpoint
1698 ,min(DewPoint) as MinDewPoint
1699 ,max(DewPoint) as MaxDewPoint
1700 ,avg(SeaLevelPressure) as SeaLevelPressure
1701 ,min(SeaLevelPressure) as MinSeaLevelPressure
1702 ,max(SeaLevelPressure) as MaxSeaLevelPressure
1703 ,avg(Altimeter) as Altimeter
1704 ,min(Altimeter) as MinAltimeter
1705 ,max(Altimeter) as MaxAltimeter
1706 ,avg(StationPressure) as StationPressure
1707 ,min(StationPressure) as MinStationPressure
1708 ,max(StationPressure) as MaxStationPressure
1709 ,max(Temperature) as MaxTemp
1710 ,min(Temperature) as MinTemp
1711 ,(select max(DayMaxTemp)
1712 from #station_daily
1713 where #station_daily.Month = @bydayMonth) as DayMaxTemp
1714 ,(select min(DayMaxTemp)
1715 from #station_daily
1716 where #station_daily.Month = @bydayMonth) as DayMinTemp
1717 ,(select avg(DayMaxTemp)
1718 from #station_daily
1719 where #station_daily.Month = @bydayMonth) as DayAvgTemp
1720 ,(select max(NightMaxTemp)
1721 from #station_daily
1722 where #station_daily.Month = @bydayMonth) as NightMaxTemp
1723 ,(select min(NightMaxTemp)
1724 from #station_daily
1725 where #station_daily.Month = @bydayMonth) as NightMinTemp
1726 ,(select avg(NightMaxTemp)
1727 from #station_daily
1728 where #station_daily.Month = @bydayMonth) as NightAvgTemp
1729 ,(select avg(PrecipTotal)
1730 from #station_daily
1731 where #station_daily.Month = @bydayMonth) as Precip
1732 ,(select min(PrecipTotal)
1733 from #station_daily
1734 where #station_daily.Month = @bydayMonth) as MinPrecip
1735 ,(select max(PrecipTotal)
1736 from #station_daily
1737 where #station_daily.Month = @bydayMonth) as MaxPrecip
1738 ,(select avg(SnowDepth)
1739 from #station_daily
1740 where #station_daily.Month = @bydayMonth) as SnowDepth
1741 ,(select min(SnowDepth)
1742 from #station_daily
1743 where #station_daily.Month = @bydayMonth) as MinSnowDepth
1744 ,(select max(SnowDepth)
1745 from #station_daily
1746 where #station_daily.Month = @bydayMonth) as MaxSnowDepth
1747 from #station_average
1748 where #station_average.Station = @stationID and
1749 #station_average.Month = @bydayMonth
1750
1751 set @bydayMonth = @bydayMonth + 1
1752
1753 end
1754
1755 set @stationID = @stationID + 1
1756
1757 end
1758
1759 else
1760
1761 begin
1762
1763 set @stationID = @stationID + 1
1764
1765 end
1766
1767end