Complex Query

Hi Guys

Hope you can help with this - it's certainly got me scratching my head.

I'm Querying a Call Centre Database

I have a Table of Call Data with a Start Time & End Time of the Call.

(CallID,Started,Ended)
10942086 2007-04-01 00:01:09.000 2007-04-01 00:11:31.000
1003855355 2007-04-01 00:01:24.000 2007-04-01 00:01:24.000
10942071 2007-04-01 00:01:25.000 2007-04-01 00:02:43.000
10942271 2007-04-01 00:02:57.000 2007-04-01 00:05:01.000
10942283 2007-04-01 00:05:54.000 2007-04-01 00:06:50.000
10942079 2007-04-01 00:07:15.000 2007-04-01 00:07:46.000
10942287 2007-04-01 00:07:30.000 2007-04-01 00:08:12.000
10942289 2007-04-01 00:07:49.000 2007-04-01 00:08:33.000
I'm trying to produce Stats that tell me how many Calls were live in any one given minute.

Ultimately I will be producing a Line Graph of No of Calls Connected grouped by Minute.

I've gone as far as creating a temp table with every minute in a month with the following query maybe to join to but not sure if this will help me.

WHILE(@.cnt <= 43200)
BEGIN
SELECT @.MaxDate =DATEADD(mi,1,MAX(DTBlock))FROM AprilMinutes
INSERTINTO AprilMinutes VALUES(@.MaxDate,NULL)
SET @.cnt = @.Cnt +1
END
Which produces a nifty little table with
01/04/2007 00:09:00
01/04/2007 00:10:00
01/04/2007 00:11:00
01/04/2007 00:12:00
01/04/2007 00:13:00
01/04/2007 00:14:00
01/04/2007 00:15:00
01/04/2007 00:16:00



If one individual Call Spans 2 minutes I'll count it as 1 in the first minute & 1 in the second minute.

Overall I'm trying analyze how many telephone lines we need

Any Help much, much appreciated

Thanks

GWselect AprilMinutes.datetimecolumn
, count(CallData.StartTime) as active_calls
from AprilMinutes
left outer
join CallData
on AprilMinutes.datetimecolumn
between CallData.StartTime
and CallData.EndTime
group
by AprilMinutes.datetimecolumn|||Tehe

Thanks r937 - Though art a veritable GURU of SQL'ness

My meager Server is crunching the TOP 100 as we speak (creak, grind)

However :eek:

What will happen if a call arrives at eg. 00:05:10 and Ends at 00:05:55

methinks it will not get counted ?

please tell me i'm wrong|||please tell me i'm wrongnope, thou art correct

here's an idea (too busy to test it meself): in the query round the start time down to the nearest previous minute, and round the end time up to the next minute, then instead of BETWEEN, use >= and <|||Whooooaa

Close but no Bannana's

The suggestion produces


(DateTimeBlock,CountOf)
2007-04-01 00:00:00.000 2
2007-04-01 00:01:00.000 4
2007-04-01 00:02:00.000 5
2007-04-01 00:03:00.000 3
2007-04-01 00:04:00.000 7
2007-04-01 00:05:00.000 8
2007-04-01 00:06:00.000 8
2007-04-01 00:07:00.000 8
2007-04-01 00:08:00.000 8
2007-04-01 00:09:00.000 7


