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

[Former Member]

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.

[Former Member]

Let me try to make it more general what I want to achieve:

I've got a list with processes. Each of them is having a start and (given they have been ended up already) a end timestamp. Now I want to calculate the number of active processes at a given time.

this is quiet easy to achieve. But as you can see from my little sketch:

I'd like to do it for a given time period with a given interval. So e.g. doing a snapshot each hour or each day. So taking this example result would be something like:

P1: 2

P2: 2

P3: 4

P4: 4

P5: 4

P6: 3

[Former Member]

I will try to answer...

If you have to write own code then the accuracy of process/connection runtime would be depends on frequency of your snapshot. You will not have 100% accuracy!

Easy and accurate way would be get request level SQL logging enabled for that duration. Then load the file in satmp_request_time, query this table sum(millisecs) with group by conn_id, req_id. This is my opinion people may have different opinion.

Regards,

Abhi

[Former Member]

Thanks for the good answer, but I'm afraid its tending to a wrong direction (hard to bring my thoughts to words w/o using my monther tongue). I don't want to monitor the database. More I like to do reporting over some data:

Processes are in my example an external dataset -- something like a craftsman is working at a special task for example. Also I canot say for sure, which intervalls and which time period I need a report for -- this is depending on user's wish.

[Former Member]

Hi Frank,

No response.... looks like nobody understood what you are trying to acheive. I have gone through your post 3-4 times still have no clue what exactly you are looking for.

If you could write in simple words which would help.

Regards,

Abhijit