title: TSQL Grouping by time with Start and End dates
body: <p>I have been struggling to make a set of reports that gives the sum of calories burned in 15 minute increments for "Teams" and "Users. </p>
  
  <p>The tables I am using are as follows:</p>
  
  <pre><code>IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Teams') AND  OBJECTPROPERTY(id, 'IsTable') = 1)
  DROP TABLE Teams;
  
  CREATE TABLE Teams
  (
  Team_ID int NOT NULL PRIMARY KEY IDENTITY (1,1),
  Team_Name varchar(100),
  Team_Description varchar(200)
  );
  
  IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Users') AND  OBJECTPROPERTY(id, 'IsTable') = 1)
  DROP TABLE Users;
  
  CREATE TABLE Users
  (
  User_ID int NOT NULL PRIMARY KEY IDENTITY (1,1),
  User_Name varchar(100)
  );
  
  IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Team_User') AND  OBJECTPROPERTY(id, 'IsTable') = 1)
  DROP TABLE Team_User;
  
  CREATE TABLE Team_User
  (
  Team_ID int,
  User_ID int
  );
  
  IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Workouts') AND  OBJECTPROPERTY(id, 'IsTable') = 1)
  DROP TABLE Workouts;
  
  CREATE TABLE Workouts
  (
  Workout_ID int NOT NULL PRIMARY KEY IDENTITY (1,1),
  User_ID int,
  Workout_Type int,
  Start_Time datetime,
  End_Time datetime,
  Calories float
  );
  
  IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('LK_Workout_Type') AND  OBJECTPROPERTY(id, 'IsTable') = 1)
  DROP TABLE LK_Workout_Type;
  
  CREATE TABLE LK_Workout_Type
  (
  WT_ID int NOT NULL PRIMARY KEY IDENTITY (1,1),
  Workout_Name varchar (80),
  Workout_Description varchar(200)
  );
  
  INSERT INTO Teams (Team_Name, Team_Description) VALUES ('A Team', 'We are not vets.');
  INSERT INTO Teams (Team_Name, Team_Description) VALUES ('Team Solo', 'One man. One team.');
  
  INSERT INTO Users (User_Name) VALUES ('Frank Appleton');
  INSERT INTO Users (User_Name) VALUES ('Tim Murdock');
  INSERT INTO Users (User_Name) VALUES ('Tim Smith');
  
  INSERT INTO Team_User (Team_ID, User_ID) Values (1,1);
  INSERT INTO Team_User (Team_ID, User_ID) Values (1,2);
  INSERT INTO Team_User (Team_ID, User_ID) Values (2,3);
  
  INSERT INTO LK_Workout_Type (Workout_Name, Workout_Description) VALUES ('Bike - Mountain','Trail riding.');
  INSERT INTO LK_Workout_Type (Workout_Name, Workout_Description) VALUES ('Bike - Road','Road riding.');
  INSERT INTO LK_Workout_Type (Workout_Name, Workout_Description) VALUES ('Elliptical','Standard elliptical');
  INSERT INTO LK_Workout_Type (Workout_Name, Workout_Description) VALUES ('Running','Typical running');
  INSERT INTO LK_Workout_Type (Workout_Name, Workout_Description) VALUES ('Treadmill','Treadmill running');
  INSERT INTO LK_Workout_Type (Workout_Name, Workout_Description) VALUES ('Weights','Weightroom');
  
  INSERT INTO Workouts (User_ID, Workout_Type, Start_Time, End_Time, Calories) VALUES (1,1,'3/10/2012 08:00:00 AM','3/10/2012 09:30:00 AM', 860);
  INSERT INTO Workouts (User_ID, Workout_Type, Start_Time, End_Time, Calories) VALUES (2,1,'3/10/2012 08:30:00 AM','3/10/2012 10:45:00 AM', 950);
  INSERT INTO Workouts (User_ID, Workout_Type, Start_Time, End_Time, Calories) VALUES (3,5,'3/10/2012 10:05:00 AM','3/10/2012 12:27:00 PM', 1917);
  INSERT INTO Workouts (User_ID, Workout_Type, Start_Time, End_Time, Calories) VALUES (1,5,'3/10/2012 02:38:00 PM','3/10/2012 03:17:00 PM', 536);
  </code></pre>
  
  <p>One of the reports I want to generate displays the calories burned by a given team, in specific increments and time frame. So, If I want to display the A-Team's calories burned from 8 AM to 6 PM on a given day in 15 minute increments, the report should look like this:<br>
      TheDt         Team     Calories<br>
      08:00:00 AM   A-Team   860<br>
      08:15:00 AM   A-Team   860<br>
      08:30:00 AM   A-Team   1810<br>
      08:45:00 AM   A-Team   1810<br>
      09:00:00 AM   A-Team   1810<br>
      09:15:00 AM   A-Team   1810<br>
      09:30:00 AM   A-Team   1810<br>
      09:45:00 AM   A-Team   950<br>
      10:00:00 AM   A-Team   950<br>
      10:15:00 AM   A-Team   950<br>
      10:30:00 AM   A-Team   950<br>
      10:45:00 AM   A-Team   950<br>
      11:00:00 AM   A-Team   0<br>
      ....<br>
      02:30:00 PM   A-Team   0<br>
      02:45:00 PM   A-Team   536<br>
      03:00:00 PM   A-Team   536<br>
      03:15:00 PM   A-Team   536<br>
      03:30:00 PM   A-Team   0<br>
      ...<br>
      06:00:00 PM  A-Team   0  </p>
  
  <p>In my research, I discovered that using a table to support the increments (15 mins) and joining it to my Workouts table should help. So, I created the following table: </p>
  
  <pre><code>IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Tally') AND  OBJECTPROPERTY(id, 'IsTable') = 1)
  DROP TABLE Tally;
  
  CREATE TABLE Tally 
      (N INT, 
      CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N))
  
  -----------Create and preset a loop counter
  DECLARE @Counter INT
      SET @Counter = 0
  
  -----------Populate the table with 15 minute intervals between 2012 and 2017
    WHILE @Counter &lt;= 175299
    BEGIN
           INSERT INTO Tally (N) VALUES (@Counter) SET @Counter = @Counter + 1
      END
  
  -----------Build the CalendarDT Table
  IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('CalendarDT') AND  OBJECTPROPERTY(id, 'IsTable') = 1)
  DROP TABLE CalendarDT;
  
  DECLARE @StartDT DATETIME;
  
  SELECT @StartDT = '2012-01-01T00:00:00';
  
  SELECT  DATEADD(mi,v.N*15,@StartDT) AS TheDT INTO CalendarDT FROM Tally v WHERE v.N BETWEEN 0 AND 175299;
  
  -----------Creating a clustered index for performance
  IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('IDXC_CalendarDT_TheDT') AND  OBJECTPROPERTY(id, 'IsIndex') = 1)
  DROP INDEX CalendarDT.IDXC_CalendarDT_TheDT;
  
  CREATE CLUSTERED INDEX IDXC_CalendarDT_TheDT ON CalendarDT (TheDT)
  GO
  
  -----------Drop the Tally table
  IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Tally') AND  OBJECTPROPERTY(id, 'IsTable') = 1)
  DROP TABLE Tally;
  </code></pre>
  
  <p>I then tried to join my Workouts table with the following:  </p>
  
  <pre><code>SELECT  CalendarDT.TheDT AS [Date], COALESCE(SUM(Workouts.Calories),0) AS CalorieCnt, Workouts.User_ID
  FROM    CalendarDT
          LEFT OUTER JOIN Workouts ON Workouts.Start_Time &gt;= '03/10/2012 08:00:00 AM' AND Workouts.End_Time &lt; '03/10/2012 06:00:00 PM'
          AND CalendarDT.TheDT &gt;= '03/10/2012 08:00:00 AM' AND CalendarDT.TheDT &lt;= '03/10/2012 06:00:00 PM'
  WHERE   Workouts.User_ID IN (SELECT User_ID FROM Team_User WHERE Team_ID = 1)
  GROUP BY CalendarDT.TheDT, Workouts.User_ID
  ORDER BY CalendarDT.TheDT;
  </code></pre>
  
  <p>This is as close as I've been able to come to the solution. But, I have a few problems:  </p>
  
  <p>1) I haven't figured out how to join the Team_Name from within the JOIN statement. </p>
  
  <p>2) The CalorieCnt, even if it was aggregated by Team_Name instead of User_ID shows the same values for each time period. It's not aggregating the totals properly for each given time, including those times when it should be 0.  </p>
  
  <p>I think one of the problems is that I can't get the comparison of how many calories were burnt at say 08:45:00 AM because there is no Workout that started/ended at that time. 
  I've been looking at this for a while and I'm stumped. Any help getting these reports working for an individual user and by team would be greatly appreciated!</p>