How many minutes is agent ready by determined interval

Ian,

the way you are explaining the problem is very similar to your last question. You only need to change the table structure of my answer to fit your new requirements. Unfortunately, your sample data and expected results do not match. The sample data will return SkillTargetID 12345 as being ready for the whole day from 10am onwards.

The CASE statement inside the AgentTimings CTE needs restating to take into account that you have differing statuses deriving different calculations. The join to the AgentEventLog also needs a similar join condition to the CASE statement.

Other than these two changes, everything else should work. Here is my solution:

/* Test Data */
IF EXISTS ( SELECT  *
            FROM    sys.tables
            WHERE   name = 'AgentEventLog'
                    AND type = 'U' ) 
    BEGIN
        DROP TABLE dbo.AgentEventLog
    END

SELECT 12345 [SkillTargetID], 1 [EventID], '2011-03-20 09:59:50.000' [EventDateTime], 0 [EventDuration], CONVERT(datetime, NULL) [EventStartDateTime] INTO dbo.AgentEventLog UNION ALL 
SELECT 12345, 3, '2011-03-20 12:01:11.000', 7281, NULL UNION ALL
SELECT 12345, 3, '2011-03-20 13:26:39.003', 3153, NULL UNION ALL
SELECT 12345, 3, '2011-03-20 13:43:55.000', 1035, NULL UNION ALL
SELECT 12345, 3, '2011-03-20 15:08:56.000', 2647, NULL UNION ALL
SELECT 12345, 3, '2011-03-20 15:23:54.000', 149, NULL UNION ALL
SELECT 12345, 3, '2011-03-20 15:52:27.000', 225, NULL UNION ALL
SELECT 12345, 3, '2011-03-20 15:57:52.000', 193, NULL UNION ALL
SELECT 12345, 3, '2011-03-20 16:00:06.003', 0, NULL UNION ALL
SELECT 12345, 2, '2011-03-20 16:00:06.010', 21616, NULL

UPDATE dbo.AgentEventLog
SET EventStartDateTime = DATEADD(s, - EventDuration, EventDateTime)

DECLARE @StartOfDay datetime,
    @EndofDay datetime,
    @NumberOfMinutes int,
    @MinuteGroupings smallint

SELECT  @StartOfDay = '2011-03-20 00:00:00',
        @EndOfDay = '2011-03-20 23:59:59',
        @MinuteGroupings = 30

/* Real Work! */
;
WITH    Timings
          -- Used to create every minute of a day (1440 minutes in 24 hours) using a Numbers table (function Nums here).
          -- The Timing groups are then identified, so that we can sum the minutes per grouping 
          AS (SELECT    Timings,
                        DATEADD(MINUTE, @MinuteGroupings * (ROW_NUMBER() OVER (PARTITION BY timingMinutes ORDER BY Timings) - 1), (DATEADD(day, DATEDIFF(day, 0, @StartOfDay), 0))) timingGrp -- Create the groups
              FROM      (SELECT DATEADD(minute, (n - 1), (DATEADD(day, DATEDIFF(day, 0, @StartOfDay), 0))) Timings,
                                (n - 1) % @MinuteGroupings timingMinutes -- used to make the groups - Uses MinuteGroupings to create that.
                         FROM   dbo.Nums(DATEDIFF(MINUTE, @StartOfDay, @EndOfDay)) N) N), -- Create the minutes between start and end of day
    AgentTimings
      -- We make the relation between actual working time and the timing groups previously identified  
      -- CASE statement identifies the minutes that the agent is logged in, skipping where they are on a lunch break
      AS (SELECT    *,
                    CASE WHEN T.Timings BETWEEN A.EventStartDateTime
                                        AND     DATEADD(s,EventDuration,EventStartDateTime)
                                        AND EventId IN (1,2)
                              THEN 1
                         ELSE 0
                    END WorkingTime,
                    CASE WHEN T.Timings BETWEEN A.EventStartDateTime
                                        AND     DATEADD(s,EventDuration,EventStartDateTime)
                                        AND EventId IN (3)
                              THEN 1
                         ELSE 0
                    END                         BusyTime
          FROM      Timings T
          LEFT JOIN dbo.AgentEventLog A ON T.Timings BETWEEN A.EventStartDateTime
                                              AND    DATEADD(s,EventDuration,EventStartDateTime))
