In SSMS, using a Calendar Table (broken out into quarter hours), how can I sum over particular columns during...
Please see this post from yesterday about my original problem.
Basically I need to find all the potential overlapping time intervals between a key employee and up to 3 others working simultaneously. a brief recap of the post is here:
I have written a query that pulled each "other employee's" specific overlapping time interval with the keystone. For one shift the timeline looks like this:
Key Emp. | 9AM------------------------6PM
Emp. A | 9AM------------------------6PM
Emp. B | 12PM-------4PM
So the discrete periods where a true "controlled" comparison can be made are among:
- Key, A, and B from 12PM - 4PM
- Key and A from 9AM - 12PM
- Key and A from 4PM - 6PM
The end goal is to pull all the activity (organized as events with datetime stamps in a separate table) for each employee that occurs within those time periods and compare totals for each relevant employee. So there would be a separate "Count(events)" total for each time frame only affected by the employees that share the time interval as described above.
The "In" and "Out" columns for key and other employees are stored as DATETIME.
Currently, my data is organized like this:
Key_ShiftID | Key In | Key Out |
K 2017-01-01 09:00:00.000 2017-01-10 18:00:00.000
K 2017-01-01 09:00:00.000 2017-01-10 18:00:00.000
...continued...
Oth_Emp_ShiftID | Oth_Emp_In | Oth_Emp_Out
A 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
B 2017-01-01 12:00:00.000 2017-01-01 16:00:00.000
Where the Shift IDs (Key_ShiftID and Oth_Emp_ShiftID) are unique strings and the time intervals are defined by two columns a piece (Key_In & Key_Out + Oth_Emp_In & Oth_Emp_Out) are stored as datetime/timestamps. I'm looking for discrete periods where I can compare the activity of the employees, which is in a separate table with each event having a unique datetime as was mentioned earlier. Thus, I think the ending data would look something like this:
Key_ShiftID | Key In | Key Out |
K 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
K 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
K 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
K 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
...continued...
Oth_Emp_ShiftID | Oth_Emp_In | Oth_Emp_Out
A 2017-01-01 12:00:00.000 2017-01-01 16:00:00.000
B 2017-01-01 12:00:00.000 2017-01-01 16:00:00.000
A 2017-01-01 09:00:00.000 2017-01-01 12:00:00.000
A 2017-01-01 16:00:00.000 2017-01-01 18:00:00.000
Here is sample code from that table:
CREATE TABLE "sample_data"
(
"Employee" INT,
"Key_ShiftID" TEXT,
"Key_In" TIMESTAMP,
"Key_Out" TIMESTAMP,
"Other_Emp_ShiftID" TEXT,
"Other_Emp_In" TIMESTAMP,
"Other_Emp_Out" TIMESTAMP,
"overlap_min" TIMESTAMP,
"overlap_max" TIMESTAMP
);
INSERT INTO "sample_data"
VALUES (900, '545BD826-0C9A-408B-BE9F-4C3D7D307948', '2016-09-27 14:15:00', '2016-09-27 21:45:00', '035FA1C1-B469-44EB-B5B4-5B6948574464', '2016-09-27 08:45:00', '2016-09-27 16:15:00', '2016-09-27 14:15:00', '2016-09-27 16:15:00'),
(78, '545BD826-0C9A-408B-BE9F-4C3D7D307948', '2016-09-27 14:15:00', '2016-09-27 21:45:00', '74035838-FD07-4F8D-8AC4-F6407AC786D9', '2016-09-27 18:00:00', '2016-09-27 21:15:00', '2016-09-27 18:00:00', '2016-09-27 21:15:00'),
(900, '545BD826-0C9A-408B-BE9F-4C3D7D307948', '2016-09-27 14:15:00', '2016-09-27 21:45:00', 'D7E9ADCD-8631-476D-B69F-00626F0E4B06', '2016-09-27 16:45:00', '2016-09-27 21:45:00', '2016-09-27 16:45:00', '2016-09-27 21:45:00');
Last night I built out the calendar. Each employee is represented by four new variables reflecting each 15 min interval, including the key employee:
- a flag for whether or not the employee was working during the 15 min
- the number of events associated with that employee during that time frame
- the DATETIME of the start of the shift associated with that interval
- the DATETIME of the end of the shift associated with that interval
Here is sample data of the same shift information but presented in the Calendar table:
`CREATE TABLE sample_data (
[month] INT,
[day] INT,
[year] INT,
[hourno] INT,
[quarter] INT,
[start] DATETIME,
[end] DATETIME,
[KEY_SHIFTID] NVARCHAR(36),
[Column_Key] INT,
[Column_Key_start] DATETIME,
[Column_Key_end] DATETIME,
[Column_Key_num_pat] NVARCHAR(4),
[Column_900] NVARCHAR(4),
[Column_900_start] NVARCHAR(19),
[Column_900_end] NVARCHAR(19),
[Column_900_num_pat] NVARCHAR(4),
[Column_78] NVARCHAR(4),
[Column_78_start] NVARCHAR(19),
[Column_78_end] NVARCHAR(19),
[Column_78_num_pat] NVARCHAR(4)
);`
`INSERT INTO sample_data VALUES
(9,27,2016,15,2,'2016-09-27 14:15:00','2016-09-27 14:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,15,3,'2016-09-27 14:30:00','2016-09-27 14:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,15,4,'2016-09-27 14:45:00','2016-09-27 14:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,16,1,'2016-09-27 15:00:00','2016-09-27 15:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,16,2,'2016-09-27 15:15:00','2016-09-27 15:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,16,3,'2016-09-27 15:30:00','2016-09-27 15:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,16,4,'2016-09-27 15:45:00','2016-09-27 15:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'2',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,17,1,'2016-09-27 16:00:00','2016-09-27 16:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,17,2,'2016-09-27 16:15:00','2016-09-27 16:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'NULL',N'NULL',N'NULL',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,17,3,'2016-09-27 16:30:00','2016-09-27 16:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'2',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,17,4,'2016-09-27 16:45:00','2016-09-27 16:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,18,1,'2016-09-27 17:00:00','2016-09-27 17:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,18,2,'2016-09-27 17:15:00','2016-09-27 17:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,18,3,'2016-09-27 17:30:00','2016-09-27 17:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,18,4,'2016-09-27 17:45:00','2016-09-27 17:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,19,1,'2016-09-27 18:00:00','2016-09-27 18:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'2',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,19,2,'2016-09-27 18:15:00','2016-09-27 18:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'3'),
(9,27,2016,19,3,'2016-09-27 18:30:00','2016-09-27 18:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'NULL'),
(9,27,2016,19,4,'2016-09-27 18:45:00','2016-09-27 18:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,20,1,'2016-09-27 19:00:00','2016-09-27 19:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'NULL'),
(9,27,2016,20,2,'2016-09-27 19:15:00','2016-09-27 19:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,20,3,'2016-09-27 19:30:00','2016-09-27 19:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,20,4,'2016-09-27 19:45:00','2016-09-27 19:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,21,1,'2016-09-27 20:00:00','2016-09-27 20:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'NULL'),
(9,27,2016,21,2,'2016-09-27 20:15:00','2016-09-27 20:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,21,3,'2016-09-27 20:30:00','2016-09-27 20:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'2'),
(9,27,2016,21,4,'2016-09-27 20:45:00','2016-09-27 20:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'2',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,22,1,'2016-09-27 21:00:00','2016-09-27 21:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'2',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'NULL'),
(9,27,2016,22,2,'2016-09-27 21:15:00','2016-09-27 21:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,22,3,'2016-09-27 21:30:00','2016-09-27 21:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL');`
The end goal is the same. I want to be able to compare totals for "controlled" intervals as described in my original post. How can I use the calendar table to find the intervals like
- Key, A, and B from 12PM - 4PM
- Key and A from 9AM - 12PM
- Key and A from 4PM - 6PM
and count the number of events in each relevant 15 min increment that would flow into a total over the sought after interval?
sql-server datetime calendar ssms gaps-and-islands asked Nov 15 '18 at 16:22
freezekillfreezekill
114
add a comment |
Please see this post from yesterday about my original problem.
Basically I need to find all the potential overlapping time intervals between a key employee and up to 3 others working simultaneously. a brief recap of the post is here:
I have written a query that pulled each "other employee's" specific overlapping time interval with the keystone. For one shift the timeline looks like this:
Key Emp. | 9AM------------------------6PM
Emp. A | 9AM------------------------6PM
Emp. B | 12PM-------4PM
So the discrete periods where a true "controlled" comparison can be made are among:
- Key, A, and B from 12PM - 4PM
- Key and A from 9AM - 12PM
- Key and A from 4PM - 6PM
The end goal is to pull all the activity (organized as events with datetime stamps in a separate table) for each employee that occurs within those time periods and compare totals for each relevant employee. So there would be a separate "Count(events)" total for each time frame only affected by the employees that share the time interval as described above.
The "In" and "Out" columns for key and other employees are stored as DATETIME.
Currently, my data is organized like this:
Key_ShiftID | Key In | Key Out |
K 2017-01-01 09:00:00.000 2017-01-10 18:00:00.000
K 2017-01-01 09:00:00.000 2017-01-10 18:00:00.000
...continued...
Oth_Emp_ShiftID | Oth_Emp_In | Oth_Emp_Out
A 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
B 2017-01-01 12:00:00.000 2017-01-01 16:00:00.000
Where the Shift IDs (Key_ShiftID and Oth_Emp_ShiftID) are unique strings and the time intervals are defined by two columns a piece (Key_In & Key_Out + Oth_Emp_In & Oth_Emp_Out) are stored as datetime/timestamps. I'm looking for discrete periods where I can compare the activity of the employees, which is in a separate table with each event having a unique datetime as was mentioned earlier. Thus, I think the ending data would look something like this:
Key_ShiftID | Key In | Key Out |
K 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
K 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
K 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
K 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
...continued...
Oth_Emp_ShiftID | Oth_Emp_In | Oth_Emp_Out
A 2017-01-01 12:00:00.000 2017-01-01 16:00:00.000
B 2017-01-01 12:00:00.000 2017-01-01 16:00:00.000
A 2017-01-01 09:00:00.000 2017-01-01 12:00:00.000
A 2017-01-01 16:00:00.000 2017-01-01 18:00:00.000
Here is sample code from that table:
CREATE TABLE "sample_data"
(
"Employee" INT,
"Key_ShiftID" TEXT,
"Key_In" TIMESTAMP,
"Key_Out" TIMESTAMP,
"Other_Emp_ShiftID" TEXT,
"Other_Emp_In" TIMESTAMP,
"Other_Emp_Out" TIMESTAMP,
"overlap_min" TIMESTAMP,
"overlap_max" TIMESTAMP
);
INSERT INTO "sample_data"
VALUES (900, '545BD826-0C9A-408B-BE9F-4C3D7D307948', '2016-09-27 14:15:00', '2016-09-27 21:45:00', '035FA1C1-B469-44EB-B5B4-5B6948574464', '2016-09-27 08:45:00', '2016-09-27 16:15:00', '2016-09-27 14:15:00', '2016-09-27 16:15:00'),
(78, '545BD826-0C9A-408B-BE9F-4C3D7D307948', '2016-09-27 14:15:00', '2016-09-27 21:45:00', '74035838-FD07-4F8D-8AC4-F6407AC786D9', '2016-09-27 18:00:00', '2016-09-27 21:15:00', '2016-09-27 18:00:00', '2016-09-27 21:15:00'),
(900, '545BD826-0C9A-408B-BE9F-4C3D7D307948', '2016-09-27 14:15:00', '2016-09-27 21:45:00', 'D7E9ADCD-8631-476D-B69F-00626F0E4B06', '2016-09-27 16:45:00', '2016-09-27 21:45:00', '2016-09-27 16:45:00', '2016-09-27 21:45:00');
Last night I built out the calendar. Each employee is represented by four new variables reflecting each 15 min interval, including the key employee:
- a flag for whether or not the employee was working during the 15 min
- the number of events associated with that employee during that time frame
- the DATETIME of the start of the shift associated with that interval
- the DATETIME of the end of the shift associated with that interval
Here is sample data of the same shift information but presented in the Calendar table:
`CREATE TABLE sample_data (
[month] INT,
[day] INT,
[year] INT,
[hourno] INT,
[quarter] INT,
[start] DATETIME,
[end] DATETIME,
[KEY_SHIFTID] NVARCHAR(36),
[Column_Key] INT,
[Column_Key_start] DATETIME,
[Column_Key_end] DATETIME,
[Column_Key_num_pat] NVARCHAR(4),
[Column_900] NVARCHAR(4),
[Column_900_start] NVARCHAR(19),
[Column_900_end] NVARCHAR(19),
[Column_900_num_pat] NVARCHAR(4),
[Column_78] NVARCHAR(4),
[Column_78_start] NVARCHAR(19),
[Column_78_end] NVARCHAR(19),
[Column_78_num_pat] NVARCHAR(4)
);`
`INSERT INTO sample_data VALUES
(9,27,2016,15,2,'2016-09-27 14:15:00','2016-09-27 14:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,15,3,'2016-09-27 14:30:00','2016-09-27 14:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,15,4,'2016-09-27 14:45:00','2016-09-27 14:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,16,1,'2016-09-27 15:00:00','2016-09-27 15:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,16,2,'2016-09-27 15:15:00','2016-09-27 15:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,16,3,'2016-09-27 15:30:00','2016-09-27 15:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,16,4,'2016-09-27 15:45:00','2016-09-27 15:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'2',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,17,1,'2016-09-27 16:00:00','2016-09-27 16:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,17,2,'2016-09-27 16:15:00','2016-09-27 16:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'NULL',N'NULL',N'NULL',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,17,3,'2016-09-27 16:30:00','2016-09-27 16:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'2',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,17,4,'2016-09-27 16:45:00','2016-09-27 16:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,18,1,'2016-09-27 17:00:00','2016-09-27 17:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,18,2,'2016-09-27 17:15:00','2016-09-27 17:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,18,3,'2016-09-27 17:30:00','2016-09-27 17:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,18,4,'2016-09-27 17:45:00','2016-09-27 17:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,19,1,'2016-09-27 18:00:00','2016-09-27 18:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'2',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,19,2,'2016-09-27 18:15:00','2016-09-27 18:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'3'),
(9,27,2016,19,3,'2016-09-27 18:30:00','2016-09-27 18:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'NULL'),
(9,27,2016,19,4,'2016-09-27 18:45:00','2016-09-27 18:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,20,1,'2016-09-27 19:00:00','2016-09-27 19:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'NULL'),
(9,27,2016,20,2,'2016-09-27 19:15:00','2016-09-27 19:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,20,3,'2016-09-27 19:30:00','2016-09-27 19:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,20,4,'2016-09-27 19:45:00','2016-09-27 19:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,21,1,'2016-09-27 20:00:00','2016-09-27 20:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'NULL'),
(9,27,2016,21,2,'2016-09-27 20:15:00','2016-09-27 20:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,21,3,'2016-09-27 20:30:00','2016-09-27 20:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'2'),
(9,27,2016,21,4,'2016-09-27 20:45:00','2016-09-27 20:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'2',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,22,1,'2016-09-27 21:00:00','2016-09-27 21:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'2',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'NULL'),
(9,27,2016,22,2,'2016-09-27 21:15:00','2016-09-27 21:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,22,3,'2016-09-27 21:30:00','2016-09-27 21:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL');`
The end goal is the same. I want to be able to compare totals for "controlled" intervals as described in my original post. How can I use the calendar table to find the intervals like
- Key, A, and B from 12PM - 4PM
- Key and A from 9AM - 12PM
- Key and A from 4PM - 6PM
and count the number of events in each relevant 15 min increment that would flow into a total over the sought after interval?
sql-server datetime calendar ssms gaps-and-islands asked Nov 15 '18 at 16:22
freezekillfreezekill
114
add a comment |
Please see this post from yesterday about my original problem.
Basically I need to find all the potential overlapping time intervals between a key employee and up to 3 others working simultaneously. a brief recap of the post is here:
I have written a query that pulled each "other employee's" specific overlapping time interval with the keystone. For one shift the timeline looks like this:
Key Emp. | 9AM------------------------6PM
Emp. A | 9AM------------------------6PM
Emp. B | 12PM-------4PM
So the discrete periods where a true "controlled" comparison can be made are among:
- Key, A, and B from 12PM - 4PM
- Key and A from 9AM - 12PM
- Key and A from 4PM - 6PM
The end goal is to pull all the activity (organized as events with datetime stamps in a separate table) for each employee that occurs within those time periods and compare totals for each relevant employee. So there would be a separate "Count(events)" total for each time frame only affected by the employees that share the time interval as described above.
The "In" and "Out" columns for key and other employees are stored as DATETIME.
Currently, my data is organized like this:
Key_ShiftID | Key In | Key Out |
K 2017-01-01 09:00:00.000 2017-01-10 18:00:00.000
K 2017-01-01 09:00:00.000 2017-01-10 18:00:00.000
...continued...
Oth_Emp_ShiftID | Oth_Emp_In | Oth_Emp_Out
A 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
B 2017-01-01 12:00:00.000 2017-01-01 16:00:00.000
Where the Shift IDs (Key_ShiftID and Oth_Emp_ShiftID) are unique strings and the time intervals are defined by two columns a piece (Key_In & Key_Out + Oth_Emp_In & Oth_Emp_Out) are stored as datetime/timestamps. I'm looking for discrete periods where I can compare the activity of the employees, which is in a separate table with each event having a unique datetime as was mentioned earlier. Thus, I think the ending data would look something like this:
Key_ShiftID | Key In | Key Out |
K 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
K 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
K 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
K 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
...continued...
Oth_Emp_ShiftID | Oth_Emp_In | Oth_Emp_Out
A 2017-01-01 12:00:00.000 2017-01-01 16:00:00.000
B 2017-01-01 12:00:00.000 2017-01-01 16:00:00.000
A 2017-01-01 09:00:00.000 2017-01-01 12:00:00.000
A 2017-01-01 16:00:00.000 2017-01-01 18:00:00.000
Here is sample code from that table:
CREATE TABLE "sample_data"
(
"Employee" INT,
"Key_ShiftID" TEXT,
"Key_In" TIMESTAMP,
"Key_Out" TIMESTAMP,
"Other_Emp_ShiftID" TEXT,
"Other_Emp_In" TIMESTAMP,
"Other_Emp_Out" TIMESTAMP,
"overlap_min" TIMESTAMP,
"overlap_max" TIMESTAMP
);
INSERT INTO "sample_data"
VALUES (900, '545BD826-0C9A-408B-BE9F-4C3D7D307948', '2016-09-27 14:15:00', '2016-09-27 21:45:00', '035FA1C1-B469-44EB-B5B4-5B6948574464', '2016-09-27 08:45:00', '2016-09-27 16:15:00', '2016-09-27 14:15:00', '2016-09-27 16:15:00'),
(78, '545BD826-0C9A-408B-BE9F-4C3D7D307948', '2016-09-27 14:15:00', '2016-09-27 21:45:00', '74035838-FD07-4F8D-8AC4-F6407AC786D9', '2016-09-27 18:00:00', '2016-09-27 21:15:00', '2016-09-27 18:00:00', '2016-09-27 21:15:00'),
(900, '545BD826-0C9A-408B-BE9F-4C3D7D307948', '2016-09-27 14:15:00', '2016-09-27 21:45:00', 'D7E9ADCD-8631-476D-B69F-00626F0E4B06', '2016-09-27 16:45:00', '2016-09-27 21:45:00', '2016-09-27 16:45:00', '2016-09-27 21:45:00');
Last night I built out the calendar. Each employee is represented by four new variables reflecting each 15 min interval, including the key employee:
- a flag for whether or not the employee was working during the 15 min
- the number of events associated with that employee during that time frame
- the DATETIME of the start of the shift associated with that interval
- the DATETIME of the end of the shift associated with that interval
Here is sample data of the same shift information but presented in the Calendar table:
`CREATE TABLE sample_data (
[month] INT,
[day] INT,
[year] INT,
[hourno] INT,
[quarter] INT,
[start] DATETIME,
[end] DATETIME,
[KEY_SHIFTID] NVARCHAR(36),
[Column_Key] INT,
[Column_Key_start] DATETIME,
[Column_Key_end] DATETIME,
[Column_Key_num_pat] NVARCHAR(4),
[Column_900] NVARCHAR(4),
[Column_900_start] NVARCHAR(19),
[Column_900_end] NVARCHAR(19),
[Column_900_num_pat] NVARCHAR(4),
[Column_78] NVARCHAR(4),
[Column_78_start] NVARCHAR(19),
[Column_78_end] NVARCHAR(19),
[Column_78_num_pat] NVARCHAR(4)
);`
`INSERT INTO sample_data VALUES
(9,27,2016,15,2,'2016-09-27 14:15:00','2016-09-27 14:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,15,3,'2016-09-27 14:30:00','2016-09-27 14:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,15,4,'2016-09-27 14:45:00','2016-09-27 14:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,16,1,'2016-09-27 15:00:00','2016-09-27 15:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,16,2,'2016-09-27 15:15:00','2016-09-27 15:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,16,3,'2016-09-27 15:30:00','2016-09-27 15:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,16,4,'2016-09-27 15:45:00','2016-09-27 15:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'2',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,17,1,'2016-09-27 16:00:00','2016-09-27 16:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,17,2,'2016-09-27 16:15:00','2016-09-27 16:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'NULL',N'NULL',N'NULL',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,17,3,'2016-09-27 16:30:00','2016-09-27 16:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'2',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,17,4,'2016-09-27 16:45:00','2016-09-27 16:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,18,1,'2016-09-27 17:00:00','2016-09-27 17:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,18,2,'2016-09-27 17:15:00','2016-09-27 17:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,18,3,'2016-09-27 17:30:00','2016-09-27 17:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,18,4,'2016-09-27 17:45:00','2016-09-27 17:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,19,1,'2016-09-27 18:00:00','2016-09-27 18:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'2',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,19,2,'2016-09-27 18:15:00','2016-09-27 18:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'3'),
(9,27,2016,19,3,'2016-09-27 18:30:00','2016-09-27 18:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'NULL'),
(9,27,2016,19,4,'2016-09-27 18:45:00','2016-09-27 18:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,20,1,'2016-09-27 19:00:00','2016-09-27 19:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'NULL'),
(9,27,2016,20,2,'2016-09-27 19:15:00','2016-09-27 19:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,20,3,'2016-09-27 19:30:00','2016-09-27 19:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,20,4,'2016-09-27 19:45:00','2016-09-27 19:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,21,1,'2016-09-27 20:00:00','2016-09-27 20:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'NULL'),
(9,27,2016,21,2,'2016-09-27 20:15:00','2016-09-27 20:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,21,3,'2016-09-27 20:30:00','2016-09-27 20:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'2'),
(9,27,2016,21,4,'2016-09-27 20:45:00','2016-09-27 20:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'2',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,22,1,'2016-09-27 21:00:00','2016-09-27 21:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'2',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'NULL'),
(9,27,2016,22,2,'2016-09-27 21:15:00','2016-09-27 21:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,22,3,'2016-09-27 21:30:00','2016-09-27 21:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL');`
The end goal is the same. I want to be able to compare totals for "controlled" intervals as described in my original post. How can I use the calendar table to find the intervals like
- Key, A, and B from 12PM - 4PM
- Key and A from 9AM - 12PM
- Key and A from 4PM - 6PM
and count the number of events in each relevant 15 min increment that would flow into a total over the sought after interval?
sql-server datetime calendar ssms gaps-and-islands asked Nov 15 '18 at 16:22
freezekillfreezekill
114
Please see this post from yesterday about my original problem.
Basically I need to find all the potential overlapping time intervals between a key employee and up to 3 others working simultaneously. a brief recap of the post is here:
I have written a query that pulled each "other employee's" specific overlapping time interval with the keystone. For one shift the timeline looks like this:
Key Emp. | 9AM------------------------6PM
Emp. A | 9AM------------------------6PM
Emp. B | 12PM-------4PM
So the discrete periods where a true "controlled" comparison can be made are among:
- Key, A, and B from 12PM - 4PM
- Key and A from 9AM - 12PM
- Key and A from 4PM - 6PM
The end goal is to pull all the activity (organized as events with datetime stamps in a separate table) for each employee that occurs within those time periods and compare totals for each relevant employee. So there would be a separate "Count(events)" total for each time frame only affected by the employees that share the time interval as described above.
The "In" and "Out" columns for key and other employees are stored as DATETIME.
Currently, my data is organized like this:
Key_ShiftID | Key In | Key Out |
K 2017-01-01 09:00:00.000 2017-01-10 18:00:00.000
K 2017-01-01 09:00:00.000 2017-01-10 18:00:00.000
...continued...
Oth_Emp_ShiftID | Oth_Emp_In | Oth_Emp_Out
A 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
B 2017-01-01 12:00:00.000 2017-01-01 16:00:00.000
Where the Shift IDs (Key_ShiftID and Oth_Emp_ShiftID) are unique strings and the time intervals are defined by two columns a piece (Key_In & Key_Out + Oth_Emp_In & Oth_Emp_Out) are stored as datetime/timestamps. I'm looking for discrete periods where I can compare the activity of the employees, which is in a separate table with each event having a unique datetime as was mentioned earlier. Thus, I think the ending data would look something like this:
Key_ShiftID | Key In | Key Out |
K 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
K 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
K 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
K 2017-01-01 09:00:00.000 2017-01-01 18:00:00.000
...continued...
Oth_Emp_ShiftID | Oth_Emp_In | Oth_Emp_Out
A 2017-01-01 12:00:00.000 2017-01-01 16:00:00.000
B 2017-01-01 12:00:00.000 2017-01-01 16:00:00.000
A 2017-01-01 09:00:00.000 2017-01-01 12:00:00.000
A 2017-01-01 16:00:00.000 2017-01-01 18:00:00.000
Here is sample code from that table:
CREATE TABLE "sample_data"
(
"Employee" INT,
"Key_ShiftID" TEXT,
"Key_In" TIMESTAMP,
"Key_Out" TIMESTAMP,
"Other_Emp_ShiftID" TEXT,
"Other_Emp_In" TIMESTAMP,
"Other_Emp_Out" TIMESTAMP,
"overlap_min" TIMESTAMP,
"overlap_max" TIMESTAMP
);
INSERT INTO "sample_data"
VALUES (900, '545BD826-0C9A-408B-BE9F-4C3D7D307948', '2016-09-27 14:15:00', '2016-09-27 21:45:00', '035FA1C1-B469-44EB-B5B4-5B6948574464', '2016-09-27 08:45:00', '2016-09-27 16:15:00', '2016-09-27 14:15:00', '2016-09-27 16:15:00'),
(78, '545BD826-0C9A-408B-BE9F-4C3D7D307948', '2016-09-27 14:15:00', '2016-09-27 21:45:00', '74035838-FD07-4F8D-8AC4-F6407AC786D9', '2016-09-27 18:00:00', '2016-09-27 21:15:00', '2016-09-27 18:00:00', '2016-09-27 21:15:00'),
(900, '545BD826-0C9A-408B-BE9F-4C3D7D307948', '2016-09-27 14:15:00', '2016-09-27 21:45:00', 'D7E9ADCD-8631-476D-B69F-00626F0E4B06', '2016-09-27 16:45:00', '2016-09-27 21:45:00', '2016-09-27 16:45:00', '2016-09-27 21:45:00');
Last night I built out the calendar. Each employee is represented by four new variables reflecting each 15 min interval, including the key employee:
- a flag for whether or not the employee was working during the 15 min
- the number of events associated with that employee during that time frame
- the DATETIME of the start of the shift associated with that interval
- the DATETIME of the end of the shift associated with that interval
Here is sample data of the same shift information but presented in the Calendar table:
`CREATE TABLE sample_data (
[month] INT,
[day] INT,
[year] INT,
[hourno] INT,
[quarter] INT,
[start] DATETIME,
[end] DATETIME,
[KEY_SHIFTID] NVARCHAR(36),
[Column_Key] INT,
[Column_Key_start] DATETIME,
[Column_Key_end] DATETIME,
[Column_Key_num_pat] NVARCHAR(4),
[Column_900] NVARCHAR(4),
[Column_900_start] NVARCHAR(19),
[Column_900_end] NVARCHAR(19),
[Column_900_num_pat] NVARCHAR(4),
[Column_78] NVARCHAR(4),
[Column_78_start] NVARCHAR(19),
[Column_78_end] NVARCHAR(19),
[Column_78_num_pat] NVARCHAR(4)
);`
`INSERT INTO sample_data VALUES
(9,27,2016,15,2,'2016-09-27 14:15:00','2016-09-27 14:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,15,3,'2016-09-27 14:30:00','2016-09-27 14:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,15,4,'2016-09-27 14:45:00','2016-09-27 14:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,16,1,'2016-09-27 15:00:00','2016-09-27 15:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,16,2,'2016-09-27 15:15:00','2016-09-27 15:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,16,3,'2016-09-27 15:30:00','2016-09-27 15:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,16,4,'2016-09-27 15:45:00','2016-09-27 15:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'2',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,17,1,'2016-09-27 16:00:00','2016-09-27 16:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 14:15:00',N'2016-09-27 16:15:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,17,2,'2016-09-27 16:15:00','2016-09-27 16:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'NULL',N'NULL',N'NULL',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,17,3,'2016-09-27 16:30:00','2016-09-27 16:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'2',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,17,4,'2016-09-27 16:45:00','2016-09-27 16:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,18,1,'2016-09-27 17:00:00','2016-09-27 17:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,18,2,'2016-09-27 17:15:00','2016-09-27 17:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,18,3,'2016-09-27 17:30:00','2016-09-27 17:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,18,4,'2016-09-27 17:45:00','2016-09-27 17:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,19,1,'2016-09-27 18:00:00','2016-09-27 18:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'2',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,19,2,'2016-09-27 18:15:00','2016-09-27 18:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'3'),
(9,27,2016,19,3,'2016-09-27 18:30:00','2016-09-27 18:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'NULL'),
(9,27,2016,19,4,'2016-09-27 18:45:00','2016-09-27 18:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,20,1,'2016-09-27 19:00:00','2016-09-27 19:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'NULL'),
(9,27,2016,20,2,'2016-09-27 19:15:00','2016-09-27 19:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,20,3,'2016-09-27 19:30:00','2016-09-27 19:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,20,4,'2016-09-27 19:45:00','2016-09-27 19:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,21,1,'2016-09-27 20:00:00','2016-09-27 20:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'NULL'),
(9,27,2016,21,2,'2016-09-27 20:15:00','2016-09-27 20:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,21,3,'2016-09-27 20:30:00','2016-09-27 20:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'2'),
(9,27,2016,21,4,'2016-09-27 20:45:00','2016-09-27 20:59:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'2',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'1'),
(9,27,2016,22,1,'2016-09-27 21:00:00','2016-09-27 21:14:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'2',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 18:00:00',N'2016-09-27 21:15:00',N'NULL'),
(9,27,2016,22,2,'2016-09-27 21:15:00','2016-09-27 21:29:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'1',N'NULL',N'NULL',N'NULL',N'NULL'),
(9,27,2016,22,3,'2016-09-27 21:30:00','2016-09-27 21:44:59',N'545BD826-0C9A-408B-BE9F-4C3D7D307948',1,'2016-09-27 14:15:00','2016-09-27 21:45:00',N'NULL',N'1',N'2016-09-27 16:45:00',N'2016-09-27 21:45:00',N'NULL',N'NULL',N'NULL',N'NULL',N'NULL');`
The end goal is the same. I want to be able to compare totals for "controlled" intervals as described in my original post. How can I use the calendar table to find the intervals like
- Key, A, and B from 12PM - 4PM
- Key and A from 9AM - 12PM
- Key and A from 4PM - 6PM
and count the number of events in each relevant 15 min increment that would flow into a total over the sought after interval?
sql-server datetime calendar ssms gaps-and-islands sql-server datetime calendar ssms gaps-and-islands asked Nov 15 '18 at 16:22
freezekillfreezekill
114
asked Nov 15 '18 at 16:22
freezekillfreezekill
114
edited Nov 15 '18 at 16:27
freezekill
asked Nov 15 '18 at 16:22
freezekillfreezekill
114
asked Nov 15 '18 at 16:22
freezekillfreezekill
114
asked Nov 15 '18 at 16:22
freezekillfreezekill
114
114
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53323759%2fin-ssms-using-a-calendar-table-broken-out-into-quarter-hours-how-can-i-sum-o%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53323759%2fin-ssms-using-a-calendar-table-broken-out-into-quarter-hours-how-can-i-sum-o%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