From
(CallID,Start,End,FormatedStart,FormatedEnd)
10942086 2007-04-01 00:01:09.000 2007-04-01 00:11:31.000 2007-04-01 00:01:00.000 2007-04-01 00:12:00.000
10038553 2007-04-01 00:01:24.000 2007-04-01 00:01:24.000 2007-04-01 00:01:00.000 2007-04-01 00:02:00.000
10942071 2007-04-01 00:01:25.000 2007-04-01 00:02:43.000 2007-04-01 00:01:00.000 2007-04-01 00:03:00.000
10942271 2007-04-01 00:02:57.000 2007-04-01 00:05:01.000 2007-04-01 00:02:00.000 2007-04-01 00:05:00.000
10942283 2007-04-01 00:05:54.000 2007-04-01 00:06:50.000 2007-04-01 00:05:00.000 2007-04-01 00:07:00.000
10942079 2007-04-01 00:07:15.000 2007-04-01 00:07:46.000 2007-04-01 00:07:00.000 2007-04-01 00:08:00.000
10942287 2007-04-01 00:07:30.000 2007-04-01 00:08:12.000 2007-04-01 00:07:00.000 2007-04-01 00:09:00.000
10942289 2007-04-01 00:07:49.000 2007-04-01 00:08:33.000 2007-04-01 00:07:00.000 2007-04-01 00:09:00.000
10942090 2007-04-01 00:09:52.000 2007-04-01 00:13:40.000 2007-04-01 00:09:00.000 2007-04-01 00:14:00.000
10942094 2007-04-01 00:15:18.000 2007-04-01 00:15:45.000 2007-04-01 00:15:00.000 2007-04-01 00:16:00.000
10942306 2007-04-01 00:15:37.000 2007-04-01 00:16:26.000 2007-04-01 00:15:00.000 2007-04-01 00:17:00.000
10942104 2007-04-01 00:17:51.000 2007-04-01 00:18:29.000 2007-04-01 00:17:00.000 2007-04-01 00:19:00.000

I Estimate it should produce2007-04-01 00:00:00.000 0
2007-04-01 00:01:00.000 3
2007-04-01 00:02:00.000 3
2007-04-01 00:03:00.000 2
2007-04-01 00:04:00.000 2
2007-04-01 00:05:00.000 3

Do you think it will help if I post some code to create Test Tables n Data

I had hoped maybe someone familiar with a Call Centre / telephone type operation would have done this kind of thing.

I'm not sure why your suggestion does'nt work - I'll continue studying it.

:confused:

Thanks|||Oh NO !!

Everyone seems to have given up :eek: :eek: :eek: :eek:

Surely there's a SQL God out there who could make a constructive suggestion.

Come on Chaps OR I'll break out the CURSORS - tehe.

Only Joking.

SELECT CASE WHEN @.GoodSugestions >= 1
THEN
'Thankyou'
ELSE
'Struggle On'
END
Thanks to r937, you've brought me very close.

GW|||can you show the query you developed based on my suggestion in post #4|||Here's the RAW code exactly as used


select top 10 AprilMinutes.DTBlock
,count(callActionSummary.NotiTime)as active_calls
from AprilMinutes
left outer
join callActionSummary
on AprilMinutes.DTBlock >=LEFT(dbo.FormatDate(NotiTime,'yyyy-mm-dd hh:mm:ss.Ms'),16)+':00.000'
and AprilMinutes.DTBlock <DATEADD(mi,1,LEFT(dbo.FormatDate(DATEADD(ss,ssopco nn,NotiTime),'yyyy-mm-dd hh:mm:ss.Ms'),16)+':00')
group
by AprilMinutes.DTBlock
orderby AprilMinutes.DTBlock

Do you think possibly the Date manipulation / dbo.FormatDate is affecting it ?

ssopconn is Int (operator connected seconds)
NotiTime is Datetime (Time Call arrived)
dbo.FormatDate returns nVarChar (custom UDF) hopefuly implicit conversion to date ??

GW|||Do you think possibly the Date manipulation / dbo.FormatDate is affecting it ?it's a possibility ;)

actually, now that i think about it, you would only have to round the start time down to the current minute, to have DTBlock >= pick it up

then if ssopconn doesn't extend the time into the next minute, the call will still be counted in that minute

anyhow, there is a much better way to round down (would've mentioned it yesterday but i was too busy to look it up)

SELECT DATEADD(<datepart>, DATEDIFF(<datepart>, <refdatetime>, <datetime>), <refdatetime>)

which simply adds back the same number of whole dateparts to the reference datetime as there are between the reference datetime and the given datetime

the excess just disappears ;)

example:

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) returns midnight of first day of current month (try it :))

reference date of 0 equals the sql server zero datetime, whatever that may be, jan 1 1900 or whatever

it doesn't actually matter what the exact zero datetime is, because it subtracts back out ;)

expression for your situation:

DATEADD(mi, DATEDIFF(mi, '2000-01-01', NotiTime), '2000-01-01')

