Can you post DDL for your table? What datatype is TIME_IN / TIME_OUT? And
what does the data look like if, for instance, TIME_IN is 11:58:00 PM and
the call lasted for 20 minutes?

I recommend that you store times and dates together and eliminate the "date"
column.

Also, in this query are you hoping to see every 30 minute interval during
the day (including those with no activity), or just those with activity?

--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--


[quoted text, click to view]

Okay, I think this should do it for you. You should create the Intervals
table ahead of time and keep it around -- you can populate it with whatever
range you feel is appropriate for your situation. Tell me if you need
clarification on any of this:



use tempdb
go

create table intervals (interval smalldatetime primary key)
go

--create temp table of numbers
select top 200000 identity(int, 0, 1) as num
into #numbers
from pubs..authors a,
pubs..authors b,
pubs..authors c,
pubs..authors d

--set 'initial' date
declare @startdate smalldatetime
set @startdate = '20030101'

--populate intervals table
insert intervals
select dateadd(mi, num * 30, @startdate)
from #numbers
where dateadd(mi, num * 30, @startdate) >= '20030101'
and dateadd(mi, num * 30, @startdate) < '20100101'

go

CREATE TABLE [dbo].[Logins] (
[ORIG_DTS] [datetime] NOT NULL ,
[TERM_DTS] [datetime] NOT NULL ,
[AGENT_ID] [int] NOT NULL
) ON [PRIMARY]
GO

insert logins
values
('20040101 12:21:02', '20040101 12:37:05', 123)

insert logins
values
('20040101 12:38:12', '20040101 12:42:15', 123)

insert logins
values
('20040101 2:15:45', '20040101 4:20:20', 234)
GO

select logins.agent_id,
intervals.interval as intervalStart,
SUM(case when orig_dts between intervals.interval and dateadd(mi, 30,
intervals.interval)
or term_dts between intervals.interval and dateadd(mi, 30,
intervals.interval)
or intervals.interval between orig_dts and term_dts then
--started in this interval
case when orig_dts >= intervals.interval then
--ends in this interval
case when term_dts < dateadd(mi, 30, intervals.interval) then
datediff(mi, orig_dts, term_dts)
--ends in a later interval
else
datediff(mi, orig_dts, dateadd(mi, 30, intervals.interval))
end
--started in previous interval
when orig_dts < intervals.interval then
--ends in this interval
case when term_dts < dateadd(mi, 30, intervals.interval) then
datediff(mi, intervals.interval, term_dts)
--ends in a later interval
else
datediff(mi, intervals.interval, dateadd(mi, 30, intervals.interval))
end
else 0
end
else 0
end)
from logins
cross join intervals
where intervals.interval >= '20040101' and intervals.interval < '20040102'
group by logins.agent_id,
intervals.interval
order by logins.agent_id,
intervals.interval
GO

drop table logins
drop table intervals
drop table #numbers
GO



--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--


[quoted text, click to view]