Technical FAQs

Ask a Question

A SQL Query to Return PME Historical Data in Tabular Format - Crosstab Query

Writing a simple query to view historical data returns data in a single column which makes it difficult to analyze.

Product Line
Power Monitoring Expert 8.0 Base/Healthcare/Data Center/Buildings
Power Monitoring Expert 7.2.x Base/Healthcare/Data Center
Struxureware Power Monitoring 7.0.x
Does not apply to ION Enterprise 6.0 and earlier versions due to table structure.

Query Editor, SQL Server Management Studio (SSMS)

A user would like to obtain data directly from the database in a tabular format.

*Note: This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

The following query returns data for two sources (identified by their sourceids) and two quantities (identified by their quantityids).

SELECT TimestampUTC  AS timestamp, SourceID,
  MAX(CASE WHEN QuantityID=129 THEN [Value] END)  AS "kWh del",
  MAX(CASE WHEN QuantityID=91 THEN [Value] END)  AS "kVARh del"
WHERE SourceID in (8, 10)
  AND QuantityID in (129, 91)
  AND (TimestampUTC > '2014-11-11 15:00') AND (TimestampUTC < '2014-11-11 16:00')
GROUP BY SourceID, TimestampUTC

ORDER BY sourceid ASC, TimestampUTC DESC

The result looks like below; note that the quantity data is returned in separate columns:


Modify the SourceIDs and QuantitlyIDs with the desired sourceids as specified in Source and Quantity tables of the ION_Data database.

Was this helpful?
What can we do to improve the information ?