SELECT  SkillTargetID ,
        TimingGrp,
        SUM(WorkingTime) - SUM(BusyTime) AvailableMinutes
FROM    AgentTimings
WHERE SkillTargetID  IS NOT NULL
GROUP BY SkillTargetID ,
        TimingGrp

Notice that the only change is the test table definition and the definition of the AgentTimings CTE, everything else remains the same.

You will still need the PIVOT, but you have stated that is not a problem. Give this a try and let me know.

It looks to me like you're after the time spans that are implicit between your result rows. This gets those spans (in seconds rather than minutes):

;WITH source AS 
(SELECT ROW_NUMBER() OVER( order BY EventDateTime) AS rownumber, * FROM #event_list)

SELECT CASE WHEN s2.eventID = 3 THEN 
DATEDIFF(ss, s1.EventDateTime, CASE WHEN s2.EventStartDateTime > s1.EventDateTime THEN s2.EventStartDateTime ELSE s1.EventDateTime END) 
ELSE 0 END
AS availability_duration, s1.EventDateTime AS availability_start, s2.EventStartDateTime AS availability_end 
FROM source AS s1 INNER JOIN source AS s2 ON s2.rownumber = s1.rownumber + 1
WHERE s2.EventID <> 2

This query returns the following results:

availability_duration availability_start      availability_end
--------------------- ----------------------- -----------------------
0                     2011-03-20 09:59:50.000 2011-03-20 09:59:50.000
1975                  2011-03-20 12:01:11.000 2011-03-20 12:34:06.003
1                     2011-03-20 13:26:39.003 2011-03-20 13:26:40.000
2454                  2011-03-20 13:43:55.000 2011-03-20 14:24:49.000
749                   2011-03-20 15:08:56.000 2011-03-20 15:21:25.000
1488                  2011-03-20 15:23:54.000 2011-03-20 15:48:42.000
132                   2011-03-20 15:52:27.000 2011-03-20 15:54:39.000
134                   2011-03-20 15:57:52.000 2011-03-20 16:00:06.003

Here is the table setup

CREATE TABLE #event_list (
SkillTargetID INT,
EventID INT,
EventDateTime DATETIME,
EventDuration INT,
EventStartDateTime DATETIME
)
INSERT #event_list
SELECT 12345,         1,           '2011-03-20 09:59:50.000', 0,             '2011-03-20 09:59:50.000'
UNION ALL SELECT 12345,         3,           '2011-03-20 12:01:11.000', 7281,          '2011-03-20 09:59:50.000'
UNION ALL SELECT 12345,         3,           '2011-03-20 13:26:39.003', 3153,          '2011-03-20 12:34:06.003'
UNION ALL SELECT 12345,         3,           '2011-03-20 13:43:55.000', 1035,          '2011-03-20 13:26:40.000'
UNION ALL SELECT 12345,         3,           '2011-03-20 15:08:56.000', 2647,          '2011-03-20 14:24:49.000'
UNION ALL SELECT 12345,         3,           '2011-03-20 15:23:54.000', 149,          '2011-03-20 15:21:25.000'
UNION ALL SELECT 12345,         3,           '2011-03-20 15:52:27.000', 225,           '2011-03-20 15:48:42.000'
UNION ALL SELECT 12345,         3,           '2011-03-20 15:57:52.000', 193,           '2011-03-20 15:54:39.000'
UNION ALL SELECT 12345,         3,           '2011-03-20 16:00:06.003', 0,             '2011-03-20 16:00:06.003'
UNION ALL SELECT 12345,         2,           '2011-03-20 16:00:06.010', 21616,         '2011-03-20 09:59:50.010'

With luck, this will hopefully be closer to what you were expecting. The code is pretty sloppy, I'm afraid, so I'm open to suggestions on how to improve it. It should, however, work if the jobs start and end in the same day. If you have some overnight calls (start at 11 PM and finish at 1 AM, for example) or a call which lasts more than 24 hours, there would be some fidgeting with the comparisons and possibly the addition of another set of Time records for each additional day that things can run. Your examples didn't include this, though, so I coded lazily optimistically.

