Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL Advice / help for this query RE: Advice / help for this query
June 20, 2018 at 12:41 pm
celsoc23 - Wednesday, June 20, 2018 12:01 PMHi dudes!
First these are my tables with some records:table: INDEX (all primary keys)
longitude latitude year month
-87.875 12.875 1971 1
-87.875 12.875 1971 2
-87.875 12.875 1971 3
-87.875 12.875 1971 4
-87.875 12.875 1971 5
-87.875 12.875 1971 6
-87.875 12.875 1971 7
-87.875 12.875 1971 8
-87.875 12.875 1971 9
-87.875 12.875 1971 10
-87.875 12.875 1971 11
-87.875 12.875 1972 3
-87.875 12.875 1972 4
table: DATACLIME (year, month primaries key(
year month tempmin tempavg tempmax rainfall
1971 1 20.2 26.3 32.4 0.3
1971 2 20.6 27.25 33.9 0.8
1971 3 20.8 27.3 33.8 3.3
1971 4 22 28.15 34.3 18.2
1971 8 21.5 26.1 30.7 481.1
1971 9 21.2 25.7 30.2 270.6
1971 10 21.2 26.25 31.3 46.3
1971 11 20.5 25.95 31.4 14.3
1972 3 20.6 27.1 33.6 7.1
1972 4 21.9 27.75 33.6 49.9
This is a short breif of what the data means, I have several coordinates (long,lat) which identify a unique place, and have climate data throug the years and months (1,2,... 12). What I pretending is make a query or view thats generates the average of tempmin, tempmax, tempavg from every each month of every year (1971 - 2000), sorted for months, for each location or coordinate (longitude, latitude). In shorts words I want an average of dataclimate table per month and per location (longitude, latitude from table INDEX). The query I haven't been able of write should look like this:
longitude latitude months tempmax tempmin tempavg
-87.875 12.875 1 33.2 23.6 26.65
-87.875 12.875 2 29.4 23.4 26.55
-87.875 12.875 3 31.5 24.2 28
-87.875 12.875 4 28.2 22.4 25.9
-87.875 12.875 5 31.3 24.1 27.8
-87.875 12.875 6 30.5 21.7 24.95
-87.875 12.875 7 30.6 24.3 27.8
-87.875 12.875 8 31.1 24.2 27.35
-87.875 12.875 9 31.2 24.1 27.35
-87.875 12.875 10 31.6 25.2 28.15
-87.875 12.875 11 31 25.4 28.3
-87.875 12.875 12 32.3 25.4 28.5
-83.125 15.125 1 27.9 25.3 28.15
-83.125 15.125 2 30.8 24.7 28.5
-83.125 15.125 3 29.4 21.4 24.65
-83.125 15.125 4 29.2 24.6 27.7
-83.125 15.125 5 32.3 23.4 26.4
-83.125 15.125 6 31.4 21.2 25.2
-83.125 15.125 7 28.6 24.9 28.6
-83.125 15.125 8 31.9 25.2 28.3
-83.125 15.125 9 28 22.9 25.75
-83.125 15.125 10 29.7 25.6 28.75
-83.125 15.125 11 31.7 21.5 24.75
-83.125 15.125 12 29.3 22.5 26.1Sincerely... I need at least a clue. Greetings from Nicaragua!
This looks like you just need a join on year and month between both tables.