# Thinking outside the box

Patron Saint of Lost Yaks

## January 2009 Blog Posts

##### Alternative approach to calculate most used time interval

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...

posted @ Friday, January 30, 2009 9:03 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### Create another nested XML hierarchy

-- 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...

posted @ Tuesday, January 20, 2009 9:50 PM | Feedback (4) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

##### Easy script for calculating weekday or weekend for a date

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

posted @ Wednesday, January 14, 2009 2:03 PM | Feedback (5) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]