· 8 years ago · Jan 05, 2018, 10:50 PM
1declare @station int
2declare @maxstation int
3declare @lat float
4declare @long float
5declare @month char(2)
6declare @maxmonth char(2)
7declare @day char(2)
8declare @maxday char(2)
9declare @year char(4)
10declare @maxyear char(4)
11declare @time char(4)
12declare @date char(50)
13declare @sunrise datetime
14declare @sunset datetime
15declare @offset int
16
17create table #station_sunrise(Sunrise datetime)
18create table #station_sunset(Sunset datetime)
19
20set @station = (select min(StationIncrementalID)
21 from station_data)
22
23set @maxstation = (select max(StationIncrementalID)
24 from station_data)
25
26while @station <= @maxstation
27
28begin
29
30 set @lat = (select cast(Latitude as float) from station_data
31 where StationIncrementalID = @station)
32
33 set @long = (select cast(Longitude as float) from station_data
34 where StationIncrementalID = @station)
35
36 if (@lat is not null and @long is not null)
37
38 begin
39
40 set @year = (select min([Year]) from table
41 where Station = @station)
42
43 set @maxyear = (select max([Year]) from table
44 where Station = @station)
45
46 if (@year <= @maxyear and exists (select [Year] from table
47 where Station = @station and
48 [Year] = @year)
49 )
50
51 begin
52
53 set @month = (select min([Month]) from table
54 where Station = @station and
55 [Year] = @year)
56
57 set @maxmonth = (select max([Month]) from table
58 where Station = @station and
59 [Year] = @year)
60
61 if (@month <= @maxmonth and exists (select [Year], [Month] from table
62 where Station = @station and
63 [Year] = @year and
64 [Month] = @month)
65 )
66
67 begin
68
69 set @day = (select min([Day]) from table
70 where Station = @station and
71 [Year] = @year and
72 [Month] = @month)
73
74 set @maxday = (select max([Day]) from table
75 where Station = @station and
76 [Year] = @year and
77 [Month] = @month)
78
79 if (@day <= @maxday and exists (select [Year], [Month], [Day] from table
80 where Station = @station and
81 [Year] = @year and
82 [Month] = @month and
83 [Day] = @day)
84 )
85
86 begin
87
88 delete from #station_sunrise
89 delete from #station_sunset
90
91 set @date = concat(@month,'/',@day,'/',@year)
92
93 insert into #station_sunrise
94 exec DetermineSunrise @date, @lat, @long, @offset
95 insert into #station_sunset
96 exec DetermineSunset @date, @lat, @long, @offset
97
98 set @sunrise = (select replace(convert(char(4),(select top 1 [Sunrise] from #station_sunrise),108), ':', ''))
99 set @sunset = (select replace(convert(char(4),(select top 1 [Sunset] from #station_sunset),108), ':', ''))
100
101 update table
102 set [Daylight] = case
103 when table.Station = @station and
104 table.[Year] = @year and
105 table.[Month] = @month and
106 table.[Day] = @day and
107 table.[Time] between @sunrise and @sunset
108 then 1
109 when table.Station = @station and
110 table.[Year] = @year and
111 table.[Month] = @month and
112 table.[Day] = @day and
113 (table.[Time] < @sunrise or
114 table.[Time] > @sunset)
115 then 0
116 else null
117 end
118
119 set @day = @day + 1
120
121 end
122
123 else
124
125 begin
126
127 set @day = @day + 1
128
129 end
130
131 set @month = @month + 1
132
133 end
134
135 else
136
137 begin
138
139 set @month = @month + 1
140
141 end
142
143 set @year = @year + 1
144
145 end
146
147 else
148
149 begin
150
151 set @year = @year + 1
152
153 end
154
155 set @station = @station + 1
156
157 end
158
159 else
160
161 begin
162
163 set @station = @station + 1
164
165 end
166
167end