title: Receive a new formatted table via aggregation and group by
body: <p>I am having a big issue with a SQL Server query here and I really don't know how to go on with it.</p>
  
  <p>The aim is to receive a table differentiated by different time-intervals going from <code>00:00 - 00:29</code> to <code>23:30 - 23:59</code>. In each of these intervals I want to sum up the total minutes of entities which waited during these times. This information can be received by a <code>starttime</code>, and <code>endtime</code> and the status of the entity, which looks like this:</p>
  
  <pre><code>startdate                    | finishdate                   | resourcestatus | id
  2015-03-19 10:22:56.8490000  | 2015-03-19 10:32:56.8490000  | 8              | asdsdasdsad
  </code></pre>
  
  <p>As you see such an entity can have the status 8 from one interval (10:00 - 10:30) into another (10:30 - 11:00). </p>
  
  <p>Until now I solved this by defining 4 groups of time-intervals (finish and start are both in interval, start out of interval but finish in, start in interval but finish out, both start and finish out of interval) these 4 groups are joined by the time-intervals.</p>
  
  <p>I would post the code here but it is too much. My result looks like this. Here are the beginnings of the different parts of the query:</p>
  
  <pre><code>select  zr.nr, 
          zr.interval, 
          case when outOfInterval.waittime is not null
                      then SUM(outOfInterval.waittime) 
                      else 0
                 end 
                 + 
                 case when inInterval.waittime is not null 
                      then SUM(inInterval.waittime)
                      else 0
                 end 
                 +
                 case when startInInterval.waittime is not null 
                      then SUM(startInInterval.waittime) 
                      else 0
                 end 
                 +
                 case when finishInInterval.waittime is not null 
                      then sum(finishInInterval.waittime)
                      else 0
                 end
                 as waitingMinutes
  From    (select 1 as nr,'00:00 - 00:29' as interval, 0 as waittime
          union  select 2,'00:30 - 00:59', 0 
          union  select 3,'01:00 - 01:29', 0 ...
          ) zr
  left join (select case when CONVERT(time, rt.startedat, 8) &lt; '00:00' and CONVERT(time, rt.finishedat , 8) &gt; '00:30'  then '00:00 - 00:29' end as inter, 30 as waittime from T_resourcetracking rt where rt.resource_id is not null and rt.resourcestatus = 8 AND  CONVERT(Date, rt.startedat) &gt;= '02.02.2015' AND  CONVERT(Date, rt.finishedat) &lt; DateAdd(day,1,CONVERT ( datetime , '08.05.2015', 120 ))           
  ...
  ) outOfInterval on outOfInterval.inter = zr.interval
  
  left join (select case when CONVERT(time, rt.startedat, 8) &gt;= '00:00' and CONVERT(time, rt.finishedat , 8) &lt;= '00:30'  then '00:00 - 00:29' end as inter, SUM(DATEDIFF(minute, rt.STARTEDAT, rt.FINISHEDAT)) as waittime from T_resourcetracking rt where rt.resource_id is not null and rt.resourcestatus = 8 AND  CONVERT(Date, rt.startedat) &gt;= '02.02.2015' AND  CONVERT(Date, rt.finishedat) &lt;= DateAdd(day,1,CONVERT ( datetime , '08.05.2015', 120 )) group by rt.startedat, rt.finishedat        
  ...
  ) inInterval on inInterval.inter = zr.interval
  
  left join (select case when CONVERT(time, rt.startedat, 8) &gt;= '00:00' and CONVERT(time, rt.startedat, 8) &lt; '00:30'and CONVERT(time, rt.finishedat , 8) &gt;= '00:30'  then '00:00 - 00:29' end as inter, (30-DATEPART(minute, rt.STARTEDAT)) as waittime from T_resourcetracking rt where rt.resource_id is not null and rt.resourcestatus = 8 AND  CONVERT(Date, rt.startedat) &gt;= '02.02.2015' AND  CONVERT(Date, rt.finishedat) &lt;= DateAdd(day,1,CONVERT ( datetime , '08.05.2015', 120 )) group by rt.startedat, rt.finishedat       
  ...
  ) startInInterval on startInInterval.inter = zr.interval
  
  left join (select case when CONVERT(time, rt.startedat, 8) &gt;= '00:00' and CONVERT(time,rt.finishedat, 8) &lt; '00:30'and CONVERT(time, rt.STARTEDAT , 8) &lt; '00:00'  then '00:00 - 00:29' end as inter,  DATEPART(minute, rt.finishedat) as waittime from T_resourcetracking rt where rt.resource_id is not null and rt.resourcestatus = 8 AND  CONVERT(Date, rt.startedat) &gt;= '02.02.2015' AND  CONVERT(Date, rt.finishedat) &lt;= DateAdd(day,1,CONVERT ( datetime , '08.05.2015', 120 )) group by rt.startedat, rt.finishedat        
  ...
  ) finishInInterval on finishInInterval.inter = zr.interval
  group by zr.interval, outOfInterval.waittime, inInterval.waittime, startInInterval.waittime, finishInInterval.waittime, zr.nr   
  </code></pre>
  
  <p>And this is the result:</p>
  
  <pre><code>    nr | interval      | waitingMinutes
      1  | 00:00 - 00:29 | 2
      2  | 00:30 - 00:59 | 7
  ...
      24 | 11:30 - 11:59 | 8
      24 | 11:30 - 11:59 | 51
  ...
  </code></pre>
  
  <p>So as you see I have more then one of an interval in my result set. </p>
  
  <p>Do you have any idea how to join the groups to one and sum the minutes up? I am really done with it, every kind of aggregate function did not work for me.</p>
  
  <p>Thanks in advance!</p>
  
  <p>@EDIT: If this was not difficult enough we need a second specification which I forgot to explain: We do not want to see all waitingtimes during the 48 time-intervals but the SUM of all those within a specific date-interval.</p>
  
  <p>Let's say we want to know the summed up minutes from the last month. Then the result set should look like:</p>
  
  <pre><code>    nr | interval      | waitingMinutes
      1  | 00:00 - 00:29 | 0
      2  | 00:30 - 00:59 | 0
  ...
      20 | 09:30 - 09:59 | 0
      21 | 10:00 - 10:29 | 8
      22 | 10:30 - 10:59 | 73
      23 | 11:00 - 11:29 | 20
  ...
  </code></pre>
  
  <p>The minutes are summed up over all time-intervals of the last month. So for example from 11:00 - 11:29 in the last 30 days the entities waited 20 minutes in total (e.g. yesterday 10 minutes and the day before 10 minutes).</p>
  
  <p>This is so difficult that I have really no clue anymore thinking that this is too much for SQL...</p>
  
  <p>Any suggestions?</p>