reference date is chosen closer to expected values, although zero still works

this expression rounds NotiTime down to current minute and is a lot more efficient than string manipulation

:)|||select a.datetimecolumn, count(b.StartTime)
from AprilMinutes a left join CallData b
on a.datetimecolumn between convert(varchar(17),b.StartTime ,113)
and b.EndTime --dateadd(ms,59997, convert(varchar(17),b.EndTime,113) )
group by a.datetimecolumn|||Sorry Chaps

Not had time to test these yet but will do tomorrow

Someone decided to change the CodePages on the Production SyBase servers from 850 to 1252 last night & hoped it would'nt have much of an effect - :shocked: - lol

I'll keep you posted re results of your suggestions

thanks

GW|||expression for your situation:
DATEADD(mi, DATEDIFF(mi, '2000-01-01', NotiTime), '2000-01-01')
is a lot more efficient than string manipulation
Revised queryselect a.DTBlock, count(b.StartTime)
from AprilMinutes a
left join CallData b
on a.DTBlock between dateadd(mi,datediff(mi,0,b.StartTime),0)
and b.EndTime
group by a.DTBlock|||WOW :D :D :D

r937 & pdreyer - Though art Veritable GODS of SQL ness

Worked a Treat & triple fast too.

r937 had it with the Left Outer and pdreyer swooped in with the
between dateadd(mi,datediff(mi,0,b.StartTime),0) and b.EndTime

I'm humbled in your presence - Grovel, Snivel

Many Thanks to the both of you - tis muchly appreciated

Grant|||Just to Round things Off

This is what I did with the Code


ALTERPROCEDURE LineUtilisation @.FromDate DateTime, @.ToDate DateTime
AS
SETNOCOUNTON
DECLARE @.TimeBlock TABLE(DTBlock DateTime)
DECLARE @.CallData TABLE(Call_Def Int,StartTime DateTime,EndTime DateTime)
DECLARE @.MinReq Int

INSERTINTO @.TimeBlock SELECT @.FromDate
SET @.MinReq =DATEDIFF(mi,@.FromDate,@.ToDate)
WHILE(@.MinReq > 1)
BEGIN
INSERTINTO @.TimeBlock
SELECTDATEADD(mi,1,MAX(DTBlock))FROM @.TimeBlock
SET @.MinReq = @.MinReq - 1
END
INSERTINTO @.CallData
SELECT
Call_Def,
NotiTime StartTime,
DATEADD(ss,SSTotConn,NotiTime) EndTime
FROM CallActionSummary
WHERE NotiTime between @.FromDate AND @.ToDate
ANDRTRIM(LTRIM(CallCode ))NOTIN('','B','F','4','Q','H','O','P','16')-- Background
AND ssOpConn > 0
AND ReasonRef <> 5
ORDERBY 2
SELECT a.DTBlock,COUNT(b.StartTime) CallQty
FROM @.TimeBlock a
LEFTJOIN @.CallData b
ON a.DTBlock BETWEENDATEADD(mi,DATEDIFF(mi,0,b.StartTime),0)
AND b.EndTime
GROUPBY a.DTBlock
ORDERBY 1
Will Probably end up tidying it up - poss with some indexing but it works fast enough at the Mo.

PS Mars Bar for anyone who can tell me how to LOSE THE WHILE LOOP !!

GW|||Will Probably end up tidying it up some indentation wouldn't hurt, man ;)|||I don't think you need the @.CallData table and
SELECTDATEADD(mi,1,MAX(DTBlock))FROM @.TimeBlockEvery time you select max you scan the table and the more rows you add the more there is to scan
Rather do this
select @.seq=0, @.MinReq =DATEDIFF(mi,@.FromDate,@.ToDate)
WHILE(@.seq<@.MinReq)
BEGIN
INSERT INTO @.TimeBlock SELECT DATEADD(mi,@.seq,@.FromDate)
SET @.seq = @.seq + 1
END
You could avoid the while loop if you had a numbers table e.g.
select DTBlock=dateadd(mi,numbers.seqno-1,@.FromDate)
from numbers
where seqno<=DATEDIFF(mi,@.FromDate,@.ToDate)+1

