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

Question

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 ;)

Answer 1

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  
    )

Answer 2

Frian,

I discussed this algorithm with a colleague last week. I haven't had a chance to test it yet, but we have confidence in it.

1) Convert the start and end times into a number of hours. This can be the HOURS() function or DATEDIFF. The baseline can be anything, but MIN(TimeStampStart) would be most convenient. Round TimeStampStart up and TimeStampEnd down. Let's call the resulting column HoursStart and HoursEnd.

2) Join to an Integers table where Integer.Number >= HoursStart and Integer.Number <= HoursEnd. Let's call Integer.Number "HourSampled" from now on. The integer table will be bigger or smaller depending on the baseline chosen for convertion to hours above. Create a new column called nnn (or anything else you choose) with a constant value 1.

3) Group by Type & HourSampled summing nnn into column Sum. HourSampled can be converted back to a DateTimeSampled if required.

Although I've laid this out in separate steps it can be coded in a single statement. This will be faster than your looping algorithm.

To change the sampling frequency change the function converting TimeStampStart to an integer and the corresponding content of your Integer table. For example, quarter hours would be MINUTES(TimeStampStart)/15.

I'm sorry but my Sybase knowledge is minimal. I've tried to reference the correct syntax where I can.

For added amusement you can select

select
    Type,
    REPLICATE(Sum)
from #Process
order by
    Type,
    HourSampled

To get a ready-made ASCII bar graph!