We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

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

GTC-KTX
GTC-KTX asked
on
Medium Priority
1,518 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

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"
 

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT

Commented:
Replace every TempF with

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

CERTIFIED EXPERT
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

Author

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

Commented:
You can run a separate query like this to get that information:
select observation_date, station_id, tempF from weather where tempF = 'NA'

Author

Commented:
Thanks iparul!  i guess i didn't consider a completely separate query.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.