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!