Querying metadata with the latest value

Extended properties can be included in History queries with the TaxExtendedPropertyInfo view. This view returns the localized version of the alias if one is defined for the current SQL Server connection language (e.g. the @@LANGID). The localized alias comes from the _TagLocalizedProperty table. If no localized alias is defined, the general alias from the _TagExtendedProperty table is used.

The following is an example with the Alias extended property:

SELECT Alias=isnull(i.PropertyValue,h.TagName), h.*

FROM History h

LEFT OUTER JOIN TagExtendedPropertyInfo i

ON i.TagName = h.TagName and i.PropertyName='Alias'

WHERE h.DateTime > '2023-08-07 0:00'

AND h.DateTime < '2023-08-07 5:00'

AND wwRetrievalMode='delta'

AND h.TagName='FI101.PV'

If the alias is not configured, you can instead use the ISNULL() function to use the Tag Name.

To include rows even when there isn't a matching extended property defined, use the LEFT OUTER JOIN clause

The following is an example of a query with multiple extended properties:

SELECT Alias=isnull(i.Alias,h.TagName), Location, h.*

FROM History h

LEFT OUTER JOIN

(

SELECT TagName, PropertyName, PropertyValue

FROM TagExtendedPropertyInfo) temp

PIVOT

( max(PropertyValue)

FOR PropertyName in ( [Location], [Dimension], [Alias], [Geolocation] )

) i

ON i.TagName = l.TagName

WHERE h.DateTime > '2023-12-11 0:00'

AND h.DateTime < '2023-12-11 10:00'

AND wwRetrievalMode='delta'

AND h.TagName='SysTimeSec'

ORDER BY TagName