SQL Query - min, max, average - excluding NA values

Worked with Brandon on this before, so hopefully he or someone else might be able to help.
The SQL query below occasionally gives me the following error:
(Error converting data type varchar to numeric)
This is due to the fact that, occasionally, the tempF field is populated with NA instead of a value.  This occurs when the temperature is not available at the time we sample it, due to station being down, or internet being down etc.
I can either change the SQL string or i can change the database to populate something other than NA whenever it can't get the temp.  Any suggestions would be appreciated!
sqlstring1 = "select observation_date, station_id, min(TempF),max(TempF),avg(TempF)from (select dateadd(d, datediff(d, 0, convert(datetime, observation_date, 101)),0) as 
observation_date, station_id, cast(TempF as numeric(6,2)) as TempF from weather where
 (station_id = '" & querywthr1 & "') and convert(datetime, observation_date, 101) >= '" &
 date_range_start & "' and convert(datetime, observation_date, 101) < '" & date_range_end + 1
 & "' )a group by observation_date,station_id order by observation_date"

Open in new window

GTC-KTXAsked:
Who is Participating?
 
jparulConnect With a Mentor Commented:
Try this: Added a condition in where clause to skip the cases where tempF = NA
sqlstring1 = "select observation_date, station_id, min(TempF),max(TempF),avg(TempF)from (select dateadd(d, datediff(d, 0, convert(datetime, observation_date, 101)),0) as observation_date, station_id, cast(TempF as numeric(6,2)) as TempF from weather where  (tempF <> 'NA' and station_id = '" & querywthr1 & "') and convert(datetime, observation_date, 101) >= '" &  date_range_start & "' and convert(datetime, observation_date, 101) < '" & date_range_end + 1
 & "' )a group by observation_date,station_id order by observation_date"
 
0
 
ZberteocCommented:
Replace every TempF with

case when isnumeric(TempF )<>1 then NULL else TempF end

0
 
ZberteocConnect With a Mentor Commented:
Here:
sqlstring1 = "select observation_date, station_id, min(case when isnumeric(TempF )<>1 then NULL else TempF end),max(case when isnumeric(TempF )<>1 then NULL else TempF end),avg(case when isnumeric(TempF )<>1 then NULL else TempF end)from (select dateadd(d, datediff(d, 0, convert(datetime, observation_date, 101)),0) as 
observation_date, station_id, cast(case when isnumeric(TempF )<>1 then NULL else TempF end as numeric(6,2)) as TempF from weather where
 (station_id = '" & querywthr1 & "') and convert(datetime, observation_date, 101) >= '" &
 date_range_start & "' and convert(datetime, observation_date, 101) < '" & date_range_end + 1
 & "' )a group by observation_date,station_id order by observation_date"

Open in new window

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
GTC-KTXAuthor Commented:
Thanks guys!  both good workable solutions.  I will award points to both.
One follow up though that came to mind when looking at your solutions.
Is there a way to flag (on my output query) when i come across a date that had NA's for the temp?
I see that your solution will exclude them in the calculation of min, max, and everage.  But from a data integrity standpoint it would be good to know which days had NA's.  I don't know what would be the easiest way to flag.  Right now the output is 5 columns.....Observation_Date, Station_id, min, max, average.  Maybe there could be a sixth column that is populated with a flag everytime there are NA's in the temp calculation for a certain date.

By the way, if this is something more involved that should be asked as a new question with new points, just let me know and i can do that.

Greg
0
 
jparulCommented:
You can run a separate query like this to get that information:
select observation_date, station_id, tempF from weather where tempF = 'NA'
0
 
GTC-KTXAuthor Commented:
Thanks iparul!  i guess i didn't consider a completely separate query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.