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!
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"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_Da
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
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_Da
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
You can run a separate query like this to get that information:
select observation_date, station_id, tempF from weather where tempF = 'NA'
select observation_date, station_id, tempF from weather where tempF = 'NA'
ASKER
Thanks iparul! i guess i didn't consider a completely separate query.
case when isnumeric(TempF )<>1 then NULL else TempF end