AnsweredAssumed Answered

SQL for a lesson, request check if OK please

Question asked by Steve_Goodwin on Sep 30, 2019
Latest reply on Oct 1, 2019 by Steve_Goodwin

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).


  1. 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
  2. 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.
  3. 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],
       vUnit.[Process Feedrate_Minimum],
       vUnit.[Process Feedrate_Maximum],
       vUnit.[Process Feedrate_Total],
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'
) vUnit

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
) vPlant

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