and somethign from my snippets, which is grouping data into half hour intervals:

With MySampleData

(AgentID,State,StartTime,EndTime,Duration)

AS (

SELECT '1234','NotReady',Cast('2011-04-05 08:00:00' AS datetime),Cast('2011-04-05 08:00:05' AS datetime),'5' UNION ALL

SELECT '1234','Ready','2011-04-05 08:00:05','2011-04-05 08:01:00','55' UNION ALL

SELECT '1234','Busy','2011-04-05 08:01:00','2011-04-05 08:03:00','120' UNION ALL

SELECT '1234','ACW','2011-04-05 08:03:00','2011-04-05 08:03:10','10' UNION ALL

SELECT '1234','Ready','2011-04-05 08:03:10','2011-04-05 08:05:00','110' UNION ALL

SELECT '1234','NotReady','2011-04-05 08:05:00','2011-04-05 08:35:00','1800' UNION ALL

SELECT '1234','Ready','2011-04-05 08:35:00','2011-04-05 08:40:00','300'

)

, Calendar( StartTime, EndTime ) AS (

SELECT Cast('2011-04-05 07:30' AS datetime), Cast('2011-04-05 08:00' AS datetime)

UNION ALL

SELECT Cast('2011-04-05 08:00' AS datetime), Cast('2011-04-05 08:30' AS datetime)

UNION ALL

SELECT Cast('2011-04-05 08:30' AS datetime), Cast('2011-04-05 09:00' AS datetime)

)

, Interval_Details AS (

SELECT d.AgentID, c.StartTime AS Interval

, DateDiff(ss

, CASE WHEN c.StartTime > d.StartTime THEN c.StartTime ELSE d.Starttime END

, CASE WHEN c.EndTime < d.EndTime THEN c.EndTime ELSE d.Endtime END

) AS IntervalLength

, [State]

FROM MySampleData AS d

INNER JOIN Calendar AS c

ON d.StartTime < c.EndTime

AND c.StartTime < d.EndTime

)

SELECT

AgentID

, Interval

, Sum(IntervalLength) AS LoginTime

, Sum(CASE [State] WHEN 'Ready' THEN IntervalLength ELSE 0 END) AS ReadyTime

, Sum(CASE [State] WHEN 'NotReady' THEN IntervalLength ELSE 0 END) AS NotReadyTime

, Sum(CASE [State] WHEN 'ACW' THEN IntervalLength ELSE 0 END) AS ACWTime

FROM Interval_Details

GROUP BY AgentID, Interval