title: Group table into 15 minute intervals
body: <p>T-SQL, SQL Server 2008 and up</p>
  
  <p>Given a sample table of</p>
  
  <pre><code> StatusSetDateTime   | UserID | Status    | StatusEndDateTime   | StatusDuration(in seconds)
  ============================================================================
   2012-01-01 12:00:00 | myID   | Available | 2012-01-01 13:00:00 | 3600
  </code></pre>
  
  <p>I need to break that down into a view that uses 15 minute intervals for example:</p>
  
  <pre><code>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....
  </code></pre>
  
  <p>Now I've been able to search around and find some queries that will break down 
  I found something similar for MySql <a href="https://stackoverflow.com/questions/2793994/group-mysql-query-by-15-min-intervals">Here</a> : </p>
  
  <p>And something for T-SQL <a href="http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/interesting-t-sql-problems" rel="nofollow noreferrer">Here</a></p>
  
  <p>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.</p>
  
  <p>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).</p>
  
  <p><strong>Thanks in advance for any insight!</strong></p>
  
  <p><strong>edit : First Attempt</strong></p>
  
  <pre><code> ;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
  </code></pre>
  
  <p><strong>edit : Second Attempt</strong></p>
  
  <pre><code>;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;
  </code></pre>