Accessing data directly from the database tables is not recommended for several reasons:
· Creating the JOINs to get the flags, codes, site names, etc can be complex
· The schema might change over several years
· Accidental UPDATE or INSERT commands to the tables could corrupt the database

Even with the code that generates our reports, we don’t use direct table access. Rather, we use several Views installed in the database, the most useful of which is called

“Reporting.ReadingAverageDataFull.”

The user can query against or receive data from any of these fields:

SELECT [ReadingAverageDataTagID]
,[ReadingAverageIntervalID]
,[IntervalName] e.g., “001H”
,[TimeInterval]
,[IntervalDescription]
,[DisplayOrder]
,[LastPollTime]
,[Date] e.g., “01/01/2010 04:00:00”
,[SystemStandardizedDate]
,[DateOnly]
,[DateOnlyString]
,[SystemStandardizedDateOnly]
,[TimeOnly]
,[TimeOnlyString]
,[Year]
,[QuarterOfYear]
,[MonthOfYear]
,[MonthOfYearName]
,[DayOfYear]
,[DayOfMonth]
,[WeekOfYear]
,[WeekDay]
,[WeekDayName]
,[Hour]
,[Minute]
,[Second]
,[Millisecond]
,[FinalValue]
,[RawValue]
,[RawLoggerFlags]
,[IsMissing]
,[HasFlags]
,[HasAnnotations]
,[HasQualifierCodes]
,[IsValid]
,[ReportValue]
,[AmbAqsNullCodeID]
,[NullCode]
,[NullCodeDescription]
,[AqsMethodCode]
,[AqsDurationCode]
,[AqsDurationDescription]
,[ReadingAverageDataID]
,[DataGrade]
,[CreatedOn]
,[CreatedBy]
,[ModifiedOn]
,[ModifiedBy]
,[IsLocked]
,[MappedNullCode]
,[MappedNullCodeDescription]
,[MappedAirNowCodeDescription]
,[MappedAirNowCode]
,[HighestFlag]
,[HighestFlagDescription]
,[HighestFlagBackColor]
,[HighestFlagForeColor]
,[HighestFlagInvalidatesData]
,[SystemID]
,[SystemName]
,[SystemUtilityTimeZoneID]
,[SystemEnabled]
,[AgencyCode]
,[SystemTimeZoneOffset]
,[SystemTimeZoneDescription]
,[SystemTimeZoneAbbreviation]
,[SourceSiteID]
,[SiteName]
,[SiteAbbreviation]
,[SiteDescription]
,[SiteEnabled]
,[SiteAirNowMnemonic]
,[StreetAddress1]
,[StreetAddress2]
,[City]
,[County]
,[StateRegion]
,[ZipCode]
,[Latitude]
,[Longitude]
,[AqsSiteCode]
,[SurrogateSlope]
,[SurrogateOffset]
,[SiteFileImportCode]
,[SiteUtilityTimeZoneID]
,[SiteTimeZoneOffset]
,[SiteTimeZoneDescription]
,[SiteTimeZoneAbbreviation]
,[AmbAqsCountyTribalCodeID]
,[AqsCountyTribalName]
,[AqsCountyTribalCode]
,[AqsIsTribalCode]
,[AmbAqsStateCodeID]
,[AqsStateName]
,[AqsStateAbbreviation]
,[AqsStateCode]
,[SourceParameterID]
,[ParameterName]
,[ParameterEnabled]
,[ParameterDescription]
,[SourceParameterDataTypeID]
,[DataTypeKey]
,[DataTypeDescription]
,[EnableAirNowReporting]
,[TotalizeInReports]
,[ParameterAirNowMnemonic]
,[ReportedSourceParameterUnitID]
,[ReportedUnitName]
,[ReportedUnitDescription]
,[AmbAqsParameterTypeID]
,[AmbAqsParameterCategoryID]
,[AqsParameterCategory]
,[AqsParameterDescription]
,[AqsParameterCode]
,[AqsParameterAbbreviation]
,[CasNumber]
,[AmbAqsUnitCodeID]
,[AqsUnitDescription]
,[AqsUnitType]
,[AqsUnitAbbreviation]
,[AqsUnitCode]
,[AqsParameterOccuranceCode]
,[TruncateRoundRule]
,[EpaReportingPrecision]
,[EpaReportedDigits]
,[ParameterGraphMinimum]
,[ParameterGraphMaximum]
,[ParameterCalibrationSpan]
,[ParentSourceParameterID]
,[SourceParameterTemplateID]
,[ParameterTemplateName]
,[MinimumDetectableLimit]
,[InstrumentDetectionLimit]
,[LimitOfQuantization]
,[ParameterTemplateDescription]
,[PracticalQuantizationLimit]
,[ParameterAqsMethodCode]
,[MappedNullCodeID]
,[AssignedAmbAqsNullCodeID]
,[AssignedNullCode]
,[AssignedNullCodeDescription]
,[MappedAmbAqsNullCodeID]
,[ParameterTemplateKey]
FROM [AVData].[Reporting].[ReadingAverageDataFull]


An example query for a particular data point with basic information might look like this:

select SiteName,parametername,Hour,finalvalue, ReportedUnitName,isvalid from Reporting.ReadingAverageDataFull where Timeinterval = '001H' and Date = '05/10/2010'

And would return data that looks like this:

SiteName	parametername	Hour	finalvalue	ReportedUnitName	isvalid
Roane_Y	01_CO	0	11.15500164	PPM	FALSE
Roane_Y	02_SO2	0	112.6610947	PPB	FALSE
Roane_Y	03_NOX	0	223.3222351	PPB	FALSE
Roane_Y	04_NO2	0	92.42014313	PPB	FALSE
Roane_Y	05_NO	0	130.575592	PPB	FALSE
Blount_S	01_OZONEMAX	0	65.52895355	PPB	FALSE
Union	01_OZONE	0	118.2455902	PPB	FALSE
Blount3	01_CO	0	9.946322441	PPM	FALSE
Blount3	03_NOX	0	204.4359283	PPB	FALSE
Blount3	04_NO2	0	91.22018433	PPB	FALSE
Blount3	05_NO	0	113.1910095	PPB	FALSE
Sevier	04_WS	0	1.399999976	None	TRUE
Sevier	05_WD	0	90.71469879	DEG	TRUE

Queries can be more specific, or request additional fields as necessary to build your reports / web application.

Other database Views exist for other data types:

• Calibration data
• Sample Data
• Logger Channel Configuration data
• Power failure Data
• Site/Parameter configuration Data
• Precision & Accuracy Data

You can review the schema of these views in SQL Server Management Studio, or contact support@agilairecorp.com for more information.