AnsweredAssumed Answered

finding the newest event after some date

Question asked by dpilc on Nov 21, 2016
Latest reply on Nov 22, 2016 by gachen


I am working on a query that will return me a set of tag,value,_index columns containing one row per tag. Value is the last value of a specific tag after an arbitrary date and I would also need _index colum from picomp2. As a starting point I created something like:


SELECT pi1.tag,pi2.tag,pi1.time,pi2.mintime,pi1.value,pi1._index FROM [piarchive]..[picomp2] pi1 join (select tag, min(time) mintime FROM [piarchive]..[picomp2]

where tag like '%MeterReading' and time >='2016-01-01'  group by tag) pi2 on pi1.tag=pi2.tag and pi1.time=pi2.mintime


The question is if there are better ways to achieve my goal (I will have around 5k meter reading tags and the data will be relatively sparse like one value per 5-7 mins)


Thank you in advance!