I have been playing around with the query for the "ProcessFeedrate min, max, and total for all equipment and show plant average uptime and quality, for the same time period" from the lessons ("Table-Value function" version) and have some differences.
I'd appreciate it if you could comment on whether you see any problems with it... mainly it's the following things that are are different ... (I have 2018R2 installed ... and because much seems common in attribute values it seems tricky to check that everything makes sense).
- I am using PrimaryPath from the element and checking in the hierarchy (IsPrimaryPath) to exclude any "Element References" because including them would (I think) just duplicate information
- I have removed any sub selects, collected unit id and plant id in the main query, and doing both "cross apply"s at the end of the query.
- I have selected units based on whether their template has a base template called "Unit" ... this would not pick up any units where the template hierarchy is more complex but it does mean that any new templates set up based on Unit would get reported automatically. That means I'm joining to ElementTemplate twice in the same query but it seems to be fine. Maybe to simplify, I could use Master.Element.FindElements('Template:"Unit"') to just find all the relevant elements.
SQL listed below... any thoughts ?
SELECT eUnit.PrimaryPath + eUnit.Name as [FullPrimaryName],
FROM [Master].[Element].[Element] eUnit
inner join [Master].[Element].[ElementHierarchy] ehUnit
on ehUnit.ElementID = eUnit.ID
inner join [Master].[Element].[Element] ePlant
on ePlant.Template = 'Plant'
inner join ElementTemplate etUnit
on etUnit.ID = eUnit.TemplateID
inner join ElementTemplate etUnitBase
on etUnit.BaseID = etUnitBase.ID
CROSS APPLY [StevesCatalogue].[Equipment].[Unit_GetSummary]
N'2019-09-19', -- intermittent values in my test system, would otherwise be 'y'
N'2019-09-20' -- ditto but 'y+1d'
CROSS APPLY [StevesCatalogue].[Equipment].[Plant_GetSummaries]
ePlant.ID, -- @AttributeID
N'2019-09-19 12:00:00', -- modified to get different values to display (things are SINUSOID based I suspect)
N'1d' -- @TimeStep
where ehUnit.[IsPrimaryPath] = true
and etUnitBase.Name = 'Unit'
and eUnit.PrimaryPath like ePlant.PrimaryPath + ePlant.Name + '%' -- select based on unit hierarchy
order by [FullPrimaryName] asc