· 8 years ago · Nov 25, 2017, 05:06 AM
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_median', N'U') is null
163create table station_annual_median(
164 Station int
165 ,[Year] char(4)
166 ,WindDirection smallint
167 ,WindSpeed smallint
168 ,WindGust smallint
169 ,CloudCeiling smallint
170 ,Visibility int
171 ,Temperature decimal(5,2)
172 ,DewPoint decimal(8,2)
173 ,SeaLevelPressure decimal(6,2)
174 ,Altimeter decimal(6,2)
175 ,StationPressure decimal(6,2)
176 ,MaxTemp smallint
177 ,MinTemp smallint
178 ,DayMaxTemp smallint
179 ,DayMinTemp smallint
180 ,DayMedianTemp smallint
181 ,NightMaxTemp smallint
182 ,NightMinTemp smallint
183 ,NightMedianTemp smallint
184 ,Precip decimal(6,2)
185 ,SnowDepth smallint
186 ,foreign key (Station) references station_data(StationIncrementalID)
187 ) on WeatherData_PS (Station)
188
189if object_id('tmpdb..station_average') is null
190create table #station_average(
191 Station int
192 ,[Year] char(4)
193 ,[Month] char(2)
194 ,[Day] char(2)
195 ,[Time] char(4)
196 ,WindDirection smallint
197 ,WindSpeed smallint
198 ,WindGust smallint
199 ,CloudCeiling smallint
200 ,Visibility int
201 ,Temperature decimal(5,2)
202 ,DewPoint decimal(8,2)
203 ,SeaLevelPressure decimal(6,2)
204 ,Altimeter decimal(6,2)
205 ,StationPressure decimal(6,2)
206 ,MaxTemp smallint
207 ,MinTemp smallint
208 ,Precip1Hour decimal(6,2)
209 ,Precip6Hour decimal(6,2)
210 ,Precip24Hour decimal(6,2)
211 ,PrecipOther decimal(6,2)
212 ,Trace bit
213 ,SnowDepth smallint
214 ,SnowTrace bit)
215
216if object_id('tmpdb..station_daily') is null
217create table #station_daily(
218 Station int
219 ,[Year] char(4)
220 ,[Month] char(2)
221 ,[Day] char(2)
222 ,Sunrise char(4)
223 ,Sunset char(4)
224 ,WindDirection smallint
225 ,WindSpeed smallint
226 ,WindGust smallint
227 ,CloudCeiling smallint
228 ,Visibility int
229 ,Temperature decimal(5,2)
230 ,DewPoint decimal(8,2)
231 ,SeaLevelPressure decimal(6,2)
232 ,Altimeter decimal(6,2)
233 ,StationPressure decimal(6,2)
234 ,MaxTemp smallint
235 ,MinTemp smallint
236 ,DayMaxTemp smallint
237 ,DayMinTemp smallint
238 ,DayAvgTemp smallint
239 ,NightMaxTemp smallint
240 ,NightMinTemp smallint
241 ,NightAvgTemp smallint
242 ,PrecipTotal decimal(6,2)
243 ,PrecipTrace bit
244 ,SnowDepth smallint
245 ,SnowTrace bit)
246
247if object_id('tmpdb..station_sunrise') is null
248create table #station_sunrise(
249 Sunrise datetime)
250
251if object_id('tmpdb..station_sunset') is null
252create table #station_sunset(
253 Sunset datetime)
254
255go
256
257use NOAA
258
259declare @stationID int
260declare @stationMaxRows bigint
261
262set @stationMaxRows = (
263 select max(StationIncrementalID)
264 from station_data)
265
266set @stationID = 1
267
268while @stationID <= @stationMaxRows
269
270begin
271
272 if exists (select Station
273 from weather_data
274 where weather_data.Station = @stationID)
275
276 begin
277 declare @Year char(4)
278 declare @MaxYear char(4)
279
280 delete from #station_average
281 delete from #station_daily
282
283 insert into #station_average
284 select Station
285 ,[Year]
286 ,[Month]
287 ,[Day]
288 ,[Time]
289 ,WindDirection
290 ,WindSpeed
291 ,WindGust
292 ,CloudCeiling
293 ,Visibility
294 ,Temperature
295 ,DewPoint
296 ,SeaLevelPressure
297 ,Altimeter
298 ,StationPressure
299 ,MaxTemp
300 ,MinTemp
301 ,Precip1Hour
302 ,Precip6Hour
303 ,Precip24Hour
304 ,PrecipOther
305 ,(
306 case when [1HourTrace] is not null or
307 [6HourTrace] is not null or
308 [24HourTrace] is not null or
309 [OtherTrace] is not null
310 then 1
311 else null
312 ) as Trace
313 ,SnowDepth
314 ,(
315 case when [SnowTrace] is not null
316 then 1
317 else null
318 ) as SnowTrace
319 from weather_data
320 where
321 weather_data.Station = @stationID
322
323 set @Year = (
324 select min([Year])
325 from #station_average)
326
327 set @MaxYear = (
328 select max([Year])
329 from #station_average)
330
331 while @year <= @MaxYear
332
333 begin
334
335 declare @month char(2)
336
337 set @month = 01
338
339 while @month <= 12
340
341 begin
342
343 declare @day char(2)
344
345 set @day = 01
346
347 while @day <= 31
348
349 begin
350
351 delete from #station_sunrise
352 delete from #station_sunset
353
354 declare @date varchar(11)
355 declare @offset int
356 declare @lat real
357 declare @long real
358
359 set @date = concat(@month,'/',@day,'/',@year)
360 set @offset = dbo.udf_getUTCOffSet()
361 set @lat = (select Latitude
362 from station_data
363 where station_data.StationIncrementalID = @stationID)
364 set @long = (select Longitude
365 from station_data
366 where station_data.StationIncrementalID = @stationID)
367
368 insert into #station_sunrise
369 exec DetermineSunrise
370 @date
371 ,@lat
372 ,@long
373 ,@offset
374
375 insert into #station_sunset
376 exec DetermineSunset
377 @date
378 ,@lat
379 ,@long
380 ,@offset
381
382 declare @sunrise char(4)
383 declare @sunset char(4)
384
385 set @sunrise = (
386 select replace(convert(char(4),(select top 1 from #station_sunrise),108), ':', '')
387
388 set @sunset = (
389 select replace(convert(char(4),(select top 1 from #station_sunset),108), ':', '')
390
391 insert into station_daily
392 select Station
393 ,Year
394 ,Month
395 ,Day
396 ,@sunrise as Sunrise
397 ,@sunset as Sunset
398 ,avg(WindDirection)
399 ,avg(WindSpeed)
400 ,avg(WindGust)
401 ,avg(CloudCeiling)
402 ,avg(Visibility)
403 ,avg(Temperature)
404 ,avg(DewPoint)
405 ,avg(SeaLevelPressure)
406 ,avg(SeaLevelPressuure)
407 ,avg(Altimeter)
408 ,avg(StationPressure)
409 ,avg(MaxTemp) as MaxTemp
410 ,avg(MinTemp) as MinTemp
411 ,(select max(
412 Temperature)
413 from #station_average
414 where
415 #station_average.[Year] = @year and
416 #station_average.[Month] = @month and
417 #station_average.[Day] = @day
418 and
419 where #station_average.[Time] between @sunrise and @sunset) as DayMaxTemp
420 ,(select min(
421 Temperature)
422 from #station_average
423 where
424 #station_average.[Year] = @year and
425 #station_average.[Month] = @month and
426 #station_average.[Day] = @day
427 and
428 where #station_average.[Time] between @sunrise and @sunset) as DayMinTemp
429 ,(select avg(
430 Temperature)
431 from #station_average
432 where
433 #station_average.[Year] = @year and
434 #station_average.[Month] = @month and
435 #station_average.[Day] = @day
436 and
437 where #station_average.[Time] between @sunrise and @sunset) as DayAvgTemp
438 ,(select max(
439 Temperature)
440 from #station_average
441 where
442 #station_average.[Year] = @year and
443 #station_average.[Month] = @month and
444 #station_average.[Day] = @day
445 and
446 where #station_average.[Time] between @sunrise and @sunset) as DayMaxTemp
447 ,(select min(
448 Temperature)
449 from #station_average
450 where
451 #station_average.[Year] = @year and
452 #station_average.[Month] = @month and
453 #station_average.[Day] = @day
454 and
455 where #station_average.[Time] between @sunrise and @sunset) as DayMinTemp
456 ,(select avg(
457 Temperature)
458 from #station_average
459 where
460 #station_average.[Year] = @year and
461 #station_average.[Month] = @month and
462 #station_average.[Day] = @day
463 and
464 where #station_average.[Time] between @sunrise and @sunset) as DayAvgTemp
465 ,(select sum(
466 Precip1Hour + Precip6Hour + Precip24Hour + PrecipOther
467 from #station_average
468 where
469 [Year] = @year and
470 [Month] = @month and
471 [Day] = @day)
472 as TotalPrecip
473 ,(
474 case when [Trace] is not null
475 then 1
476 else null
477 ) as PrecipTrace
478 ,avg(SnowDepth)
479 ,(
480 case when [SnowTrace] is not null
481 then 1
482 else null
483 ) as SnowTrace
484 from #station_average
485 where
486 #station_average.[Year] = @year and
487 #station_average.[Month] = @month and
488 #station_average.[Day] = @day
489
490 insert into #station_daily
491 select Station
492 ,Year
493 ,Month
494 ,Day
495 ,@sunrise as Sunrise
496 ,@sunset as Sunset
497 ,avg(WindDirection)
498 ,avg(WindSpeed)
499 ,avg(WindGust)
500 ,avg(CloudCeiling)
501 ,avg(Visibility)
502 ,avg(Temperature)
503 ,avg(DewPoint)
504 ,avg(SeaLevelPressure)
505 ,avg(SeaLevelPressuure)
506 ,avg(Altimeter)
507 ,avg(StationPressure)
508 ,avg(MaxTemp) as MaxTemp
509 ,avg(MinTemp) as MinTemp
510 ,(select max(
511 Temperature)
512 from #station_average
513 where
514 #station_average.[Year] = @year and
515 #station_average.[Month] = @month and
516 #station_average.[Day] = @day
517 and
518 where #station_average.[Time] between @sunrise and @sunset) as DayMaxTemp
519 ,(select min(
520 Temperature)
521 from #station_average
522 where
523 #station_average.[Year] = @year and
524 #station_average.[Month] = @month and
525 #station_average.[Day] = @day
526 and
527 where #station_average.[Time] between @sunrise and @sunset) as DayMinTemp
528 ,(select avg(
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 DayAvgTemp
537 ,(select max(
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 DayMaxTemp
546 ,(select min(
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 DayMinTemp
555 ,(select avg(
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] between @sunrise and @sunset) as DayAvgTemp
564 ,(select sum(
565 Precip1Hour + Precip6Hour + Precip24Hour + PrecipOther
566 from #station_average
567 where
568 [Year] = @year and
569 [Month] = @month and
570 [Day] = @day)
571 as TotalPrecip
572 ,(
573 case when [Trace] is not null
574 then 1
575 else null
576 ) as PrecipTrace
577 ,avg(SnowDepth)
578 ,(
579 case when [SnowTrace] is not null
580 then 1
581 else null
582 ) as SnowTrace
583 from #station_average
584 where
585 #station_average.[Year] = @year and
586 #station_average.[Month] = @month and
587 #station_average.[Day] = @day
588
589 insert into station_daily_median
590 select Station
591 ,Year
592 ,Month
593 ,Day
594 ,(declare @c bigint = (select count(*)
595 from #station_average
596 where #station_average.[Year] = @year and
597 where #station_average.[Month] = @month and
598 where #station_average.[Day] = @day)
599
600 select avg(1.0 * [WindDirection])
601 from (
602 select WindDirection
603 from #station_average
604 where #station_average.[Year] = @year and
605 where #station_average.[Month] = @month and
606 where #station_average.[Day] = @day)
607 order by [WindDirection]
608 offset (@c - 1) / 2 rows
609 fetch next 1 + (1 - @c % 2) rows only) as WindDirection
610 ,(declare @c bigint = (select count(*)
611 from #station_average
612 where #station_average.[Year] = @year and
613 where #station_average.[Month] = @month and
614 where #station_average.[Day] = @day)
615
616 select avg(1.0 * [WindSpeed])
617 from (
618 select WindSpeed
619 from #station_average
620 where #station_average.[Year] = @year and
621 where #station_average.[Month] = @month and
622 where #station_average.[Day] = @day)
623 order by [WindSpeed]
624 offset (@c - 1) / 2 rows
625 fetch next 1 + (1 - @c % 2) rows only) as WindSpeed
626 ,(declare @c bigint = (select count(*)
627 from #station_average
628 where #station_average.[Year] = @year and
629 where #station_average.[Month] = @month and
630 where #station_average.[Day] = @day)
631
632 select avg(1.0 * [WindGust])
633 from (
634 select WindGust
635 from #station_average
636 where #station_average.[Year] = @year and
637 where #station_average.[Month] = @month and
638 where #station_average.[Day] = @day)
639 order by [WindGust]
640 offset (@c - 1) / 2 rows
641 fetch next 1 + (1 - @c % 2) rows only) as WindGust
642 ,(declare @c bigint = (select count(*)
643 from #station_average
644 where #station_average.[Year] = @year and
645 where #station_average.[Month] = @month and
646 where #station_average.[Day] = @day)
647
648 select avg(1.0 * [CloudCeiling])
649 from (
650 select CloudCeiling
651 from #station_average
652 where #station_average.[Year] = @year and
653 where #station_average.[Month] = @month and
654 where #station_average.[Day] = @day)
655 order by [CloudCeiling]
656 offset (@c - 1) / 2 rows
657 fetch next 1 + (1 - @c % 2) rows only) as CloudCeiling
658 ,(declare @c bigint = (select count(*)
659 from #station_average
660 where #station_average.[Year] = @year and
661 where #station_average.[Month] = @month and
662 where #station_average.[Day] = @day)
663
664 select avg(1.0 * [Visibility])
665 from (
666 select Visibility
667 from #station_average
668 where #station_average.[Year] = @year and
669 where #station_average.[Month] = @month and
670 where #station_average.[Day] = @day)
671 order by [Visibility]
672 offset (@c - 1) / 2 rows
673 fetch next 1 + (1 - @c % 2) rows only) as Visibility
674 ,(declare @c bigint = (select count(*)
675 from #station_average
676 where #station_average.[Year] = @year and
677 where #station_average.[Month] = @month and
678 where #station_average.[Day] = @day)
679
680 select avg(1.0 * [Temperature])
681 from (
682 select Temperature
683 from #station_average
684 where #station_average.[Year] = @year and
685 where #station_average.[Month] = @month and
686 where #station_average.[Day] = @day)
687 order by [Temperature]
688 offset (@c - 1) / 2 rows
689 fetch next 1 + (1 - @c % 2) rows only) as Temperature
690 ,(declare @c bigint = (select count(*)
691 from #station_average
692 where #station_average.[Year] = @year and
693 where #station_average.[Month] = @month and
694 where #station_average.[Day] = @day)
695
696 select avg(1.0 * [DewPoint])
697 from (
698 select DewPoint
699 from #station_average
700 where #station_average.[Year] = @year and
701 where #station_average.[Month] = @month and
702 where #station_average.[Day] = @day)
703 order by [DewPoint]
704 offset (@c - 1) / 2 rows
705 fetch next 1 + (1 - @c % 2) rows only) as Dewpoint
706 ,(declare @c bigint = (select count(*)
707 from #station_average
708 where #station_average.[Year] = @year and
709 where #station_average.[Month] = @month and
710 where #station_average.[Day] = @day)
711
712 select avg(1.0 * [SeaLevelPressure])
713 from (
714 select SeaLevelPressure
715 from #station_average
716 where #station_average.[Year] = @year and
717 where #station_average.[Month] = @month and
718 where #station_average.[Day] = @day)
719 order by [SeaLevelPressure]
720 offset (@c - 1) / 2 rows
721 fetch next 1 + (1 - @c % 2) rows only) as SeaLevelPressure
722 ,(declare @c bigint = (select count(*)
723 from #station_average
724 where #station_average.[Year] = @year and
725 where #station_average.[Month] = @month and
726 where #station_average.[Day] = @day)
727
728 select avg(1.0 * [Altimeter])
729 from (
730 select Altimeter
731 from #station_average
732 where #station_average.[Year] = @year and
733 where #station_average.[Month] = @month and
734 where #station_average.[Day] = @day)
735 order by [Altimeter]
736 offset (@c - 1) / 2 rows
737 fetch next 1 + (1 - @c % 2) rows only) as Altimeter
738 ,(declare @c bigint = (select count(*)
739 from #station_average
740 where #station_average.[Year] = @year and
741 where #station_average.[Month] = @month and
742 where #station_average.[Day] = @day)
743
744 select avg(1.0 * [StationPressure])
745 from (
746 select StationPressure
747 from #station_average
748 where #station_average.[Year] = @year and
749 where #station_average.[Month] = @month and
750 where #station_average.[Day] = @day)
751 order by [StationPressure]
752 offset (@c - 1) / 2 rows
753 fetch next 1 + (1 - @c % 2) rows only) as StationPressure
754 ,(declare @c bigint = (select count(*)
755 from #station_average
756 where #station_average.[Year] = @year and
757 where #station_average.[Month] = @month and
758 where #station_average.[Day] = @day)
759
760 select avg(1.0 * [MaxTemp])
761 from (
762 select MaxTemp
763 from #station_average
764 where #station_average.[Year] = @year and
765 where #station_average.[Month] = @month and
766 where #station_average.[Day] = @day)
767 order by [MaxTemp]
768 offset (@c - 1) / 2 rows
769 fetch next 1 + (1 - @c % 2) rows only) as MaxTemp
770 ,(declare @c bigint = (select count(*)
771 from #station_average
772 where #station_average.[Year] = @year and
773 where #station_average.[Month] = @month and
774 where #station_average.[Day] = @day)
775
776 select avg(1.0 * [MinTemp])
777 from (
778 select MinTemp
779 from #station_average
780 where #station_average.[Year] = @year and
781 where #station_average.[Month] = @month and
782 where #station_average.[Day] = @day)
783 order by [MinTemp]
784 offset (@c - 1) / 2 rows
785 fetch next 1 + (1 - @c % 2) rows only) as MinTemp
786 ,(select sum(
787 Precip1Hour + Precip6Hour + Precip24Hour + PrecipOther
788 from #station_average
789 where
790 [Year] = @year and
791 [Month] = @month and
792 [Day] = @day)
793 as TotalPrecip
794 ,(declare @c bigint = (select count(*)
795 from #station_average
796 where #station_average.[Year] = @year and
797 where #station_average.[Month] = @month and
798 where #station_average.[Day] = @day)
799
800 select avg(1.0 * [SnowDepth])
801 from (
802 select SnowDepth
803 from #station_average
804 where #station_average.[Year] = @year and
805 where #station_average.[Month] = @month and
806 where #station_average.[Day] = @day)
807 order by [SnowDepth]
808 offset (@c - 1) / 2 rows
809 fetch next 1 + (1 - @c % 2) rows only) as SnowDepth
810 from #station_average
811 where
812 #station_average.[Year] = @year and
813 #station_average.[Month] = @month and
814 #station_average.[Day] = @day
815
816 set @day = @day + 1
817
818 end
819
820 insert into station_monthly_average
821 select Station
822 ,Year
823 ,Month
824 ,(select min(Sunrise)
825 from #station_daily
826 where #station_daily.[Station] = @stationID and
827 where #station_daily.[Year] = @year and
828 where #station_daily.[Month] = @month) as MinSunrise
829 ,(select max(Sunrise)
830 from #station_daily
831 where #station_daily.[Station] = @stationID and
832 where #station_daily.[Year] = @year and
833 where #station_daily.[Month] = @month) as MaxSunrise
834 ,(select avg(Sunrise)
835 from #station_daily
836 where #station_daily.[Station] = @stationID and
837 where #station_daily.[Year] = @year and
838 where #station_daily.[Month] = @month) as AvgSunrise
839 ,(select min(Sunset)
840 from #station_daily
841 where #station_daily.[Station] = @stationID and
842 where #station_daily.[Year] = @year and
843 where #station_daily.[Month] = @month) as MinSunset
844 ,(select max(Sunset)
845 from #station_daily
846 where #station_daily.[Station] = @stationID and
847 where #station_daily.[Year] = @year and
848 where #station_daily.[Month] = @month) as MaxSunset
849 ,(select avg(Sunset)
850 from #station_daily
851 where #station_daily.[Station] = @stationID and
852 where #station_daily.[Year] = @year and
853 where #station_daily.[Month] = @month) as AvgSunset
854 ,avg(WindDirection)
855 ,avg(WindSpeed)
856 ,avg(WindGust)
857 ,avg(CloudCeiling)
858 ,avg(Visibility)
859 ,avg(Temperature)
860 ,avg(DewPoint)
861 ,avg(SeaLevelPressure)
862 ,avg(SeaLevelPressuure)
863 ,avg(Altimeter)
864 ,avg(StationPressure)
865 ,avg(MaxTemp)
866 ,avg(MinTemp)
867 ,(select max(
868 Temperature)
869 from #station_average
870 where
871 #station_average.[Year] = @year and
872 #station_average.[Month] = @month
873 and
874 where #station_average.[Time] between @sunrise and @sunset) as DayMaxTemp
875 ,(select min(
876 Temperature)
877 from #station_average
878 where
879 #station_average.[Year] = @year and
880 #station_average.[Month] = @month
881 and
882 where #station_average.[Time] between @sunrise and @sunset) as DayMinTemp
883 ,(select avg(
884 Temperature)
885 from #station_average
886 where
887 #station_average.[Year] = @year and
888 #station_average.[Month] = @month
889 and
890 where #station_average.[Time] between @sunrise and @sunset) as DayAvgTemp
891 ,(select max(
892 Temperature)
893 from #station_average
894 where
895 #station_average.[Year] = @year and
896 #station_average.[Month] = @month
897 and
898 where #station_average.[Time] between @sunrise and @sunset) as DayMaxTemp
899 ,(select min(
900 Temperature)
901 from #station_average
902 where
903 #station_average.[Year] = @year and
904 #station_average.[Month] = @month
905 and
906 where #station_average.[Time] between @sunrise and @sunset) as DayMinTemp
907 ,(select avg(
908 Temperature)
909 from #station_average
910 where
911 #station_average.[Year] = @year and
912 #station_average.[Month] = @month
913 and
914 where #station_average.[Time] between @sunrise and @sunset) as DayAvgTemp
915 ,avg(select PrecipTotal
916 from #station_daily
917 where #station_daily.[Station] = @stationID and
918 where #station_daily.[Year] = @year and
919 where #station_daily.[Month] = @month)
920 as Precip
921 ,(
922 select count(*)
923 from #station_daily
924 where PrecipTrace = 1 and
925 where #station_daily.[Station] = @stationID and
926 where #station_daily.[Year] = @year and
927 where #station_daily.[Month] = @month)
928 as PrecipTraceDays
929 ,avg(SnowDepth)
930 ,(
931 select count(*)
932 from #station_daily
933 where SnowTrace = 1 and
934 where #station_daily.[Station] = @stationID and
935 where #station_daily.[Year] = @year and
936 where #station_daily.[Month] = @month)
937 as SnowTraceDays
938 from #station_average
939 where
940 #station_average.[Year] = @year and
941 #station_average.[month] = @month
942
943 insert into station_monthly_median
944 select Station
945 ,Year
946 ,Month
947 ,(declare @c bigint = (select count(*)
948 from #station_average
949 where #station_average.[Year] = @year and
950 where #station_average.[Month] = @month
951
952 select avg(1.0 * [WindDirection])
953 from (
954 select WindDirection
955 from #station_average
956 where #station_average.[Year] = @year and
957 where #station_average.[Month] = @month)
958 order by [WindDirection]
959 offset (@c - 1) / 2 rows
960 fetch next 1 + (1 - @c % 2) rows only) as WindDirection
961 ,(declare @c bigint = (select count(*)
962 from #station_average
963 where #station_average.[Year] = @year and
964 where #station_average.[Month] = @month
965
966 select avg(1.0 * [WindSpeed])
967 from (
968 select WindSpeed
969 from #station_average
970 where #station_average.[Year] = @year and
971 where #station_average.[Month] = @month)
972 order by [WindSpeed]
973 offset (@c - 1) / 2 rows
974 fetch next 1 + (1 - @c % 2) rows only) as WindSpeed
975 ,(declare @c bigint = (select count(*)
976 from #station_average
977 where #station_average.[Year] = @year and
978 where #station_average.[Month] = @month)
979
980 select avg(1.0 * [WindGust])
981 from (
982 select WindGust
983 from #station_average
984 where #station_average.[Year] = @year and
985 where #station_average.[Month] = @month)
986 order by [WindGust]
987 offset (@c - 1) / 2 rows
988 fetch next 1 + (1 - @c % 2) rows only) as WindGust
989 ,(declare @c bigint = (select count(*)
990 from #station_average
991 where #station_average.[Year] = @year and
992 where #station_average.[Month] = @month)
993
994 select avg(1.0 * [CloudCeiling])
995 from (
996 select CloudCeiling
997 from #station_average
998 where #station_average.[Year] = @year and
999 where #station_average.[Month] = @month)
1000 order by [CloudCeiling]
1001 offset (@c - 1) / 2 rows
1002 fetch next 1 + (1 - @c % 2) rows only) as CloudCeiling
1003 ,(declare @c bigint = (select count(*)
1004 from #station_average
1005 where #station_average.[Year] = @year and
1006 where #station_average.[Month] = @month)
1007
1008 select avg(1.0 * [Visibility])
1009 from (
1010 select Visibility
1011 from #station_average
1012 where #station_average.[Year] = @year and
1013 where #station_average.[Month] = @month)
1014 order by [Visibility]
1015 offset (@c - 1) / 2 rows
1016 fetch next 1 + (1 - @c % 2) rows only) as Visibility
1017 ,(declare @c bigint = (select count(*)
1018 from #station_average
1019 where #station_average.[Year] = @year and
1020 where #station_average.[Month] = @month)
1021
1022 select avg(1.0 * [Temperature])
1023 from (
1024 select Temperature
1025 from #station_average
1026 where #station_average.[Year] = @year and
1027 where #station_average.[Month] = @month)
1028 order by [Temperature]
1029 offset (@c - 1) / 2 rows
1030 fetch next 1 + (1 - @c % 2) rows only) as Temperature
1031 ,(declare @c bigint = (select count(*)
1032 from #station_average
1033 where #station_average.[Year] = @year and
1034 where #station_average.[Month] = @month)
1035
1036 select avg(1.0 * [DewPoint])
1037 from (
1038 select DewPoint
1039 from #station_average
1040 where #station_average.[Year] = @year and
1041 where #station_average.[Month] = @month)
1042 order by [DewPoint]
1043 offset (@c - 1) / 2 rows
1044 fetch next 1 + (1 - @c % 2) rows only) as Dewpoint
1045 ,(declare @c bigint = (select count(*)
1046 from #station_average
1047 where #station_average.[Year] = @year and
1048 where #station_average.[Month] = @month)
1049
1050 select avg(1.0 * [SeaLevelPressure])
1051 from (
1052 select SeaLevelPressure
1053 from #station_average
1054 where #station_average.[Year] = @year and
1055 where #station_average.[Month] = @month)
1056 order by [SeaLevelPressure]
1057 offset (@c - 1) / 2 rows
1058 fetch next 1 + (1 - @c % 2) rows only) as SeaLevelPressure
1059 ,(declare @c bigint = (select count(*)
1060 from #station_average
1061 where #station_average.[Year] = @year and
1062 where #station_average.[Month] = @month)
1063
1064 select avg(1.0 * [Altimeter])
1065 from (
1066 select Altimeter
1067 from #station_average
1068 where #station_average.[Year] = @year and
1069 where #station_average.[Month] = @month)
1070 order by [Altimeter]
1071 offset (@c - 1) / 2 rows
1072 fetch next 1 + (1 - @c % 2) rows only) as Altimeter
1073 ,(declare @c bigint = (select count(*)
1074 from #station_average
1075 where #station_average.[Year] = @year and
1076 where #station_average.[Month] = @month)
1077
1078 select avg(1.0 * [StationPressure])
1079 from (
1080 select StationPressure
1081 from #station_average
1082 where #station_average.[Year] = @year and
1083 where #station_average.[Month] = @month)
1084 order by [StationPressure]
1085 offset (@c - 1) / 2 rows
1086 fetch next 1 + (1 - @c % 2) rows only) as StationPressure
1087 ,(declare @c bigint = (select count(*)
1088 from #station_average
1089 where #station_average.[Year] = @year and
1090 where #station_average.[Month] = @month)
1091
1092 select avg(1.0 * [MaxTemp])
1093 from (
1094 select MaxTemp
1095 from #station_average
1096 where #station_average.[Year] = @year and
1097 where #station_average.[Month] = @month)
1098 order by [MaxTemp]
1099 offset (@c - 1) / 2 rows
1100 fetch next 1 + (1 - @c % 2) rows only) as MaxTemp
1101 ,(declare @c bigint = (select count(*)
1102 from #station_average
1103 where #station_average.[Year] = @year and
1104 where #station_average.[Month] = @month)
1105
1106 select avg(1.0 * [MinTemp])
1107 from (
1108 select MinTemp
1109 from #station_average
1110 where #station_average.[Year] = @year and
1111 where #station_average.[Month] = @month)
1112 order by [MinTemp]
1113 offset (@c - 1) / 2 rows
1114 fetch next 1 + (1 - @c % 2) rows only) as MinTemp
1115 ,(declare @c bigint = (select count(*)
1116 from #station_average
1117 where #station_average.[Year] = @year and
1118 where #station_average.[Month] = @month)
1119
1120 select avg(1.0 * [PrecipTotal])
1121 from (
1122 select PrecipTotal
1123 from #station_daily
1124 where #station_daily.[Year] = @year and
1125 where #station_daily.[Month] = @month)
1126 order by [PrecipTotal]
1127 offset (@c - 1) / 2 rows
1128 fetch next 1 + (1 - @c % 2) rows only) as Precip
1129 ,(declare @c bigint = (select count(*)
1130 from #station_average
1131 where #station_daily.[Year] = @year and
1132 where #station_daily.[Month] = @month)
1133
1134 select avg(1.0 * [SnowDepth])
1135 from (
1136 select SnowDepth
1137 from #station_average
1138 where #station_average.[Year] = @year and
1139 where #station_average.[Month] = @month)
1140 order by [SnowDepth]
1141 offset (@c - 1) / 2 rows
1142 fetch next 1 + (1 - @c % 2) rows only) as SnowDepth
1143 from #station_average
1144 where
1145 #station_average.[Year] = @year and
1146 #station_average.[Month] = @month
1147
1148 end
1149
1150 set @month = @month + 1
1151
1152 end
1153
1154 insert into station_annual_average
1155 select Station
1156 ,Year
1157 ,(select min(Sunrise)
1158 from #station_daily
1159 where #station_daily.[Station] = @stationID and
1160 where #station_daily.[Year] = @year) as MinSunrise
1161 ,(select max(Sunrise)
1162 from #station_daily
1163 where #station_daily.[Station] = @stationID and
1164 where #station_daily.[Year] = @year) as MaxSunrise
1165 ,(select avg(Sunrise)
1166 from #station_daily
1167 where #station_daily.[Station] = @stationID and
1168 where #station_daily.[Year] = @year) as AvgSunrise
1169 ,(select min(Sunset)
1170 from #station_daily
1171 where #station_daily.[Station] = @stationID and
1172 where #station_daily.[Year] = @year) as MinSunset
1173 ,(select max(Sunset)
1174 from #station_daily
1175 where #station_daily.[Station] = @stationID and
1176 where #station_daily.[Year] = @year) as MaxSunset
1177 ,(select avg(Sunset)
1178 from #station_daily
1179 where #station_daily.[Station] = @stationID and
1180 where #station_daily.[Year] = @year) as AvgSunset
1181 ,avg(WindDirection)
1182 ,avg(WindSpeed)
1183 ,avg(WindGust)
1184 ,avg(CloudCeiling)
1185 ,avg(Visibility)
1186 ,avg(Temperature)
1187 ,avg(DewPoint)
1188 ,avg(SeaLevelPressure)
1189 ,avg(SeaLevelPressuure)
1190 ,avg(Altimeter)
1191 ,avg(StationPressure)
1192 ,avg(MaxTemp)
1193 ,avg(MinTemp)
1194 ,(select max(
1195 Temperature)
1196 from #station_average
1197 where
1198 #station_average.[Year] = @year
1199 and
1200 where #station_average.[Time] between @sunrise and @sunset) as DayMaxTemp
1201 ,(select min(
1202 Temperature)
1203 from #station_average
1204 where
1205 #station_average.[Year] = @year
1206 and
1207 where #station_average.[Time] between @sunrise and @sunset) as DayMinTemp
1208 ,(select avg(
1209 Temperature)
1210 from #station_average
1211 where
1212 #station_average.[Year] = @year
1213 and
1214 where #station_average.[Time] between @sunrise and @sunset) as DayAvgTemp
1215 ,(select max(
1216 Temperature)
1217 from #station_average
1218 where
1219 #station_average.[Year] = @year
1220 and
1221 where #station_average.[Time] between @sunrise and @sunset) as DayMaxTemp
1222 ,(select min(
1223 Temperature)
1224 from #station_average
1225 where
1226 #station_average.[Year] = @year
1227 and
1228 where #station_average.[Time] between @sunrise and @sunset) as DayMinTemp
1229 ,(select avg(
1230 Temperature)
1231 from #station_average
1232 where
1233 #station_average.[Year] = @year
1234 and
1235 where #station_average.[Time] between @sunrise and @sunset) as DayAvgTemp
1236 ,avg(select TotalPrecip
1237 from #station_daily
1238 where #station_daily.Station = stationID and
1239 where #station_daily.[Year] = @year)
1240 as Precip
1241 ,(
1242 select count(*)
1243 from #station_daily
1244 where PrecipTrace = 1 and
1245 where #station_daily.[Station] = @stationID and
1246 where #station_daily.[Year] = @year and
1247 where #station_daily.[Month] = @month)
1248 as PrecipTraceDays
1249 ,avg(SnowDepth)
1250 ,(
1251 select count(*)
1252 from #station_daily
1253 where SnowTrace = 1 and
1254 where #station_daily.[Station] = @stationID and
1255 where #station_daily.[Year] = @year and
1256 where #station_daily.[Month] = @month)
1257 as SnowTraceDays
1258 from #station_average
1259 where
1260 year = @year
1261
1262 insert into station_annual_median
1263 select Station
1264 ,Year
1265 ,(declare @c bigint = (select count(*)
1266 from #station_average
1267 where #station_average.[Year] = @year
1268
1269 select avg(1.0 * [WindDirection])
1270 from (
1271 select WindDirection
1272 from #station_average
1273 where #station_average.[Year] = @year)
1274 order by [WindDirection]
1275 offset (@c - 1) / 2 rows
1276 fetch next 1 + (1 - @c % 2) rows only) as WindDirection
1277 ,(declare @c bigint = (select count(*)
1278 from #station_average
1279 where #station_average.[Year] = @year)
1280
1281 select avg(1.0 * [WindSpeed])
1282 from (
1283 select WindSpeed
1284 from #station_average
1285 where #station_average.[Year] = @year)
1286 order by [WindSpeed]
1287 offset (@c - 1) / 2 rows
1288 fetch next 1 + (1 - @c % 2) rows only) as WindSpeed
1289 ,(declare @c bigint = (select count(*)
1290 from #station_average
1291 where #station_average.[Year] = @year)
1292
1293 select avg(1.0 * [WindGust])
1294 from (
1295 select WindGust
1296 from #station_average
1297 where #station_average.[Year] = @year)
1298 order by [WindGust]
1299 offset (@c - 1) / 2 rows
1300 fetch next 1 + (1 - @c % 2) rows only) as WindGust
1301 ,(declare @c bigint = (select count(*)
1302 from #station_average
1303 where #station_average.[Year] = @year)
1304
1305 select avg(1.0 * [CloudCeiling])
1306 from (
1307 select CloudCeiling
1308 from #station_average
1309 where #station_average.[Year] = @year)
1310 order by [CloudCeiling]
1311 offset (@c - 1) / 2 rows
1312 fetch next 1 + (1 - @c % 2) rows only) as CloudCeiling
1313 ,(declare @c bigint = (select count(*)
1314 from #station_average
1315 where #station_average.[Year] = @year)
1316
1317 select avg(1.0 * [Visibility])
1318 from (
1319 select Visibility
1320 from #station_average
1321 where #station_average.[Year] = @year)
1322 order by [Visibility]
1323 offset (@c - 1) / 2 rows
1324 fetch next 1 + (1 - @c % 2) rows only) as Visibility
1325 ,(declare @c bigint = (select count(*)
1326 from #station_average
1327 where #station_average.[Year] = @year)
1328
1329 select avg(1.0 * [Temperature])
1330 from (
1331 select Temperature
1332 from #station_average
1333 where #station_average.[Year] = @year)
1334 order by [Temperature]
1335 offset (@c - 1) / 2 rows
1336 fetch next 1 + (1 - @c % 2) rows only) as Temperature
1337 ,(declare @c bigint = (select count(*)
1338 from #station_average
1339 where #station_average.[Year] = @year)
1340
1341 select avg(1.0 * [DewPoint])
1342 from (
1343 select DewPoint
1344 from #station_average
1345 where #station_average.[Year] = @year)
1346 order by [DewPoint]
1347 offset (@c - 1) / 2 rows
1348 fetch next 1 + (1 - @c % 2) rows only) as Dewpoint
1349 ,(declare @c bigint = (select count(*)
1350 from #station_average
1351 where #station_average.[Year] = @year)
1352
1353 select avg(1.0 * [SeaLevelPressure])
1354 from (
1355 select SeaLevelPressure
1356 from #station_average
1357 where #station_average.[Year] = @year)
1358 order by [SeaLevelPressure]
1359 offset (@c - 1) / 2 rows
1360 fetch next 1 + (1 - @c % 2) rows only) as SeaLevelPressure
1361 ,(declare @c bigint = (select count(*)
1362 from #station_average
1363 where #station_average.[Year] = @year)
1364
1365 select avg(1.0 * [Altimeter])
1366 from (
1367 select Altimeter
1368 from #station_average
1369 where #station_average.[Year] = @year)
1370 order by [Altimeter]
1371 offset (@c - 1) / 2 rows
1372 fetch next 1 + (1 - @c % 2) rows only) as Altimeter
1373 ,(declare @c bigint = (select count(*)
1374 from #station_average
1375 where #station_average.[Year] = @year)
1376
1377 select avg(1.0 * [StationPressure])
1378 from (
1379 select StationPressure
1380 from #station_average
1381 where #station_average.[Year] = @year)
1382 order by [StationPressure]
1383 offset (@c - 1) / 2 rows
1384 fetch next 1 + (1 - @c % 2) rows only) as StationPressure
1385 ,(declare @c bigint = (select count(*)
1386 from #station_average
1387 where #station_average.[Year] = @year)
1388
1389 select avg(1.0 * [MaxTemp])
1390 from (
1391 select MaxTemp
1392 from #station_average
1393 where #station_average.[Year] = @year)
1394 order by [MaxTemp]
1395 offset (@c - 1) / 2 rows
1396 fetch next 1 + (1 - @c % 2) rows only) as MaxTemp
1397 ,(declare @c bigint = (select count(*)
1398 from #station_average
1399 where #station_average.[Year] = @year)
1400
1401 select avg(1.0 * [MinTemp])
1402 from (
1403 select MinTemp
1404 from #station_average
1405 where #station_average.[Year] = @year
1406 order by [MinTemp]
1407 offset (@c - 1) / 2 rows
1408 fetch next 1 + (1 - @c % 2) rows only) as MinTemp
1409 ,(declare @c bigint = (select count(*)
1410 from #station_average
1411 where #station_average.[Year] = @year)
1412
1413 select avg(1.0 * [PrecipTotal])
1414 from (
1415 select PrecipTotal
1416 from #station_daily
1417 where #station_daily.[Year] = @year
1418 order by [PrecipTotal]
1419 offset (@c - 1) / 2 rows
1420 fetch next 1 + (1 - @c % 2) rows only) as Precip
1421 ,(declare @c bigint = (select count(*)
1422 from #station_average
1423 where #station_daily.[Year] = @year)
1424
1425 select avg(1.0 * [SnowDepth])
1426 from (
1427 select SnowDepth
1428 from #station_average
1429 where #station_average.[Year] = @year
1430 order by [SnowDepth]
1431 offset (@c - 1) / 2 rows
1432 fetch next 1 + (1 - @c % 2) rows only) as SnowDepth
1433 from #station_average
1434 where
1435 #station_average.[Year] = @year
1436
1437 set @year = @year + 1
1438
1439 end
1440
1441 set @stationID = @stationID + 1
1442 end
1443
1444 else
1445
1446 begin
1447
1448 set @stationID = @stationID + 1
1449
1450 end
1451
1452end
1453
1454drop table #station_average