December 7, 2016 at 7:20 am
Okay, so I have a new report that needs to get data for telephone calls on certain DNIS lines. They want start date and end date - no problem. But they also only want calls that are from 8AM to 5PM. We have several records for phone lines during off hours also. I can do a start date and end date, but how would I filter for that time frame for every date in between? The only thing I can think of is do some DATEPART concatenation and create a temp table with using just the date and then a field for the time and deleting records from that table where there are calls outside of that time range, then remerging those records into the returned dataset?
Is there any other easier way to do this? Thanks
December 7, 2016 at 8:13 am
The normal approach is to join to a calendar table for the datetime between the given times.
December 7, 2016 at 8:14 am
Here's an option that can help.
IF OBJECT_ID('dbo.DateTimeTable') IS NOT NULL
DROP TABLE dbo.DateTimeTable;
CREATE TABLE dbo.DateTimeTable( MyDatetime datetime);
CREATE CLUSTERED INDEX CI_MyDatetime ON dbo.DateTimeTable(MyDatetime);
INSERT INTO dbo.DateTimeTable
SELECT TOP 10000 CAST( '2010' AS datetime) + RAND(CHECKSUM(NEWID()))*10000
FROM sys.all_columns a, sys.all_columns b;
DECLARE @StartDate datetime = '20161201',
@EndDate datetime = '20161210';
SET STATISTICS XML ON;
SELECT *
FROM dbo.DateTimeTable
WHERE MyDatetime >= @StartDate
AND MyDatetime < @EndDate + 1
AND CAST(MyDatetime AS time) >= '08:00'
AND CAST(MyDatetime AS time) < '17:00';
SET STATISTICS XML OFF;
DROP TABLE dbo.DateTimeTable;
December 7, 2016 at 8:18 am
Ken McKelvey (12/7/2016)
The normal approach is to join to a calendar table for the datetime between the given times.
I'm not sure about this approach. If the datetime column can have any time, then the calendar table would be too large to handle joins.
December 7, 2016 at 8:43 am
The logic shouldn't be too complicated add your normal filter in the where clause for the start and end dates which it sounds like you already have then add another filter that looks something like
AND DATEDIFF(hour, DATEADD(day, 0, DATEDIFF(day, 0, CALL_DATE_TIME)), CALL_DATE_TIME) >= 8 AND DATEDIFF(hour, DATEADD(day, 0, DATEDIFF(day, 0, CALL_DATE_TIME)), CALL_DATE_TIME) < 17
Depending on the volume of calls there might be some performance tweeking that would need to be done.
December 7, 2016 at 8:54 am
A minor point, but if you don't need much precision on the seconds this will save you 4 bytes per row that is processed during the actual CONVERTs:
DECLARE @StartDate datetime = '20161201',
@EndDate datetime = '20161210';
SELECT *
FROM dbo.DateTimeTable
WHERE MyDatetime >= @StartDate
AND MyDatetime < @EndDate + 1
AND CAST(MyDatetime AS time(2)) >= CAST('08:00' AS time(2))
AND CAST(MyDatetime AS time(2)) < CAST('17:00' AS time(2));
Defaulted time datatype is time(7), which is 5 bytes in size. time(0) - time(3) takes only 3 bytes. time(0) is what most people really should use I think because they only have whole seconds, not fractional ones.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 7, 2016 at 8:58 am
ZZartin (12/7/2016)
The logic shouldn't be too complicated add your normal filter in the where clause for the start and end dates which it sounds like you already have then add another filter that looks something like
AND DATEDIFF(hour, DATEADD(day, 0, DATEDIFF(day, 0, CALL_DATE_TIME)), CALL_DATE_TIME) >= 8 AND DATEDIFF(hour, DATEADD(day, 0, DATEDIFF(day, 0, CALL_DATE_TIME)), CALL_DATE_TIME) < 17Depending on the volume of calls there might be some performance tweeking that would need to be done.
So much code when you're only need this:
DATEPART(HH, MyDatetime)
Either way, that would return incorrect results. I'll let you figure out why.;-)
December 7, 2016 at 3:23 pm
...
WHERE datetime_column >= @StartDate_set_to_0800_AM AND
datetime_column < DATEADD(DAY, 1, @EndDate_set_to_midnight) AND
DATEPART(HOUR, datetime_column) BETWEEN 8 AND 16
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply