Skip to Content
0
Former Member
Apr 08, 2014 at 03:26 PM

What's the best way to get data for sampling points within a timeframe

41 Views

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. 😉