Aggregates per half hour intervals within a time span
First, let's look at how to get all the time slices.

DECLARE @FirstDay smalldatetime = '20161128', 
        @LastDay  smalldatetime = '20161129';

SELECT TOP (24*2*DATEDIFF(DAY, @FirstDay, DATEADD(DAY,1,@LastDay)))
  DATEADD(MINUTE, 30*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1), @FirstDay)
FROM master..spt_values;


This will give you your set of 96 time slots, in half hour increments, from the 28th at midnight through the 29th at 23:30. A Numbers table is a better option than master.dbo.spt_values, but you can also use sys.all_objects - or any table really that has enough rows to cover your desired date range.

Next, let's see how to find the rows in the Events table that you care about - basically anything that passes through your window.

DECLARE @FirstDay smalldatetime = '20161128', 
        @LastDay  smalldatetime = '20161129';

SELECT EventID, EventName, StartDate, 
  EndDate = DATEADD(MILLISECOND, Duration, StartDate)
FROM dbo.Events
WHERE StartDate &gt;= DATEADD(MILLISECOND, -Duration, @FirstDay) 
  AND StartDate &lt;  DATEADD(DAY, 1, @LastDay);


Now, how to use that to get the results you're after (edited to accommodate durations that do not end on the half hour mark):

DECLARE @FirstDay smalldatetime = '20161128', 
        @LastDay  smalldatetime = '20161129';

;WITH TimeSlots(HalfHour) AS 
(
  SELECT TOP (24*2*DATEDIFF(DAY, @FirstDay, DATEADD(DAY,1,@LastDay)))
    DATEADD(MINUTE, 30*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1), @FirstDay)
  FROM master..spt_values
),
EventRange AS
(
  SELECT EventID, EventName, StartDate, 
    EndDate = DATEADD(MILLISECOND, Duration, StartDate)
  FROM dbo.Events
  WHERE StartDate &gt;= DATEADD(MILLISECOND, -Duration, @FirstDay)
    AND StartDate &lt;  DATEADD(DAY, 1, @LastDay)
),
Combo AS
(
  SELECT e.EventID, e.EventName, 
    HalfHourSlot = CONVERT(CHAR(5), CONVERT(datetime, CONVERT(time, t.HalfHour)), 108), 
    CASE
        WHEN e.StartDate &gt; DATEADD(minute, 30, t.HalfHour) OR e.EndDate &lt;= t.HalfHour
            THEN 0
        ELSE
            DATEDIFF(MINUTE, CASE WHEN e.StartDate &gt;= t.HalfHour THEN e.StartDate ELSE t.HalfHour END, CASE WHEN e.EndDate &lt;= DATEADD(minute, 30, t.HalfHour) THEN e.EndDate ELSE DATEADD(minute, 30, t.HalfHour) END)
    END AS Duration
  FROM TimeSlots AS t
  CROSS JOIN EventRange AS e
)
SELECT EventID, EventName, HalfHour = HalfHourSlot,
  SumDuration  = SUM(Duration), 
  CountClients = SUM(CASE WHEN Duration&gt;0 THEN 1 ELSE 0 END) 
FROM Combo
GROUP BY EventID, EventName, HalfHourSlot
ORDER BY EventName DESC, HalfHour;