I have templates in AF for a meter of a system (Domestic Water, Chilled, Water, Steam, etc.) as well as having a Building template. So my heirarchy is to create a building and then add a meter template as a New Child Element (the buildings vary with number of meters and systems being metered). In each meter template I have attributes like Maintenance Date and Comments... Under Comments, I have five other attributes as "Comments (1-5)" to give details of what a technician did to service the meter. Each time he goes there and services it, I've been recording what was done/found under the next Comments (1-5) attribute. So, I'd now like to beable to create a report for all the meters that have been worked on...
I've created table-valued functions for each template and can query (based off the sample queries in SQL Commander) all the attributes of a meter (template)... One meter at a time though... I'd like to query all the meters I have in my AF system depending on the meter type template to report maintance dates and info. Below is a snapshot of my AF System:
I can query a meter by giving a path to the repective building... But I want to go through all buildings and query only the Comments and Maintance Date attributes. Furthermore, when I query, I'm only retrieving the Comments attribute and none of the Comments (1-5) attributes. The code I've been tinkering with is as follows:
SELECT eh.Path + eh.Name Element, ts.* FROM Metering.Asset.ElementTemplate et INNER JOIN Metering.Asset.Element e ON e.ElementTemplateID = et.ID INNER JOIN Metering.Asset.ElementHierarchy eh ON eh.ElementID = e.ID CROSS APPLY Metering.DataT.[TransposeSnapshot_Domestic Water Meter Template] (eh.ElementID) ts WHERE eh.Path = N'\Buildings\Babbidge Library\' OPTION (FORCE ORDER< EMBED ERRORS)
Thanks for any help!