Calculate Utilization

[rdeleon]

I am tracking the use of a resource with the following table structure:

rID rDateTime rStatus

My status is basically "in use" (1) or "idle" (0) and a record is written each time the resource goes in and out of each status.

I am calculating the number of hours that a resource is "in use" by using a subquery to find the next time the resource was "idle" so I end up with:

rID TimeInUse
1 10
2 4
3 8
1 3
2 7
...

I need to calculate the utilization of a particular resource for various user defined time periods (day, week, month, etc) but I am having trouble because you cannot use the aggregate functions on subqueries.

Is there a better technique? Any help is appreciated.

Rene'

 

[vongrunt]

One scenario to consider: is it possible that one "in use" period spreads across two or more report periods? For example, 22:00 yesterday - 04:00 today and you want to group results by days...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[rdeleon]

Yes, that is what I am having some problem with.

So lets take your scenario and the "in use" period was from 6/1/05 22:00 thru 6/2/05 04:00. If the user wants the utilization for 6/1/05, I need to give them 2/24 for the 6/1/05 period. If they select 6/1/05 - 6/2/05, I give them 6/48.

I am playing around with a temp table, but I think I am going to have to store more data to get them the granularity they want.

Rene'

 

[vongrunt]

I don't think granularity is necessary... here is general idea:

- calculate "in use" intervals in form [dateTimeFrom - dateTimeTo)
- in temp table generate all intervals you wish to monitor (also [from-To))
- calculate intersections between these two sets and sum utilization

That way you are also not limited to single interval and including/excluding "empty" intervals is a matter of join type... everything is actually really simple. Is that what you need?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)