wasifh

How to get attribute values at specific time stamps

Discussion created by wasifh on Oct 10, 2013
Latest reply on Oct 14, 2013 by hanyong

I am new to vCampus. I have a problem. I need to bring second attribute value of the an element at the same time stamps of the first attribute value (based on some condiyion). I know transposed query in AF server can do this but it is very time consuming as database is big as I never seen an output and get time out after 40 min. I have used the normal query to get the value of one attribute and that get value for the second attribute from the by using the In (timestamp), the problem is it only gives me value for one element at one time stamp. As I have hundreds of elements. I think it make sense to you guys.  

 

SELECT eh.Name ChargerID,c.Time, c.ValueDbl

 

FROM Database.Asset.ElementHierarchy eh

 

INNER JOIN Database.Asset.ElementAttribute ea ON ea.ElementID = eh.ElementID

 

INNER JOIN DataBase.Data.Archive c ON c.ElementAttributeID = ea.ID

 

WHERE eh.Path LIKE N'\%\'

 

AND eh.Name  like ('1%')  AND ea.Name IN ('time5E')

 

AND c.Time In ( SELECT c.Time

 

FROM Database.Asset.ElementHierarchy eh

 

INNER JOIN Database.Asset.ElementAttribute ea ON ea.ElementID = eh.ElementID

 

INNER JOIN DataBase.Data.Archive c ON c.ElementAttributeID = ea.ID

 

WHERE eh.Path LIKE N'\%\'

 

AND eh.Name LIKE N'1%'

 

AND ea.Name IN ('FAULT_CODE')

 

AND c.Time between '25/08/2013' and '*'

 

AND c.ValueDbl is not null

 

AND c.ValueDbl between 1 and 15

 

)

 

OPTION (ALLOW EXPENSIVE,FORCE ORDER,IGNORE ERRORS)

 

Here In statement return about 300 time stamp. The overall output shows only one of the time stamp value of time5E. Remember number of elements are about 50. I think he needs to know which element is related to which timestamp. Even though if I limit the query to one element, it only shows me first time stamp value of the element and neglect others.

 

It needs to be sorted in the query

Outcomes