In the past I have given the advice to break down all date intervals into the smallest part, most often minutes.
Then OP should group by the minute.
-- Prepare sample data
DECLARE @Data TABLE
(
RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
CreateDate DATETIME,
DeleteDate DATETIME
)
-- Populate sample data
INSERT @Data
(
CreateDate,
DeleteDate
)
SELECT '2009-01-14 22:33', '2009-01-14 22:35' UNION ALL
SELECT '2009-01-14 22:33', '2009-01-14 22:33' UNION ALL
SELECT '2009-01-14 22:34', '2009-01-14 22:35' UNION ALL
SELECT '2009-01-14 22:35', '2009-01-14 22:35' UNION ALL
SELECT '2009-01-14 22:35', '2009-01-14 22:36' UNION ALL
SELECT '2009-01-14 22:37', '2009-01-14 22:37' UNION ALL
SELECT '2009-01-14 22:39', '2009-01-14 22:39' UNION ALL
SELECT '2009-01-14 22:38', '2009-01-14 22:38' UNION...
-- Prepare sample data
DECLARE @Sample TABLE
(
GalaxyID INT,
ObjectID VARCHAR(16),
ObjectType VARCHAR(5),
ObjectTitle VARCHAR(200)
)
INSERT @Sample
SELECT 1, 'T022520001611242', 'Topic', 'Business Strategy' UNION ALL
SELECT 1, 'T021320001145243', 'Topic', 'Decision Making' UNION ALL
SELECT 1, 'T8150310322032', 'Topic', 'New Growth' UNION ALL
SELECT 1, 'T97200019493829', 'Topic', 'Marketing Strategy' UNION ALL
SELECT 2, 'T1210018575047', 'Topic', 'Strategic Relationships' UNION ALL
SELECT 2, 'T1027001655860', 'Topic', 'Globalization' UNION ALL
SELECT 2, 'T95200015582307', 'Topic', 'Strategic Sourcing' UNION ALL
SELECT 2, 'T021120001714561', 'Topic', 'Business Processes & Architectures' UNION ALL
SELECT 3, 'T011820001527219', 'Topic', 'Business Models' UNION ALL
SELECT 3, 'T022520001622334', 'Topic', 'Venture Capital Processes' UNION ALL
SELECT 3, 'T524200010114538', 'Topic', 'Entrepreneurial Thinking' UNION ALL
SELECT 3, 'T011820001524538', 'Topic', 'Drivers...
SELECT d.theDate,
DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekend,
1 - DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekday
FROM (
SELECT CAST('20081124' AS SMALLDATETIME) AS theDate UNION ALL
SELECT '20081125' UNION ALL
SELECT '20081126' UNION ALL
SELECT '20081127' UNION ALL
SELECT '20081128' UNION ALL
SELECT '20081129' UNION ALL
SELECT '20081130'
) AS d