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

sybasesybaseiq

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 😉

Best Answer

I would take answer by Micheael Green a step further and suggest generating a Numbers table. It will help many other algorythms as well. Another handy table is Calendar table with every date for +/- 20 years. You can get numbers from ID column of such table as well.

Here is a query I came up with. You can easily wrap it into SP to TVF. It works for me. I tested on my dev db so had to prefix table names with tmp.

Play with >, >=, <, <= sings to count or not those processes that start/finish on boundary.

---- Create temp tables to test
create table tmp_numbers  (
id int null
)
insert into tmp_numbers values (0)
go
insert into tmp_numbers 
select MAX(ID)+1 from tmp_numbers 
go 1000
create clustered index Idx_ID on tmp_numbers (id)
go

create table tmp_items (
    "Type" integer,
    TimeStampStart datetime null, 
    TimeStampend datetime null
)
go

insert into tmp_items
select 1,'2012-03-03 02:13:01.000','2012-03-03 15:09:05.000'
UNION ALL
select 2,'2012-03-03 07:33:59.990','2012-03-03 14:59:10.000'
UNION ALL
select 3,'2012-03-03 22:13:01.000','2012-03-04 15:09:05.000'
UNION ALL
select 4,'2012-03-04 10:33:59.990','2012-03-04 14:59:10.000'
UNION ALL
select 5,'2012-03-04 23:20:00.000','2012-03-05 02:50:00.000'
UNION ALL
select 6,'2012-03-05 12:00:00.000','2012-03-05 23:01:00.000'

------ The query itself

declare @start datetime, @end datetime
set @start = '20120303'
set @end  = '20120305'

select 
ID,
DATEADD(hour, id, @start),
DATEADD(hour, id+1, @start),
(select COUNT (*) 
    from tmp_items 
    where TimeStampStart <= DATEADD(hour, tmp_numbers.id +1, @start) 
    and TimeStampEnd > DATEADD(hour, tmp_numbers.id, @start)

    -- To limit number of Log ("items") rows scanned:
    and TimeStampEnd >= @start

)
from tmp_numbers 
where id < DATEDIFF (hh,@start, @end)+24;

Another way to organise this query to overcome single CPU limitatation

declare @start datetime, @end datetime
    set @start = '20120303'
    set @end  = '20120305'

create table #Temp (
StartTime datetime null,
EndTime datetime null,
RowNum int null)

insert into #Temp
  select 
    DATEADD(hour, id, @start),
    DATEADD(hour, id+1, @start),
  from tmp_numbers 
    where id < DATEDIFF (hh,@start, @end)+24;

 update #Temp
 set RowNum =  (select COUNT (*) 
        from tmp_items 
        where TimeStampStart <= #Temp.EndTime 
        and TimeStampEnd > #Temp.StartTime 

        -- To limit number of Log ("items") rows scanned:
        and TimeStampEnd >= @start  
    )