Aggregating Durational Event Data

[VLG711]

Hi,
I need some help with aggregating some data. I have a table that contains records of durational events where each record has a SEGSTART and SEGSTOP field. These events occur against a piece of equipment that can only handle a maximum of 24 events at any one time. So I need to know how I can find out the percentage of time the equipment was maxed out so I can determine if I need to increase its capacity.

Up to this point I have written the below query (Go easy I'm learning) that loops through each second and compares the >segstart and <segstop.

declare @CallHour int
declare @CallSec int
declare @ROW_DATE int
declare @CallBeginDate datetime
declare @Dur_Hour as int


Set @CallBeginDate= '2007-02-08 11:30:00.000' --Enter Start date and Time for the Report.
set @Dur_Hour = 1 --Enter the Duration in hours you want the report to run.
Set @CallHour = 0
Set @CallSec = 0
Set @ROW_DATE = 0

Set @CallBeginDate= dateadd(hh,datediff(hh, getdate(), getutcdate()),@CallBeginDate) -- Convert to GMT

While @CallHour < @Dur_Hour
Begin
SET @ROW_DATE = convert(int, Convert(varchar,datepart(yyyy, @CallBeginDate))
+ '' + CASE WHEN Convert(int,datepart(mm, @CallBeginDate))<=9 THEN '0' + Convert(varchar,datepart(mm, @CallBeginDate)) ELSE Convert(varchar,datepart(mm, @CallBeginDate)) END
+ '' + CASE WHEN Convert(int,datepart(dd, @CallBeginDate))<=9 THEN '0' + Convert(varchar,datepart(dd, @CallBeginDate)) ELSE Convert(varchar,datepart(dd, @CallBeginDate)) END
+ '' + CASE WHEN Convert(int,datepart(hh, @CallBeginDate))<=9 THEN '0' + Convert(varchar,datepart(hh, @CallBeginDate)) ELSE Convert(varchar,datepart(hh, @CallBeginDate)) END)

While @CallSec < 1800
Begin
SET NOCOUNT ON
select dateadd(hh,datediff(hh, getutcdate(), getdate()),@CallBeginDate) as time, count (UCID) as Port_Usage, avg(duration)as Avg_Duration,
max(duration)as Max_Duration, min(duration)as Min_Duration from user.PortDetail
where sourceID = 1
and DISPSPLIT = 518
and ROWDATE = @ROW_DATE
and SEGSTART <= @CallBeginDate
and SEGSTOP >= @CallBeginDate
--Print @CallSec
Set @CallSec = @CallSec + 1
set @CallBeginDate = dateadd(ss, 1, @CallBeginDate)
End
set @CallSec = 0
Set @CallHour = @CallHour + 1
--Print @CallHour
End

Which outputs .....


time Port_Usage Avg_Duration Max_Duration Min_Duration
------------------------ ----------- ------------ ------------ ------------
2007-02-08 11:30:00.000 1 171 171 171

time Port_Usage Avg_Duration Max_Duration Min_Duration
------------------------ ----------- ------------ ------------ ------------
2007-02-08 11:30:01.000 1 171 171 171

While this works I wind up with a huge amount of data that I have to move into excel so I can graph (not a good solution).


I'm hoping to be able to develop a query that would aggregate the data and output something like this..

Interval %Busy
-------- -----
9:00 50% ( between 9:00 and 9:30 the box was filled to capacity for 900 seconds)
9:30 75% ( between 9:30 and 10:00 the box was filled to capacity for 1350 seconds)

... But I'm not sure what my next step is I've looked for books and online but cannot find anything that helps. If anyone could point me in a direction I would be very appreciative. I should also mention I am only a user on this database with query Manager and do not have write access to the DB.

Thanks in advance,

VLG711