If anyone can solve this can you please email me..I will even send you my
two table and the inserts for some data..thanks..please email me with
questions..

This should be a real challenge, here is what I am trying to do.
I have a RAS Server on our network and a RAS manager that logs ras usage as
follows:
COMPORT     StartTime                           EndTime
COM28            02/04/98 09:35:39           02/04/98 09:36:31
COM28            02/04/98 09:38:39           02/04/98 09:45:31
COM30           02/04/98 09:35:39           02/04/98 09:36:31
etc....
I need to be able to produce some data that will tell me how many modems
where in use in each hour of the day, if a modem was on use for less than 20
minutes then we can say it was not in use !! Please note StartTime and
EndTime get logged into varchar fields, would it be easier with datetime
datatypes???

So I need data like this so that I can graph it in excel historically for a
month.
HourTime HourEndTime ModemsInUse
00:00:00   00:59:59                  4
01:00:00    01:59:59                 8
etc...

I thing I had to do was create a data table to hold  all the hours of the
day. Here is what I have so far and it is not quite working becuase of LEAST
and GREATEST.

SELECT
 HourTime,
 EndHourTime,
 ModemsInUse=
  (
  SELECT COUNT(*)
  FROM RPMSess Master
    WHERE
     (
     SELECT
       TotalUsedTime=
        SUM

(

LEAST( EndTime, EndHourTime) -

GREATEST( StartTime, HourTime)

)
     FROM RPMSess
     WHERE StartTime <= EndHourTime
     AND EndTime >= HourTime
     AND ComPort = Master.ComPort
     ) >= 20
  )    
FROM Hour;

There is a good book that I've browsed but haven't read that may help:

Optimizing Transact-Sql : Advanced Programming Techniques ~ Ships in 2-3
days
David Rozenshtein, et al / Paperback / Published 1997
www.amazon.com

I'm certainly no expert but wouldn't mind giving it a shot (if it's not a
huge hassle to send the files)
although someone here can probably tell you the answer without having to
experiment much...

-Dave

Terry,

        Interesting problem.  Here is a take on solving it.  Fails if the modem is
in continuous use for more than 24 hours.  Seems to work otherwise.

                        Russell Fields, MVP

drop table rlfRAS
go
/* Yes, use date time.  Saves a lot of converting of character strings. */
create table rlfRAS (ComPort char(5), BeginTime datetime, EndTime datetime)
go

/* Test data points */
insert into rlfRAS values('COM28','02/04/98 09:35:39','02/04/98 09:36:31')
insert into rlfRAS values('COM28','02/04/98 09:38:39','02/04/98 09:45:31')
insert into rlfRAS values('COM30','02/04/98 09:35:39','02/04/98 11:36:02')
insert into rlfRAS values('COM28','02/04/98 12:00:00','02/04/98 13:00:01')
insert into rlfRAS values('COM30','02/04/98 21:35:39','02/05/98 01:36:02')
go

/* Create twenty-four hours */
drop table #HoursTable
go
create table #HoursTable (TheHour int)
go

begin


end
go
drop table #PortSeconds
go
create table #PortSeconds (ComPort char(5), TheHour int, SecondsUsed int)
go      

Insert into #PortSeconds

select ComPort, TheHour, sum(

case
  /* Within the Hour */
  when (datepart(hour,BeginTime) = datepart(hour,EndTime)) then
    datediff(second,BeginTime,EndTime)
  else
    case
      /* Started in this hour, continues longer */
      when (datepart(hour,BeginTime) = TheHour) then
        datediff(second,BeginTime,
          dateadd(hour, TheHour + 1,convert(datetime,
          convert(char(10),BeginTime,6))))
      /* Ended in this hour, started earlier. */
      when (datepart(hour,EndTime) = TheHour) then
        datediff (second,
          dateadd(hour, TheHour,convert(datetime,
          convert(char(10),EndTime,6))), EndTime)
      /* The whole hour */
      else
        3600
    end
end
)
SecondsUsed

from rlfRAS, rlfHoursTable

where
/* The OR is needed to handle periods that span midnight */
dateadd(hour, TheHour,dateadd(hour, datepart(hour,BeginTime) * -1,
BeginTime))
between BeginTime and EndTime
or
dateadd(hour, TheHour,dateadd(hour, datepart(hour,EndTime) * -1, EndTime))
between BeginTime and EndTime
/* Condense it to total time per ComPort per Hour */
group by ComPort, TheHour

/* Condense the intermediate results to a count of ComPorts used */
select count(ComPort) PortCount, TheHour, sum(SecondsUsed) SecondsUsed from
#PortSeconds
Where SecondsUsed > 1200 /* 20 minutes */
Group by TheHour