Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 > @utcStartDate AND vm.dtUTCDateTime < @utcEndDate
        AND  DATEPART(HOUR, vm.dtUtcDateTime) >  @iStartHour

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

        Where vm.dtUTCDateTime > @utcStartDate AND vm.dtUTCDateTime < @utcEndDate
        AND  DATEPART(HOUR, vm.dtUtcDateTime) <  @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.

share|improve this question
up vote 6 down vote accepted

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:

  1. If @StartHour is before the @EndHour
    Simply return rows 'between' those two values
  2. 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 < @EndHour
    AND ML.HourPart > @StartHour
    AND ML.HourPart < @EndHour
UNION ALL
SELECT * 
FROM dbo.MyLogs AS ML
WHERE
    @StartHour > @EndHour 
    AND ML.HourPart > @StartHour
UNION ALL
SELECT * 
FROM dbo.MyLogs AS ML
WHERE
    @StartHour > @EndHour 
    AND ML.HourPart < @EndHour;

Execution plan

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 < and > comparison operators to <= and >= respectively.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.