You can generate a numbers table by selecting from a large table
e.g.
select top 50000 seq=identity(int,1,1) into numbers from some_big_table

and you can handle all in a single query if you join to the numbers table|||r937 - 4 Sure - looks fine in my Query Editor but everything (including spaces) is stripped off when pasting between #Code# blocks for some reason ?.

Glad it keeps the Pretty Colours though.

Yup pdreyer - using the @.seq variable is gonna make a lot of sense - not sure about a numbers table though as I prefer not to create ancilliary/metadata tables unless I absolutely have to.

I'll have a think & post back optimized Code

Thanks Chaps

GW|||Had some time to play and test what I said before.
The left join can be a killer and it seems you need an interim table

I generated this test data

create table CallActionSummary (Call_Def int,NotiTime datetime,SSTotConn int
, CallCode char(10), ssOpConn int, ReasonRef int)
insert into CallActionSummary select
10942086 ,'2007-04-01 00:01:09.000',622 ,'ok',2,1 union all select
10038553 ,'2007-04-01 00:01:24.000',0 ,'ok',2,1 union all select
10942071 ,'2007-04-01 00:01:25.000',78 ,'ok',2,1 union all select
10942271 ,'2007-04-01 00:02:57.000',124 ,'ok',2,1 union all select
10942283 ,'2007-04-01 00:05:54.000',56 ,'ok',2,1 union all select
10942079 ,'2007-04-01 00:07:15.000',31 ,'ok',2,1 union all select
10942287 ,'2007-04-01 00:07:30.000',42 ,'ok',2,1 union all select
10942289 ,'2007-04-01 00:07:49.000',44 ,'ok',2,1 union all select
10942090 ,'2007-04-01 00:09:52.000',228 ,'ok',2,1 union all select
10942094 ,'2007-04-01 00:15:18.000',27 ,'ok',2,1 union all select
10942306 ,'2007-04-01 00:15:37.000',49 ,'ok',2,1 union all select
10942104 ,'2007-04-01 00:17:51.000',38 ,'ok',2,1 union all select
1 ,'2007-04-01 02:17:51.000',38 ,'ok',2,1 --union all select

