CODEWITHSUNDEEP
sqlsql-server
Lime3003
Lime3003

Reputation: 123

Simplify SQL query

Wondering if there is a better looking way to get this information?

while (t0 < t1 ) do

    tag1Val=(select avg from aggregates where name= tag1 and ts between t0-03:00 and t0 and period=01:00);
    tag2Val=(select avg from aggregates where name=tag2 and ts between t0-03:00 and t0 and period=01:00);
    tagRes = ((1000*tag1Val)/tag2Val);
    if tagRes not like '' then
        insert into module.ddtemp(ts,value) values (t0, tagRes);
    end;
    t0 = t0+03:00;
end;

Upvotes: 0

Views: 66

Answers (1)

StanislavL
StanislavL

Reputation: 57381

select avg 
from aggregates,
     (select t0 as period
      union all
      select t0+03:00  as period
      union all
      select t0+06:00  as period
...
      union all
      select t1 as period) periods 
where name=tag2 
  and ts between periods.period -03:00  and periods.period
  and period=01:00

You can pass all periods in a subquery and extract the result just once.

Will be slow if there are a lot of intervals.

You can extract all 

select avg 
from aggregates 
where name= tag1 and ts between t0-03:00 and t1 and period=01:00

and go through the results extracting data for each interval

Upvotes: 1

Related Questions