Searching for times within a time range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Searching for times within a time range

Author

Topic

Speckled
Starting Member

13 Posts

Posted - 2009-02-11 : 09:09:45

Hi,

Hopefully someone can help me with this. Basically, it's not as simple as I'd have hoped.

I have a similiar table layout to this:

DAY STARTTIME ENDTIME
Monday 10/10/2008 10:00:00 10/10/2008 22:00:00
Tuesday 10/10/2008 10:00:00 10/10/2008 22:00:00
Wednesday 10/10/2008 10:00:00 10/10/2008 22:00:00
Thursday 10/10/2008 10:00:00 10/10/2008 22:00:00

The user will enter two paramaters (a start time and an end time) and I need it to specify which times and days they someone is available.

I'm not bothered about the dates, so currently on working with the time values.

I can search where @StartDate > StartTime but that won't be good enough.

If someone, for example, searches between 09:00 - 14:00 then ALL of the above records should show.

If they search for 23:00 - 24:00 then NO records should show.

If they search for 13:00 - 15:00 then ALL records should show.

The whole problem I'm having is with the 09:00 figure.

I need a list of people available during them times, even if they do not start at that time, but are available during the specified range.

Any help appreciated. :)

Thanks,
Ricky

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 09:14:31

do you mean this?

SELECT * FROM Table WHERE GETDATE() BETWEEN STARTTIME AND ENDTIME

Speckled
Starting Member

13 Posts

Posted - 2009-02-11 : 09:19:55

Afraid not, that would be the case if I was searching specifically between the StartTime and EndTime.

But, they would specify 09:00 - 14:00, and the table rows have data 10:00 - 22:00.

Therefore, 09:00 is not between 10:00 - 22:00 so no results would be returned, however I need to see that row because someone is available during that time. (09:00, 10:00, 11:00, 12:00, 13:00, 14:00).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 09:21:54

quote:
Originally posted by Speckled

Afraid not, that would be the case if I was searching specifically between the StartTime and EndTime.

But, they would specify 09:00 - 14:00, and the table rows have data 10:00 - 22:00.

Therefore, 09:00 is not between 10:00 - 22:00 so no results would be returned, however I need to see that row because someone is available during that time.

so what you want is to see who are all available for at least one of slots in day in which you're running query?

Speckled
Starting Member

13 Posts

Posted - 2009-02-11 : 09:23:09

Yup, so they are available in atleast one of them hourly slots. :) So I need to the row. :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 09:26:14

[code]DECLARE @Sample TABLE
(
DOW VARCHAR(10),
STARTTIME DATETIME,
ENDTIME DATETIME
)

INSERT @Sample
SELECT 'Monday', '10/10/2008 10:00:00', '10/10/2008 22:00:00' UNION ALL
SELECT 'Tuesday', '10/10/2008 10:00:00', '10/10/2008 22:00:00' UNION ALL
SELECT 'Wednesday', '10/10/2008 10:00:00', '10/10/2008 22:00:00' UNION ALL
SELECT 'Thursday', '10/10/2008 10:00:00', '10/10/2008 22:00:00'

DECLARE @From DATETIME,
@To DATETIME

-- Case 1
SELECT @From = '20081010 09:00',
@To = '20081010 14:00'

SELECT *
FROM @Sample
WHERE STARTTIME < @To
AND ENDTIME > @From

-- Case 2
SELECT @From = '20081010 23:00',
@To = '20081011 00:00'

SELECT *
FROM @Sample
WHERE STARTTIME < @To
AND ENDTIME > @From

-- Case 3
SELECT @From = '20081010 13:00',
@To = '20081010 15:00'

SELECT *
FROM @Sample
WHERE STARTTIME < @To
AND ENDTIME > @From[/code]

E 12°55'05.63"
N 56°04'39.26"

Speckled
Starting Member

13 Posts

Posted - 2009-02-11 : 09:30:14

quote:

Originally posted by Peso

DECLARE	@Sample TABLE
	(
		DOW VARCHAR(10),
		STARTTIME DATETIME,
		ENDTIME DATETIME
	)

INSERT	@Sample
SELECT	'Monday', '10/10/2008 10:00:00', '10/10/2008 22:00:00' UNION ALL
SELECT	'Tuesday', '10/10/2008 10:00:00', '10/10/2008 22:00:00' UNION ALL
SELECT	'Wednesday', '10/10/2008 10:00:00', '10/10/2008 22:00:00' UNION ALL
SELECT	'Thursday', '10/10/2008 10:00:00', '10/10/2008 22:00:00'

DECLARE	@From DATETIME,
	@To DATETIME

-- Case 1
SELECT	@From = '20081010 09:00',
	@To = '20081010 14:00'

SELECT	*
FROM	@Sample
WHERE	STARTTIME < @To
	AND ENDTIME > @From

-- Case 2
SELECT	@From = '20081010 23:00',
	@To = '20081011 00:00'

SELECT	*
FROM	@Sample
WHERE	STARTTIME < @To
	AND ENDTIME > @From

-- Case 3
SELECT	@From = '20081010 13:00',
	@To = '20081010 15:00'

SELECT	*
FROM	@Sample
WHERE	STARTTIME < @To
	AND ENDTIME > @From

E 12°55'05.63"
N 56°04'39.26"

Wow! That's spot on, works perfectly.

Thanks for both your help :) Clever stuff. :)

Doron
Starting Member

6 Posts

Posted - 2009-02-12 : 06:34:58

You can use the simple Time-Overlap formula:

SELECT *
FROM @Sample
WHERE (STARTTIME - @To) * (ENDTIME - @From) < 0

Nob Hill Software - tools for database people (+ free stuff!) Nob Hill Software - tools for database people (+ free stuff!)
www.nobhillsoft.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 06:43:10

And if you compare your query against my suggestion for a table with several records,
my suggestion will use any present index while your suggestion want to, but can't...

Compare the queries by inspecting Execution Plan and SQL Profiler.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources