<HTML><BODY style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; "><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><FONT class="Apple-style-span" face="Lucida Grande" size="3"><SPAN class="Apple-style-span" style="font-size: 11px;">I need some help with rewriting a query. I have a query that dumps daily climate data, filling in missing data with monthly averages (one line per day). </SPAN></FONT><FONT class="Apple-style-span" face="Lucida Grande" size="3"><SPAN class="Apple-style-span" style="font-size: 11px;"></SPAN></FONT></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><FONT class="Apple-style-span" face="Lucida Grande" size="3"><SPAN class="Apple-style-span" style="font-size: 11px;"><BR class="khtml-block-placeholder"></SPAN></FONT></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><FONT class="Apple-style-span" face="Lucida Grande" size="3"><SPAN class="Apple-style-span" style="font-size: 11px;">I want to output monthly averages (one line per month). I am having a hard time wrapping my head around this. Particularly how to deal with the doy column (day of year). I have tried several approaches and my forehead is starting to get my keyboard bloody. </SPAN></FONT></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><FONT class="Apple-style-span" face="Lucida Grande" size="3"><SPAN class="Apple-style-span" style="font-size: 11px;"><BR class="khtml-block-placeholder"></SPAN></FONT></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><FONT class="Apple-style-span" face="Lucida Grande" size="3"><SPAN class="Apple-style-span" style="font-size: 11px;">Here is the daily query:</SPAN></FONT></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><FONT class="Apple-style-span" face="Lucida Grande" size="3"><SPAN class="Apple-style-span" style="font-size: 11px;"><BR class="khtml-block-placeholder"></SPAN></FONT></DIV><DIV>SELECT CASE</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>WHEN w.station_id = site_near.station_id THEN w.obs_id</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>ELSE s.obs_id</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>END AS obs_id,</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>site_near.station_id,</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>site_near.longname,</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>w.year,</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>w.doy,</DIV><DIV>--replace missing values (-999) with the monthly average</DIV><DIV> CASE w.tmax</DIV><DIV> WHEN -999 THEN avgtmax.avg</DIV><DIV> ELSE w.tmax</DIV><DIV> END,</DIV><DIV>CASE w.tmin</DIV><DIV> WHEN -999 THEN avgtmin.avg</DIV><DIV> ELSE w.tmin</DIV><DIV> END,</DIV><DIV>CASE s.par</DIV><DIV> WHEN -999 THEN avgpar.avg</DIV><DIV> ELSE s.par</DIV><DIV> END,</DIV><DIV>CASE w.precip</DIV><DIV> WHEN -999 THEN avgprecip.avg</DIV><DIV> ELSE w.precip</DIV><DIV> END</DIV><DIV>FROM site_near</DIV><DIV> INNER JOIN solar s</DIV><DIV> ON (site_near.ref_solar_station_id = s.station_id</DIV><DIV> AND site_near.obs_year = s.year)</DIV><DIV> INNER JOIN weather w</DIV><DIV> ON (site_near.ref_weather_station_id = w.station_id</DIV><DIV> AND site_near.obs_year = w.year</DIV><DIV> AND s.date = w.date)</DIV><DIV> INNER JOIN (SELECT MONTH,</DIV><DIV> round(avg(tmax)::numeric, 2) AS avg</DIV><DIV> FROM weather</DIV><DIV> WHERE tmax != -999</DIV><DIV> GROUP BY MONTH) AS avgtmax</DIV><DIV> ON (w.month = avgtmax.month)</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>INNER JOIN (SELECT MONTH,</DIV><DIV> round(avg(tmin)::numeric, 2) AS avg</DIV><DIV> FROM weather</DIV><DIV> WHERE tmin != -999</DIV><DIV> GROUP BY MONTH) AS avgtmin</DIV><DIV> ON (w.month = avgtmin.month)</DIV><DIV> INNER JOIN (SELECT MONTH,</DIV><DIV> round(avg(par)::numeric, 2) AS avg</DIV><DIV> FROM solar</DIV><DIV> WHERE par != -999</DIV><DIV> GROUP BY MONTH) AS avgpar</DIV><DIV> ON (s.month = avgpar.month)</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>INNER JOIN (SELECT MONTH,</DIV><DIV> round(avg(precip)::numeric, 2) AS avg</DIV><DIV> FROM weather</DIV><DIV> WHERE precip != -999</DIV><DIV> GROUP BY MONTH) AS avgprecip</DIV><DIV> ON (w.month = avgprecip.month)</DIV><DIV>--select station to output climate data by id number</DIV><DIV>WHERE w.station_id = 219101</DIV></BODY></HTML>