Date Ranges

Question

Date Ranges

BSavoie

SSCertifiable

Points: 6309
More actions
January 4, 2005 at 10:05 am

#74831

Given a table that keeps track of hotel reservations that includes the columns "GuestName, "ArrDate", and "DepDate", I need a tsql query that will return all the records that will be "in-house" given a specified date range. For example I would like to see all the Guests that will be in house 8/1/05 - 8/10/05. This one is giving be fits. I can brute force it with a complicated where clause that handles all possible scenarios, but there must be a better way.
.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

You have a few cases that you need to handle. An OR will do it, but there may be better ways (more efficient). So you need to handle

- people arriving before 8/1 and leaving in midstream

- people arriving before 8/1 and leaving after 8/10

- people arriving after 8/1 and leaving in midstream

- people arriving after 8/1 and leaving after 8/10

select guestname

from reservations

where

( arrdate 8/10/05')

OR ( arrdate < 8/1/05' and depdate '8/1/05 and depdate '8/1/05' and arrdate '8/10/05')

BSavoie SSCertifiable Points: 6309 More actions · Answer 2

And people arriving after 8/1 and leaving before 8/10.

That's what I meant by brute force. I was hoping for a cleaner solution. I know I've seen it done before with just one "and" or "or". I can't remember which.

Thanks for the quick response Steve!

.

BSavoie SSCertifiable Points: 6309 More actions · Answer 3

Feel pretty stupid answering my own question, but I think this does the trick if anyone else is interested.

DepDate >= '8/1/04' AND ArrDate =< '8/10/04'

.

Edwin Frydendall SSCrazy Points: 2361 More actions · Answer 4

From reading your post it looks like you want to know guests who will be in the hotel for at least one day between 8/1/05 and 8/10/05. It also looks like it doesn't matter when they will be checking out. Try the following query:

Select <ColumnList> From <Table> Where ArrDate Between '08/01/05' and '08/10/05'

BSavoie SSCertifiable Points: 6309 More actions · Answer 5

That doesn't handle people that arrive before 8/1/05 and depart before, on, or after 8/10/05. I'm pretty sure my previous post does the trick. If you find a hole in that, let me know. Thanks!

.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Brian I can't find any case where your solution doesn't work... let me know if you find any but I don't see that hapenning.

Nice trick.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 7

Brian wrote: > Feel pretty stupid answering my own question, but I think this does the trick if anyone else is interested. DepDate >= '8/1/04' AND ArrDate =< '8/10/04'

I wouldn't feel so stupid... that's pretty smart. I drew it out on paper for the six different possibilities and it works every time. Thanks for sharing the answer....

         8/1           8/10
          |             |
     <--------->        |             In
          |      <----------->        In
     <----------------------->        In
          |   <---->    |             In
  <--->   |             |         NOT In
          |             | <---->  NOT In

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)