Group table into 15 minute intervals

Question

T-SQL, SQL Server 2008 and up

Given a sample table of

 StatusSetDateTime   | UserID | Status    | StatusEndDateTime   | StatusDuration(in seconds)
============================================================================
 2012-01-01 12:00:00 | myID   | Available | 2012-01-01 13:00:00 | 3600

I need to break that down into a view that uses 15 minute intervals for example:

IntervalStart       | UserID | Status | Duration

===========================================

2012-01-01 12:00:00 | myID | Available | 900 

2012-01-01 12:15:00 | myID | Available | 900

2012-01-01 12:30:00 | myID | Available | 900 

2012-01-01 12:45:00 | myID | Available | 900 

2012-01-01 13:00:00 | myID | Available | 0

etc....

Now I've been able to search around and find some queries that will break down I found something similar for MySql Here :

And something for T-SQL Here

But on the second example they are summing the results whereas I need to divide the total duration by the interval time (900 seconds) by user by status.

I was able to adapt the examples in the second link to split everything into intervals but the total duration time is returned and I cannot quite figure out how to get the Interval durations to split (and still sum up to the total original duration).

Thanks in advance for any insight!

edit : First Attempt

 ;with cte as 
    (select MIN(StatusDateTime) as MinDate
          , MAX(StatusDateTime) as MaxDate
          , convert(varchar(14),StatusDateTime, 120) as StartDate
          , DATEPART(minute, StatusDateTime) /15 as GroupID
          , UserID
          , StatusKey
          , avg(StateDuration) as AvgAmount
     from AgentActivityLog
     group by convert(varchar(14),StatusDateTime, 120)
         , DATEPART(minute, StatusDateTime) /15
         , Userid,StatusKey)

  select dateadd(minute, 15*GroupID, CONVERT(datetime,StartDate+'00'))
         as [Start Date]
       , UserID, StatusKey, AvgAmount as [Average Amount]
  from cte

edit : Second Attempt

;With cte As
   (Select DateAdd(minute
                   , 15 * (DateDiff(minute, '20000101', StatusDateTime) / 15)
                   , '20000101') As StatusDateTime
         , userid, statuskey, StateDuration
    From AgentActivityLog)

 Select StatusDateTime, userid,statuskey,Avg(StateDuration)
 From cte
 Group By StatusDateTime,userid,statuskey;

Solution 1

;with cte_max as 
(
   select dateadd(mi, -15, max(StatusEndDateTime)) as EndTime, min(StatusSetDateTime) as StartTime
   from AgentActivityLog
), times as
(
    select StartTime as Time from cte_max
    union all
    select dateadd(mi, 15, c.Time)
    from times as c
        cross join cte_max as cm
    where c.Time <= cm.EndTime
)
select
    t.Time, A.UserID, A.Status,
    case
        when t.Time = A.StatusEndDateTime then 0
        else A.StatusDuration / (count(*) over (partition by A.StatusSetDateTime, A.UserID, A.Status) - 1)
    end as Duration
from AgentActivityLog as A
    left outer join times as t on t.Time >= A.StatusSetDateTime and t.Time <= A.StatusEndDateTime

sql fiddle demo

Solution 2

I've never been comfortable with using date math to split things up into partitions. It seems like there are all kinds of pitfalls to fall into.

What I prefer to do is to create a table (pre-defined, table-valued function, table variable) where there's one row for each date partition range. The table-valued function approach is particularly useful because you can build it for arbitrary ranges and partition sizes as you need. Then, you can join to this table to split things out.

paritionid starttime     endtime
---------- ------------- -------------
1          8/1/2012 5:00 8/1/2012 5:15
2          8/1/2012 5:15 8/1/2012 5:30
...

I can't speak to the performance of this method, but I find the queries are much more intuitive.

Solution 3

It is relatively simple if you have a helper table with every 15-minute timestamp, which you JOIN to your base table via BETWEEN. You can build the helper table on the fly or keep it permanently in your database. Simple for the next guy at your company to figure out too:

// declare a table and a timestamp variable
declare @timetbl table(t datetime)
declare @t datetime

// set the first timestamp
set @t = '2012-01-01 00:00:00'

// set the last timestamp, can easily be extended to cover many years
while @t <= '2013-01-01'
begin
    // populate the table with a new row, every 15 minutes
    insert into @timetbl values (@t)
    set @t = dateadd(mi, 15, @t)
end


// now the Select query:
select 
   tt.t, aal.UserID, aal.Status,
   case when aal.StatusEndDateTime <= tt.t then 0 else 900 end as Duration
   // using a shortcut for Duration, based on your comment that Start/End are always on the quarter-hour, and thus always 900 seconds or zero

from 
   @timetbl tt 
      INNER JOIN AgentActivityLog aal 
         on tt.t between aal.StatusSetDateTime and aal.StatusEndDateTime

order by
  aal.UserID, tt.t