I'm looking for some advice how to set up a set of queries which check for an 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 had a look into the windowing feature. I think it might only work for aggregate data for timeframe, not at a sampling point. So having this scenario:
I have a list of items with a start and an end date. I need to collect a sum of processes active at a current time. It's not about system processes, but more about something like how many craftsman were working at a given time.
Imagine tables like this (I've modified the example a little to leave out boring parts.... so might it not run 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;
This might not the best way of doing it. So I'm looking for a better approach ;)