You can adjust the minute range (you said 30 minutes, but it sounded like that was a flexible requirement) and the begin and end times for display ranges, to keep from having too many records shown. Also, I left ReadyMinutes as an int; changing it to a decimal should be pretty easy if you do want the fractional minutes broken out as well.

declare @MinuteRange int;
set @MinuteRange = 30;
declare @BeginTime datetime;
set @BeginTime = '1900-01-01 07:00:00';
declare @EndTime datetime;
set @EndTime = '1900-01-01 18:00:00';


/* Setting up a time table.  sp_tally is a simple tally table, starting from 1. */ 
IF OBJECT_ID('tempdb..#Time') IS NOT NULL DROP TABLE #Time;

create table #Time
(
    T datetime
);

declare @sql nvarchar(max);
set @sql = N'insert into #Time select top ' 
    + cast(24 * 60 / @MinuteRange as nvarchar(4)) 
    + 'DATEADD(MINUTE, (N - 1) * @MinuteRange, ''1900-01-01 00:00:00'') from sp_tally;';
exec sp_executesql @sql, N'@MinuteRange int', @MinuteRange;

with records as
(
    select
       spans.SkillTargetID,
       spans.EventID,
       spans.EventStartDate,
       spans.T,
       (@MinuteRange * 60 - sum(EventEndTimeSecondsRemaining - EventStartTimeSecondsAlreadyElapsed)) / 60 as ReadyMinutes
    from
       (
         select 
          *,
          case
              when DATEDIFF(SECOND, t, EventStartTime) < 0 then 0
              else DATEDIFF(SECOND, t, EventStartTime)
          end as EventStartTimeSecondsAlreadyElapsed,
          case
              when DATEDIFF(SECOND, t, EventEndTime) > @MinuteRange * 60 then @MinuteRange * 60
              else DATEDIFF(SECOND, t, EventEndTime)
          end as EventEndTimeSecondsRemaining
         from 
          (
              select 
                 CAST(CAST(CAST(EventStartDateTime AS DATETIME) AS FLOAT) 
                   - FLOOR(CAST(CAST(EventStartDateTime AS DATETIME) AS FLOAT)) AS DATETIME) as EventStartTime,
                 CAST(FLOOR(CAST(CAST(EventStartDateTime AS DATETIME) AS FLOAT)) AS DATETIME) as EventStartDate,
                 CAST(CAST(CAST(EventDateTime AS DATETIME) AS FLOAT) 
                   - FLOOR(CAST(CAST(EventDateTime AS DATETIME) AS FLOAT)) AS DATETIME) as EventEndTime,
                 CAST(FLOOR(CAST(CAST(EventDateTime AS DATETIME) AS FLOAT)) AS DATETIME) as EventEndDate,
                 SkillTargetID,
                 EventID,
                 EventDateTime,
                 EventDuration,
                 EventStartDateTime
              from
                 #AgentEventLog
          ) ael
          CROSS JOIN #Time t 
         where
          t.T between ael.EventStartTime and ael.EventEndTime
          OR (t.T <= EventStartTime AND t.T <= EventEndTime AND t.T >= DATEADD(MINUTE, -@MinuteRange, EventStartTime))
       ) spans
    group by
       spans.SkillTargetID,
       spans.EventID,
       spans.EventStartDate,
       spans.T   
),
RecordedEvents as
(
    select distinct 
       SkillTargetID, 
       EventID,
       CAST(FLOOR(CAST(CAST(EventStartDateTime AS DATETIME) AS FLOAT)) AS DATETIME) as EventStartDate
    from 
       #AgentEventLog
)
select
    re.SkillTargetID,
    re.EventID,
    cast(CAST(re.EventStartDate as float) as datetime) + t.T as EventInterval,
    coalesce(r.ReadyMinutes, @MinuteRange) as ReadyMinutes
from
    #Time t
    cross join RecordedEvents re
    left outer join records r 
       on r.T = t.T 
       and re.SkillTargetID = r.SkillTargetID 
       and re.EventID = r.EventID
       and re.EventStartDate = r.EventStartDate
where
    t.T between '1900-01-01 07:00:00' and '1900-01-01 18:00:00'
order by
    re.SkillTargetID,
    re.EventID,
    t.T