How to query overlapping requests

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 2008 Forums

Transact-SQL (2008)

How to query overlapping requests

Author

Topic

jasongrant15
Starting Member

2 Posts

Posted - 2012-01-24 : 13:45:04

Good morning everyone, thanks for taking the time out of your day to assist with my question.

I have been trying to figure this out for a few days. I need to be able to query overlapping dates in my sql database, for example. Lets say someone requested a water pump to run from

1/20/2012 5:15pm to 1/25/2012 5:15PM

and someone else wanted to run the same pump from

1/22/12 8:AM to 1/22/2012 4:PM

how would I query the data so I could see that the pump was already in use on the 22nd day? Here is what I currently have but it does not work.

SELECT SUM(flow_gpm) as gpmTotal FROM water_request WHERE (TO_start_time BETWEEN '01/22/2012 8:00:00 AM' AND '01/22/2012 4:00:00 PM') OR (TO_end_time BETWEEN '01/22/2012 8:00:00 AM' AND '01/22/2012 4:00:00 PM

The only thing this query succeeds in is noticing the other pump request if one of the dates falls in the specified range. Example:

SELECT SUM(flow_gpm) as gpmTotal FROM water_request WHERE (TO_start_time BETWEEN '01/25/2012 2:00:00 PM' AND '01/25/2012 10:00:00 PM') OR (TO_end_time BETWEEN '01/25/2012 2:00:00 PM' AND '01/25/2012 10:00:00 PM')

If I need to clarify please let me know, thank you very much.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-24 : 14:03:00

Would a WHERE clause such as this work for you?

WHERE
	TO_start_time <= '1/22/2012 4:PM'
	AND TO_end_time >= '1/22/12 8:AM'

jasongrant15
Starting Member

2 Posts

Posted - 2012-01-24 : 14:07:21

Yeah... that would do it, however in another example it was shown a little differently

I did another search on the forums with the word "overlapping" and their is a solution with that answer. I don't know why I was having so many problems thinking of that... Thanks so much, have a great day.

example: SELECT COUNT(*) FROM tbl WHERE StartDate <= @EndDate AND EndDate >= @StartDate

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23593

Thanks sunitabeck

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources