Technical FAQs

Ask a Question

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

Issue
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.

Environment
Query Editor, SQL Server Management Studio (SSMS)

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

Resolution
*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).

USE ION_Data
 
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"
 
FROM DataLog2 WITH (NOLOCK)
 
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
OPTION (FAST 50)


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 ?