Sign in or 
| Version | User | Scope of changes |
|---|---|---|
| May 13 2010, 12:49 PM EDT (current) | sdrevik | 427 words added |
| May 13 2010, 12:48 PM EDT | sdrevik |
Changes
Key: Additions Deletions
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:
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:
You can review the schema of these views in SQL Server Management Studio, or contact support@agilairecorp.com for more information.
· 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.
