TSQL Grouping by time with Start and End dates

Question

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.

The tables I am using are as follows:

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);

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:
TheDt Team Calories
08:00:00 AM A-Team 860
08:15:00 AM A-Team 860
08:30:00 AM A-Team 1810
08:45:00 AM A-Team 1810
09:00:00 AM A-Team 1810
09:15:00 AM A-Team 1810
09:30:00 AM A-Team 1810
09:45:00 AM A-Team 950
10:00:00 AM A-Team 950
10:15:00 AM A-Team 950
10:30:00 AM A-Team 950
10:45:00 AM A-Team 950
11:00:00 AM A-Team 0
....
02:30:00 PM A-Team 0
02:45:00 PM A-Team 536
03:00:00 PM A-Team 536
03:15:00 PM A-Team 536
03:30:00 PM A-Team 0
...
06:00:00 PM A-Team 0

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:

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 <= 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;

I then tried to join my Workouts table with the following:

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 >= '03/10/2012 08:00:00 AM' AND Workouts.End_Time < '03/10/2012 06:00:00 PM'
        AND CalendarDT.TheDT >= '03/10/2012 08:00:00 AM' AND CalendarDT.TheDT <= '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;

This is as close as I've been able to come to the solution. But, I have a few problems:

1) I haven't figured out how to join the Team_Name from within the JOIN statement.

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.

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!

Quassnoi · Accepted Answer

WITH    ranges (st) AS
        (
        SELECT  CAST('2012-03-10 08:00:00' AS DATETIME)
        UNION ALL
        SELECT  DATEADD(minute, 15, st)
        FROM    ranges
        WHERE   DATEADD(minute, 15, st) < CAST('2012-03-10 18:00:00' AS DATETIME)
        )
SELECT  st, team_name, calories
FROM    (
        SELECT  st, team_id, COALESCE(SUM(calories), 0) AS calories
        FROM    ranges r
        LEFT JOIN
                workouts w
        ON      start_time < DATEADD(minute, 15, st)
                AND end_time > st
        LEFT JOIN
                team_user tu
        ON      tu.user_id = w.user_id
        GROUP BY
                st, team_id
        ) c
LEFT JOIN
        teams t
ON      t.team_id = c.team_id
ORDER BY
        c.st, c.team_id
OPTION (MAXRECURSION 0)

TSQL Grouping by time with Start and End dates

Answers (2)

Related Questions