June 16, 2014 at 5:16 am
I am trying to create a query where you need to select for records using a date range and to only include records within a specific time range for each day within the date range. I have done this successfully for time range that needs to occur within the day but, I need help when the time range needs to be cross-midnight.
For example, I have a table of Calls and each call has a Call Date. So, I want to select a date range of 5 days or more and to include a time interval of 8 AM to 5 PM during the day. This was fine and was able to accomplish this with no problem. However, if we want to look for calls between the times of 11 PM and 2 AM for each day then what is the appropriate form of the query.
For example, the following code worked okay when the date range is 8 AM to 5 PM (within the day):
declare @startdate datetime
declare @enddate datetime
declare @starttime datetime
declare @endtime datetime
set @startdate = '12/01/2011'
set @enddate = '12/06/2011'
set @starttime = '8:00 AM'
set @endtime = '5:00 PM'
select @startdate as start_date, @enddate as end_date
select @starttime as start_time, @endtime as end_time
select CallDate, Extension, CallerID, CalledID
from RecordedMsgs
where (CallDate >= @startdate and calldate < @enddate)
and ( (CONVERT(varchar, CallDate,108)) >= @starttime and (CONVERT(varchar, CallDate,108)) < @endtime )
order by CallDate
However, in my code above there are no records returned if @starttime = '11:00 PM' & @endtime = '2:00 AM'.
Can anyone suggest the appropriate way to configure a query to deal with the cross-midnight time interval?
June 16, 2014 at 8:09 am
DROP TABLE #RecordedMsgs
;WITH RecordedMsgs (CallDate, Extension, CallerID, CalledID) AS (
SELECT '2011-12-04 01:59:59.999', 'night', 1, 20 UNION ALL
SELECT '2011-12-04 02:00:00.000', 'morn', 1, 30 UNION ALL
SELECT '2011-12-04 07:59:59.999', 'morn', 2, 30 UNION ALL
SELECT '2011-12-04 08:00:00.000', 'day', 1, 10 UNION ALL
SELECT '2011-12-04 16:59:59.999', 'day', 2, 10 UNION ALL
SELECT '2011-12-04 17:00:00.000', 'night', 2, 20 UNION ALL
SELECT '2011-12-05 07:59:59.999', 'morn', 3, 30 UNION ALL
SELECT '2011-12-05 08:00:00.000', 'day', 3, 10 UNION ALL
SELECT '2011-12-05 16:59:59.999', 'day', 4, 10 UNION ALL
SELECT '2011-12-05 17:00:00.000', 'night', 3, 20
)
SELECT * INTO #RecordedMsgs FROM RecordedMsgs
-------------------------------------------------------------------
declare @startdate datetime, @enddate datetime
SELECT @startdate = '12/01/2011', @enddate = '12/06/2011'
declare @starttime time, @endtime time
-------------------------------------------------------------------
SELECT @starttime = '8:00 AM', @endtime = '5:00 PM'
SELECT CallDate, Extension, CallerID, CalledID
FROM #RecordedMsgs
WHERE CallDate >= @startdate
AND CallDate < @enddate
AND (
(@starttime < @endtime AND CAST(CallDate AS TIME) >= @starttime AND CAST(CallDate AS TIME) < @endtime)
OR
(@starttime > @endtime AND (CAST(CallDate AS TIME) >= @starttime OR CAST(CallDate AS TIME) < @endtime))
)
ORDER BY CallDate
--------------------------------------------------------------------
SELECT @starttime = '5:00 PM', @endtime = '2:00 AM'
SELECT CallDate, Extension, CallerID, CalledID
FROM #RecordedMsgs
WHERE CallDate >= @startdate
AND CallDate < @enddate
AND (
(@starttime < @endtime AND CAST(CallDate AS TIME) >= @starttime AND CAST(CallDate AS TIME) < @endtime)
OR
(@starttime > @endtime AND (CAST(CallDate AS TIME) >= @starttime OR CAST(CallDate AS TIME) < @endtime))
)
ORDER BY CallDate
--------------------------------------------------------------------
SELECT @starttime = '2:00 AM', @endtime = '8:00 AM'
SELECT CallDate, Extension, CallerID, CalledID
FROM #RecordedMsgs
WHERE CallDate >= @startdate
AND CallDate < @enddate
AND (
(@starttime < @endtime AND CAST(CallDate AS TIME) >= @starttime AND CAST(CallDate AS TIME) < @endtime)
OR
(@starttime > @endtime AND (CAST(CallDate AS TIME) >= @starttime OR CAST(CallDate AS TIME) < @endtime))
)
ORDER BY CallDate
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 16, 2014 at 8:33 am
Nice work Chris,
I would make a small modification because it's losing values from the last day.
;WITH RecordedMsgs (CallDate, Extension, CallerID, CalledID) AS (
SELECT '2011-12-04 01:59:59.999', 'night', 1, 20 UNION ALL
SELECT '2011-12-04 02:00:00.000', 'morn', 1, 30 UNION ALL
SELECT '2011-12-04 07:59:59.999', 'morn', 2, 30 UNION ALL
SELECT '2011-12-04 08:00:00.000', 'day', 1, 10 UNION ALL
SELECT '2011-12-04 16:59:59.999', 'day', 2, 10 UNION ALL
SELECT '2011-12-04 17:00:00.000', 'night', 2, 20 UNION ALL
SELECT '2011-12-05 07:59:59.999', 'morn', 3, 30 UNION ALL
SELECT '2011-12-05 08:00:00.000', 'day', 3, 10 UNION ALL
SELECT '2011-12-05 16:59:59.999', 'day', 4, 10 UNION ALL
SELECT '2011-12-05 17:00:00.000', 'night', 3, 20 UNION ALL
SELECT '2011-12-06 03:00:00.000', 'night', 4, 40
)
SELECT * INTO #RecordedMsgs FROM RecordedMsgs
-------------------------------------------------------------------
declare @startdate datetime, @enddate datetime
SELECT @startdate = '20111201', @enddate = '20111206'
declare @starttime time, @endtime time
-------------------------------------------------------------------
SELECT @starttime = '2:00 AM', @endtime = '8:00 AM'
SELECT @startdate = DATEADD(SS, DATEDIFF(SS, 0, @starttime), @startdate),
@enddate = DATEADD(SS, DATEDIFF(SS, 0, @endtime), @enddate)
SELECT CallDate, Extension, CallerID, CalledID
FROM #RecordedMsgs
WHERE CallDate >= @startdate
AND CallDate < @enddate
AND (
(@starttime < @endtime AND CAST(CallDate AS TIME) >= @starttime AND CAST(CallDate AS TIME) < @endtime)
OR
(@starttime > @endtime AND (CAST(CallDate AS TIME) >= @starttime OR CAST(CallDate AS TIME) < @endtime))
)
ORDER BY CallDate
June 16, 2014 at 8:51 am
Luis Cazares (6/16/2014)
Nice work Chris,I would make a small modification because it's losing values from the last day.
;WITH RecordedMsgs (CallDate, Extension, CallerID, CalledID) AS (
SELECT '2011-12-04 01:59:59.999', 'night', 1, 20 UNION ALL
SELECT '2011-12-04 02:00:00.000', 'morn', 1, 30 UNION ALL
SELECT '2011-12-04 07:59:59.999', 'morn', 2, 30 UNION ALL
SELECT '2011-12-04 08:00:00.000', 'day', 1, 10 UNION ALL
SELECT '2011-12-04 16:59:59.999', 'day', 2, 10 UNION ALL
SELECT '2011-12-04 17:00:00.000', 'night', 2, 20 UNION ALL
SELECT '2011-12-05 07:59:59.999', 'morn', 3, 30 UNION ALL
SELECT '2011-12-05 08:00:00.000', 'day', 3, 10 UNION ALL
SELECT '2011-12-05 16:59:59.999', 'day', 4, 10 UNION ALL
SELECT '2011-12-05 17:00:00.000', 'night', 3, 20 UNION ALL
SELECT '2011-12-06 03:00:00.000', 'night', 4, 40
)
SELECT * INTO #RecordedMsgs FROM RecordedMsgs
-------------------------------------------------------------------
declare @startdate datetime, @enddate datetime
SELECT @startdate = '20111201', @enddate = '20111206'
declare @starttime time, @endtime time
-------------------------------------------------------------------
SELECT @starttime = '2:00 AM', @endtime = '8:00 AM'
SELECT @startdate = DATEADD(SS, DATEDIFF(SS, 0, @starttime), @startdate),
@enddate = DATEADD(SS, DATEDIFF(SS, 0, @endtime), @enddate)
SELECT CallDate, Extension, CallerID, CalledID
FROM #RecordedMsgs
WHERE CallDate >= @startdate
AND CallDate < @enddate
AND (
(@starttime < @endtime AND CAST(CallDate AS TIME) >= @starttime AND CAST(CallDate AS TIME) < @endtime)
OR
(@starttime > @endtime AND (CAST(CallDate AS TIME) >= @starttime OR CAST(CallDate AS TIME) < @endtime))
)
ORDER BY CallDate
Cheers mate.
That was deliberate, though not necessarily correct: CallDate < @enddate from the OP's original query.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 16, 2014 at 9:00 am
Dear Luis and Chris,
That was perfect. It was exactly the solution I was looking for. Thank you very much.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply