Halenger

Help with OLEDB Provider query

Discussion created by Halenger Champion on Jul 11, 2012
Latest reply on Jul 12, 2012 by Halenger

I need help to query my AF database and show at least three colomns: Building, Gallons Today, and Gallons Yesterday in PI Web Parts.  Currently I'm displaying the Building and it's total usage in gallons for the current day by looking at two AF attributes.  See code below (derived basically from example queries in SQL commander):

 
SELECT eh.Name Building, d.Value "Gallons Today", ea.*
FROM Metering.Asset.ElementHierarchy eh
INNER JOIN Metering.Asset.ElementAttribute ea ON ea.ElementID = eh.ElementID
INNER JOIN Metering.Data.Snapshot d ON d.ElementAttributeID = ea.ID
WHERE eh.Path LIKE N'\Buildings\'
AND ea.Name LIKE N'Domestic Water Today'
order by d.value desc
OPTION (FORCE ORDER)

 

 

IN PI Web Parts, it looks like this:

 

1121.PI-Web-Part-using-OLEDB.jpg

 

 

 

I'd like to add a third column (maybe even more i.e. Week, Month usages) using the "Gallons Yesterday" attribute I have constructed in AF.  Thanks for any help/guidance!

Outcomes