I'm looking for some advicse, how to setup a set of queries, which are checking for a aggregated value for sampling points within a time period. This should run at an IQ-server, so maybe not this many procedure calls would be cool 😉
I hade a look onto the windowing feature, but I think it might only working for aggregate data for timeframe, not at a sampling point. So in case I'm wrong, I'd happy to get some advice.
So having this scenario:
I'm having a list of items with a start and an end date. I need to collect a sum of processes active at a current time.
Imagine tables like this (I've modified example a little to leave out boring parts.... so might not running perfectly)
create table items (
id int ot null default autoincrement,
"Type" integer,
TimeStampStart datetime null,
TimeStampend datetime null
)
is currently used by this queryset:
create table #Processes(
"Type" integer,
"timestamp" "datetime" null,
"Sum" integer null
)
set @date = '20120303'
while @date <= '20130505'
begin
insert into #Processes
select "Type",'timestamp'=@date,'Sum'="count"()
from "items"
and "TimeStampStart" between "dateadd"("day",-"abs"(100),@date) and @date
and "TimeStampStart" <= @date
and "isnull"("TimeStampEnd",@date) >= @date
group by "Type"
set @date = "dateadd"("ss",3600,@date)
end
select * from #Processes;
Having this, might not the best way of doing it. So I'm looking for a better approach -- I hope it's not off topic here. 😉