Web Development

Ask something
Question Locked

Prices between two days

0 helpful
3 replies

by alexmq8

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

This is my data base (availability)

Datefrom Dateto Hotelid Roomid Singleprice Doubleprice

10/1/2008 10/5/2008 H101 R101 10 20

10/7/2008 10/10/2008 H101 R101 5 7

10/11/2008 10/15/2008 H101 R101 7 9

10/16/2008 10/20/2008 H101 R101 6 9
If i search for 10/2/2008 to 10/8/2008 , result is not available (because 10/6 /2008 price is not available
If i search for 10/8/2008 to 10/19/2008 , result is available
Answer as 10/8/2008 to 10/19/2008 is available (11 days)
And single price =(5+7+6)/noofdays(11)
And doubleprice=(7+9+9)/noofdays(11)

Plz help me for writing one line sql query for this?

3 replies

by tony hopkinson

Your schema is causing you problems big style.

You need to put availability in one table and price in another.

Thay have nothing whatever to do with each other. Why are you doing availability and cost in one operation?

If I rang up a hotel and said do you have a room. If you don't, the price is irrelevant. Also I'm booking a room, not room 101, 102 will do fine, unless it's much more expensive.

Maybe I don't mind swapping from 101 to 102 to save a few quid.

If you take price out of your availability table, the date ranges go contiguous, if you then join to prices, how you do that depends on how pricing is done, but worst case it will be ranged by data and type of room in a hotel.

Then you may get what you want, and a much more efficient database.

HtHs

by alexmq8

Tks for the replay,

I understand your question
if i use one table for date from and to
and another table for prices,

the same problem is happining there also. because, if i take date from and to from table one and how i take price from the second table ?

This discussion is closed to new replies.