Query to get logs between Start and End Night hour

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

CREATE TABLE #MyLogs
    (dtUtcDateTime datetime, HourPart int);

CREATE CLUSTERED INDEX cx 
ON #MyLogs (HourPart, dtUtcDateTime);
    
INSERT INTO #MyLogs
    (dtUtcDateTime, HourPart)
VALUES
    ('2015-07-31 19:00:06', 19),
    ('2015-07-31 19:00:07', 19),
    ('2015-07-31 19:01:27', 19),
    ('2015-07-31 19:01:29', 19),
    ('2015-07-31 19:01:50', 19),
    ('2015-07-31 19:04:17', 19),
    ('2015-07-31 19:04:42', 19),
    ('2015-07-31 19:27:48', 19),
    ('2015-07-31 19:48:17', 19),
    ('2015-07-31 20:04:43', 20),
    ('2015-08-01 01:55:11', 1),
    ('2015-08-01 01:55:13', 1),
    ('2015-08-01 01:55:21', 1),
    ('2015-08-01 01:55:23', 1),
    ('2015-08-01 01:55:25', 1),
    ('2015-08-01 01:55:29', 1),
    ('2015-08-01 01:55:42', 1),
    ('2015-08-01 01:55:44', 1),
    ('2015-08-01 01:55:45', 1),
    ('2015-08-01 01:56:26', 1),
    ('2015-08-01 01:56:27', 1),
    ('2015-08-01 01:56:31', 1),
    ('2015-08-01 01:56:32', 1),
    ('2015-08-01 02:06:32', 2),
    ('2015-08-01 02:10:49', 2),
    ('2015-08-01 02:14:36', 2),
    ('2015-08-01 02:15:01', 2),
    ('2015-08-01 02:15:10', 2),
    ('2015-08-01 02:15:11', 2),
    ('2015-08-01 02:15:16', 2),
    ('2015-08-01 02:15:20', 2),
    ('2015-08-01 03:11:40', 3),
    ('2015-08-01 03:11:38', 3),
    ('2015-08-01 03:14:07', 3),
    ('2015-08-01 03:14:20', 3),
    ('2015-08-01 03:14:12', 3),
    ('2015-08-01 03:14:17', 3),
    ('2015-08-01 03:14:51', 3),
    ('2015-08-01 03:15:04', 3),
    ('2015-08-01 03:15:06', 3),
    ('2015-08-01 03:15:10', 3),
    ('2015-08-01 03:15:11', 3),
    ('2015-08-01 03:15:16', 3),
    ('2015-08-01 03:15:24', 3),
    ('2015-08-01 03:15:25', 3),
    ('2015-08-01 03:15:29', 3),
    ('2015-08-01 03:15:41', 3),
    ('2015-08-01 03:15:43', 3),
    ('2015-08-01 03:17:24', 3),
    ('2015-08-01 03:17:25', 3),
    ('2015-08-01 03:17:28', 3),
    ('2015-08-01 03:17:49', 3),
    ('2015-08-01 03:17:45', 3),
    ('2015-08-01 03:19:30', 3),
    ('2015-08-01 03:20:03', 3),
    ('2015-08-01 03:22:12', 3),
    ('2015-08-01 03:23:13', 3),
    ('2015-08-01 03:23:16', 3),
    ('2015-08-01 03:24:46', 3),
    ('2015-08-01 03:26:36', 3),
    ('2015-08-01 03:26:35', 3),
    ('2015-08-01 03:26:49', 3),
    ('2015-08-01 03:26:52', 3),
    ('2015-08-01 03:26:57', 3),
    ('2015-08-01 03:27:14', 3),
    ('2015-08-01 03:27:15', 3),
    ('2015-08-01 03:27:31', 3),
    ('2015-08-01 03:27:30', 3),
    ('2015-08-01 03:28:04', 3),
    ('2015-08-01 03:29:16', 3),
    ('2015-08-01 03:29:32', 3),
    ('2015-08-01 03:29:30', 3),
    ('2015-08-01 03:29:43', 3),
    ('2015-08-01 03:29:56', 3),
    ('2015-08-01 03:30:06', 3),
    ('2015-08-01 03:30:25', 3),
    ('2015-08-01 03:30:26', 3),
    ('2015-08-01 03:30:27', 3),
    ('2015-08-01 03:32:21', 3),
    ('2015-08-01 03:34:07', 3),
    ('2015-08-01 03:35:01', 3),
    ('2015-08-01 03:35:02', 3),
    ('2015-08-01 03:35:04', 3),
    ('2015-08-01 03:35:41', 3),
    ('2015-08-01 03:35:48', 3),
    ('2015-08-01 03:36:03', 3),
    ('2015-08-01 03:36:02', 3),
    ('2015-08-01 03:36:08', 3),
    ('2015-08-01 03:36:09', 3),
    ('2015-08-01 03:36:24', 3),
    ('2015-08-01 03:36:33', 3),
    ('2015-08-01 04:30:06', 4),
    ('2015-08-01 04:36:33', 4),
    ('2015-08-01 06:03:12', 6),
    ('2015-08-01 06:03:24', 6),
    ('2015-08-01 06:03:26', 6),
    ('2015-08-01 06:03:29', 6),
    ('2015-08-01 06:03:30', 6),
    ('2015-08-01 06:04:16', 6),
    ('2015-08-01 06:25:25', 6),
    ('2015-08-01 06:25:33', 6),
    ('2015-08-01 06:26:31', 6),
    ('2015-08-01 06:26:42', 6),
    ('2015-08-01 06:26:47', 6),
    ('2015-08-01 06:26:49', 6),
    ('2015-08-01 06:26:53', 6),
    ('2015-08-01 06:28:10', 6),
    ('2015-08-01 06:28:11', 6),
    ('2015-08-01 06:28:12', 6),
    ('2015-08-01 06:31:26', 6),
    ('2015-08-01 06:32:02', 6),
    ('2015-08-01 06:32:03', 6),
    ('2015-08-01 06:32:08', 6),
    ('2015-08-01 06:32:39', 6),
    ('2015-08-01 06:32:42', 6),
    ('2015-08-01 06:32:47', 6),
    ('2015-08-01 06:33:55', 6),
    ('2015-08-01 06:37:11', 6),
    ('2015-08-01 06:38:11', 6),
    ('2015-08-01 06:38:21', 6),
    ('2015-08-01 06:38:32', 6),
    ('2015-08-01 06:38:31', 6),
    ('2015-08-01 06:38:38', 6),
    ('2015-08-01 06:38:39', 6),
    ('2015-08-01 06:38:40', 6),
    ('2015-08-01 06:38:43', 6),
    ('2015-08-01 06:38:59', 6),
    ('2015-08-01 06:39:40', 6),
    ('2015-08-01 06:41:19', 6),
    ('2015-08-01 06:41:23', 6),
    ('2015-08-01 06:41:33', 6),
    ('2015-08-01 06:41:35', 6),
    ('2015-08-01 06:41:38', 6),
    ('2015-08-01 06:42:53', 6),
    ('2015-08-01 06:44:15', 6),
    ('2015-08-01 06:44:47', 6),
    ('2015-08-01 06:45:18', 6),
    ('2015-08-01 06:45:22', 6),
    ('2015-08-01 06:46:15', 6),
    ('2015-08-01 06:46:16', 6),
    ('2015-08-01 06:49:28', 6),
    ('2015-08-01 06:50:26', 6),
    ('2015-08-01 06:51:08', 6),
    ('2015-08-01 06:55:17', 6),
    ('2015-08-01 06:58:38', 6),
    ('2015-08-01 06:58:46', 6),
    ('2015-08-01 06:59:18', 6),
    ('2015-08-01 07:01:16', 7),
    ('2015-08-01 07:01:20', 7),
    ('2015-08-01 07:01:24', 7),
    ('2015-08-01 07:01:59', 7),
    ('2015-08-01 07:02:02', 7),
    ('2015-08-01 07:02:11', 7),
    ('2015-08-01 07:02:13', 7),
    ('2015-08-01 07:03:55', 7),
    ('2015-08-01 07:04:16', 7),
    ('2015-08-01 07:04:26', 7),
    ('2015-08-01 07:04:27', 7),
    ('2015-08-01 07:04:28', 7),
    ('2015-08-01 07:05:11', 7),
    ('2015-08-01 07:05:13', 7),
    ('2015-08-01 07:06:31', 7),
    ('2015-08-01 07:06:32', 7),
    ('2015-08-01 07:07:21', 7),
    ('2015-08-01 07:07:39', 7),
    ('2015-08-01 07:07:32', 7),
    ('2015-08-01 07:07:33', 7),
    ('2015-08-01 07:07:36', 7),
    ('2015-08-01 07:08:51', 7),
    ('2015-08-01 07:08:52', 7),
    ('2015-08-01 07:10:03', 7),
    ('2015-08-01 07:11:04', 7),
    ('2015-08-01 07:11:05', 7),
    ('2015-08-01 07:11:11', 7),
    ('2015-08-01 07:11:17', 7),
    ('2015-08-01 07:11:35', 7),
    ('2015-08-01 07:12:15', 7),
    ('2015-08-01 07:14:31', 7),
    ('2015-08-01 07:14:43', 7),
    ('2015-08-01 07:15:29', 7),
    ('2015-08-01 07:16:05', 7),
    ('2015-08-01 07:16:07', 7),
    ('2015-08-01 07:17:25', 7),
    ('2015-08-01 07:17:39', 7),
    ('2015-08-01 07:19:13', 7),
    ('2015-08-01 07:27:46', 7),
    ('2015-08-01 07:28:04', 7),
    ('2015-08-01 07:28:10', 7),
    ('2015-08-01 07:28:12', 7),
    ('2015-08-01 07:29:17', 7),
    ('2015-08-01 07:31:36', 7),
    ('2015-08-01 07:31:45', 7),
    ('2015-08-01 07:31:48', 7),
    ('2015-08-01 07:31:52', 7),
    ('2015-08-01 07:31:54', 7),
    ('2015-08-01 07:31:55', 7),
    ('2015-08-01 07:32:01', 7),
    ('2015-08-01 07:32:08', 7),
    ('2015-08-01 07:32:12', 7),
    ('2015-08-01 07:33:00', 7);

-- Drop the clustered index (HourPart dependency)
DROP INDEX cx 
ON #MyLogs;

-- Drop the column
ALTER TABLE #MyLogs
DROP COLUMN HourPart;

-- Replace with a computed column
ALTER TABLE #MyLogs
ADD HourPart AS DATEPART(HOUR, dtUtcDateTime);

-- Nonclustered index, for variety
CREATE NONCLUSTERED INDEX nc1
ON #MyLogs (HourPart)
INCLUDE (dtUtcDateTime);

DECLARE 
    @StartHour integer = 1, 
    @EndHour integer = 5;

SELECT * 
FROM #MyLogs AS ML
WHERE
    @StartHour < @EndHour
    AND ML.HourPart >= @StartHour
    AND ML.HourPart <= @EndHour
UNION ALL
SELECT * 
FROM #MyLogs AS ML
WHERE
    @StartHour > @EndHour 
    AND ML.HourPart >= @StartHour
UNION ALL
SELECT * 
FROM #MyLogs AS ML
WHERE
    @StartHour > @EndHour 
    AND ML.HourPart <= @EndHour;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results