Query to get logs between Start and End Night hour I am working on Procedure where we pass in the Night Start Hour and the Night End Hour, I need to get the Logs between these hours.

So:


If the StartHour = 18 and EndHour = 5, the Stored Procedure should return all logs between 6:00 PM and 5:00 AM.
If the StartHour = 1 and EndHour = 5, the Stored Procedure should return all logs between 1:00 AM and 5:00 AM.


I tried creating a SQL Fiddle but there seems to be a problem with creating the fiddle in either SQL Server 2014 or SQL Server 2008 Today.

I AM USING SQL SERVER 2014 - But Fiddle is in MySQL

(I have created one in MySQL to display the data) [Wrong Fiddle: http://sqlfiddle.com/#!9/6f0a6/1]

Correct: http://sqlfiddle.com/#!9/6467d8/3

This is what my Script looks like:

Select 
    * 
FROM
(

    Select 
        iVehicleMonitoringId,
        dtUtcDateTime,
        HourPart = DATEPART(HOUR, dtUtcDateTime)
        From VehicleMonitoringLog vm

        Where vm.dtUTCDateTime &gt; @utcStartDate AND vm.dtUTCDateTime &lt; @utcEndDate
        AND  DATEPART(HOUR, vm.dtUtcDateTime) &gt;  @iStartHour

UNION
    Select 
        vm.iVehicleMonitoringId,
        dtUtcDateTime,
        HourPart = DATEPART(HOUR, dtUtcDateTime)
        From VehicleMonitoringLog vm

        Where vm.dtUTCDateTime &gt; @utcStartDate AND vm.dtUTCDateTime &lt; @utcEndDate
        AND  DATEPART(HOUR, vm.dtUtcDateTime) &lt;  @iEndHour
) As G
Order by dtUtcDateTime 


But this query will be wrong for the second condition. Because 18,19,20.... are all greater than 1, so I am confused on how to write this query.
Sample data

CREATE TABLE dbo.MyLogs
    (dtUtcDateTime datetime, HourPart int);

CREATE CLUSTERED INDEX cx 
ON dbo.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);


Solution

The basic idea here is quite simple:


If @StartHour is before the @EndHour
Simply return rows 'between' those two values
If @EndHour is before the @StartHour:
Return rows less than @EndHour; and
Return rows greater than @StartHour


The logic can be implemented quite naturally as:

DECLARE 
    @StartHour integer = 1, -- or 18 
    @EndHour integer = 5; -- or 5

SELECT * 
FROM dbo.MyLogs AS ML
WHERE
    @StartHour &lt; @EndHour
    AND ML.HourPart &gt; @StartHour
    AND ML.HourPart &lt; @EndHour
UNION ALL
SELECT * 
FROM dbo.MyLogs AS ML
WHERE
    @StartHour &gt; @EndHour 
    AND ML.HourPart &gt; @StartHour
UNION ALL
SELECT * 
FROM dbo.MyLogs AS ML
WHERE
    @StartHour &gt; @EndHour 
    AND ML.HourPart &lt; @EndHour;


Execution plan



The Filters in this plan are start-up filters. At execution time, either the top branch or the two lower branches will be executed depending on the values of the local variables at that time. Splitting the query into three simple sections allows an index on HourPart to be used effectively.

Try it on the Stack Exchange Data Explorer

Or try the updated version that converts the supplied sample data to have the HourPart column as an indexed computed column.

If the start and end parameters were intended to be inclusive, simply change the &lt; and &gt; comparison operators to &lt;= and &gt;= respectively.