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