Example

SELECT
e.ID ElementID,
e.Name PIAF_ElementName,
t.Name PIAF_TemplateName,
date(FORMAT(tir.Time, 'yyyy-MM-dd')) DateKey,
time(FORMAT(tir.Time, 'HH:mm:ss')) TimeKey,
tir.[\Production Metrics\Hourly Market Value] [Hourly Market Value Value]
FROM
(
SELECT ID, Name
FROM [Ontario_Wind]..[ElementTemplate] t
WHERE t.InheritancePath like N'\Wind turbine\%' /*Inheritance: templateName*/
UNION
SELECT ID, Name
FROM [Ontario_Wind]..[ElementTemplate] t
WHERE t.Name = N'Wind turbine' /*Implementation: templateName*/
) t
INNER JOIN [Ontario_Wind]..[Element] e
ON e.ElementTemplateID = t.ID
--
-- dynamically use Transpose TVF
--
CROSS APPLY [Ontario_Wind]..[TransposeInterpolateRange]
<
N'Wind turbine' /*templateName*/,
'\' /*attributePath*/,
TRUE /*includeSubtree*/,
FALSE /*valuesAsVariant*/
>
(
e.ID,
N'9/1/2011 12:00:00 AM' /*startTime*/,
N'9/15/2011 12:00:00 AM' /*endTime*/,
N'15m' /*timeStep*/
) tir
WHERE tir.[\Production Metrics\Hourly Market Value] IS NOT NULL
OPTION (IGNORE ERRORS, FORCE ORDER)
-- Boiler attribute data summaries
SELECT eh.Path + eh.Name Element, ts.*
FROM NuGreen.Asset.ElementTemplate et
INNER JOIN NuGreen.Asset.Element e ON e.ElementTemplateID = et.ID
INNER JOIN NuGreen.Asset.ElementHierarchy eh ON eh.ElementID = e.ID
CROSS APPLY NuGreen.Data.TransposeSummarize
N'Boiler' /*element template*/,
false /*values as VARIANT*/,

{ N'\Fuel Gas Flow', Average, TimeWeighted } AS [Fuel Gas Flow Average],
{ N'\Fuel Gas Flow', Minimum, TimeWeighted } AS [Fuel Gas Flow Minimum],
{ N'\Fuel Gas Flow', Maximum, TimeWeighted } AS [Fuel Gas Flow Maximum],
{ N'\Water Flow', Average, TimeWeighted } AS [Water Flow Average],
{ N'\Water Flow', Minimum, TimeWeighted } AS [Water Flow Minimum],
{ N'\Water Flow', Maximum, TimeWeighted } AS [Water Flow Maximum],

( eh.ElementID,

N't' /*StartTime*/,
N'*' /*EndTime*/,
N'1h' /*TimeStep*/,
N'MostRecentTime' /*TimeType*/

) ts

WHERE et.Name = N'Boiler'
OPTION (FORCE ORDER, EMBED ERRORS)