insert into CallActionSummary select Call_Def,dateadd(hh,1,NotiTime),SSTotConn ,'ok',2,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(hh,5,NotiTime),SSTotConn ,'ok',2,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(hh,12,NotiTime),SSTotConn ,'ok',3,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,1,NotiTime),SSTotConn ,'ok',3,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,2,NotiTime),SSTotConn ,'ok',3,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,4,NotiTime),SSTotConn ,'ok',4,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,8,NotiTime),SSTotConn ,'ok',4,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,16,NotiTime),SSTotConn ,'ok',4,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(mi,30,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(mm,-2,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
insert into CallActionSummary select Call_Def,NotiTime,SSTotConn,'16',5,1 from CallActionSummary
union all select Call_Def,NotiTime,SSTotConn,'not ok',0,1 from CallActionSummary
union all select Call_Def,NotiTime,SSTotConn,'not ok',10,5 from CallActionSummary

insert into CallActionSummary select Call_Def,dateadd(mm,-4,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(yy,-1,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(yy,-4,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary

create index ix1 on CallActionSummary (NotiTime)

and then created this numbers table (You might want to include the generation of a temporary #numbers table in your proc instead)

select top 43200 seq=identity(int,1,1) into numbers
from CallActionSummary

create unique clustered index ix1 on numbers(seq)

Table sizes during test:
name rows reserved data index_size unused
------ ---- ------ ------ ------ ---
CallActionSummary 425984 28408 KB 19040 KB 9328 KB 40 KB
numbers 43200 576 KB 560 KB 16 KB 0 KB

Tried this query (1 day):

declare @.FromDate DateTime, @.ToDate DateTime
select @.FromDate='2007-04-01 00:00:00', @.ToDate='2007-04-01 23:59:59'
select dateadd(mi,seq-1,@.FromDate) DTBlock ,count(NotiTime) CallQty
into #t3 -- used to check runtime without passing data to client
from numbers t1
left join CallActionSummary t2
on t1.seq between datediff(mi,@.FromDate,NotiTime)+1
and (datediff(ss,@.FromDate,NotiTime)+SSTotConn)/60+1
and NotiTime between @.FromDate and @.ToDate
AND RTRIM(LTRIM -- rtrim,ltrim really needed?
(CallCode )) NOT IN ('','B','F','4','Q','H','O','P','16')-- Background
AND ssOpConn > 0
AND ReasonRef <> 5
where t1.seq between 1 and datediff(mi,@.FromDate,@.ToDate)+1
group by t1.seq
order by 1
drop table #t3 -- used to check runtime without passing data to client

(1440 row(s) affected)
Table 'Worktable'. Scan count 621, logical reads 831, physical reads 0, read-ahead reads 0.
Table 'CallActionSummary'. Scan count 2, logical reads 4760, physical reads 0, read-ahead reads 0.
Table 'numbers'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 817, logical reads 1027, physical reads 0, read-ahead reads 0.
Table '#t3__<snip>'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
SQL Server Execution Times: CPU time = 765 ms, elapsed time = 557 ms.

Then 10 days and expected everything times 10
but the time was much worse
and logical reads on 'Worktable' was way up (what's with the worktable?)

...
select @.FromDate='2007-04-01 00:00:00', @.ToDate='2007-04-10 23:59:59'
...
(14400 row(s) affected)
Table 'Worktable'. Scan count 6935, logical reads 43697, physical reads 0, read-ahead reads 0.
Table 'CallActionSummary'. Scan count 2, logical reads 4760, physical reads 0, read-ahead reads 0.
Table 'numbers'. Scan count 2, logical reads 25, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 7463, logical reads 46865, physical reads 0, read-ahead reads 0.
Table '#t3__<snip>'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
SQL Server Execution Times: CPU time = 37672 ms, elapsed time = 19758 ms.

And I killed the test on 30 days after waiting for more than 2 minutes
It seems you need an interim table to solve this

Changed query to:
declare @.FromDate DateTime, @.ToDate DateTime
select @.FromDate='2007-04-01 00:00:00', @.ToDate='2007-04-01 23:59:59'
select dateadd(mi,a.seq-1,@.FromDate) DTBlock, case when b.CallQty is null then 0 else b.CallQty end CallQty
into #t3 -- used to check runtime without passing data to client
from numbers a left join (
select seq, count(*) CallQty --count(NotiTime) CallQty
from numbers t1
join CallActionSummary t2 --with (index (ix1))
on t1.seq between datediff(mi,@.FromDate,NotiTime)+1
and (datediff(ss,@.FromDate,NotiTime)+SSTotConn)/60+1
and NotiTime between @.FromDate and @.ToDate
AND RTRIM(LTRIM -- rtrim,ltrim really needed?
(CallCode )) NOT IN ('','B','F','4','Q','H','O','P','16')-- Background
AND ssOpConn > 0
AND ReasonRef <> 5
where t1.seq between 1 and datediff(mi,@.FromDate,@.ToDate)+1
group by t1.seq
) b on a.seq=b.seq
where a.seq between 1 and datediff(mi,@.FromDate,@.ToDate)+1
order by 1
drop table #t3 -- used to check runtime without passing data to client

(1440 row(s) affected)
Table 'numbers'. Scan count 210, logical reads 451, physical reads 0, read-ahead reads 0.
Table 'CallActionSummary'. Scan count 2, logical reads 2380, physical reads 0, read-ahead reads 0.
Table 't3__<snip>'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
SQL Server Execution Times: CPU time = 187 ms, elapsed time = 250 ms.

No more 'Worktable', and the run for 30 days is no longer a problem

...
select @.FromDate='2007-04-01 00:00:00', @.ToDate='2007-04-30 23:59:59'
...

(43200 row(s) affected)
Table 'numbers'. Scan count 6242, logical reads 13015, physical reads 0, read-ahead reads 0.
Table 'CallActionSummary'. Scan count 2, logical reads 2380, physical reads 0, read-ahead reads 0.
Table '#t3__<snip>'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.

SQL Server Execution Times: CPU time = 719 ms, elapsed time = 752 ms.

Hope this help you to find a suitable solution for your problem|||You don't need a table with a row for every minute in your date range. As you said, for 30 days, you would need 43200 minutes (rows). You actually only need as many rows as the length of the longest call. If the longest call is 26 minutes, your derived table only needs 26 rows. And since the datatype is integer, the performance and resource usage will be no problem.
Thanks to r937, I learned how handy an "integers" table can be (as pdreyer suggested with the numbers table).
Or you could generate the table variable as your example showed. The changes I made were to declare the datetimes as smalldatetime, since you only need precision to the minute. The field in the @.TimeBlock variable is of type int. (Should have changed the variable name to match). Changed @.MinReq to @.MaxReq since it's getting its value from the Max() function. I also wasn't sure about the structure of your "CallActionSummary" table, or the WHERE NOT IN clause (background).
You'll notice that instead of showing all minutes, this method will only show the minutes during which at least one call was taking place.

ALTER PROCEDURE LineUtilisation @.FromDate smalldatetime, @.ToDate smalldatetime
AS
SET NOCOUNT ON
DECLARE @.TimeBlock TABLE (DTBlock int)
DECLARE @.CallData TABLE(Call_Def Int,StartTime smalldatetime,EndTime smalldatetime)
DECLARE @.MaxReq Int
SET @.MaxReq = (SELECT MAX(DATEDIFF(minute, StartTime, EndTime)) FROM CallData)
INSERT INTO @.TimeBlock
SELECT @.MaxReq
WHILE (@.MaxReq > 0)
BEGIN
SET @.MaxReq = @.MaxReq - 1
INSERT INTO @.TimeBlock
SELECT @.MaxReq
END
/*
Wasn't too sure about the structure of your "CallActionSummary" table
so instead referenced the "CallData" table you used earlier in your post
*/
INSERT INTO @.CallData
SELECT
CallId,StartTime,EndTime
FROM CallData
WHERE StartTime Between @.FromDate AND @.ToDate /* if you want to filter on a date range */

SELECT DATEADD(minute, a.DTBlock, b.StartTime) AS Minutes, COUNT(DATEADD(minute, a.DTBlock, b.StartTime)) AS CallQty
FROM @.TimeBlock a INNER JOIN @.CallData b
ON a.DTBlock BETWEEN 0 AND DATEDIFF(minute, b.StartTime, b.EndTime)
GROUP BY DATEADD(minute, a.DTBlock, b.StartTime)
ORDER BY Minutes|||Thanks to r937, I learned how handy an "integers" table can be (as pdreyer suggested with the numbers table).thank you :cool:|||Overall I'm trying analyze how many telephone lines we need

If this is your overall goal, then the solution is very, very simple.

This query will tell you the maximum number of concurrent phonecalls per month

select year(starttime),month(starttime),max(cnt) from
(select dv.starttime, (select count(*) from calllog l where l.starttime<=dv.starttime and l.endtime>dv.starttime) as cnt
from (select distinct starttime from calllog) dv) t
group by year(starttime),month(starttime)

You can group the starttime column to fit your need. Group by day if you want to.

This query is based on samples from the book "Inside Microsoft SQL Server 2005 T-SQL PROGRAMMING" by guru Itzik Ben-Gan|||Thanks kaffenils

But the Query as suggested
setdateformat ymd
selectDATEPART(hour,NotiTime),max(cnt)
from
(select dv.NotiTime,(selectcount(*)
from CallActionSummary l
where l.NotiTime<=dv.NotiTime andDATEADD(ss,l.SSTotConn,l.NotiTime)>dv.NotiTime
)as cnt
from(selectdistinct NotiTime from CallActionSummary
) dv
) t
where NotiTime BETWEEN'2007-05-13'AND'2007-05-14'
groupbyDATEPART(hour,NotiTime)
Took just over one Hour to run a single day sooo..... I'm gonna get my head round the other suggestions and then post back.

Thanks Guys for all your hard work - your truly going above and beyond the call on this one.

GW|||Create an index on NotiTime. That would hopefully improve performance.