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)
 Very difficult business logic question help needed

Author  Topic 

sqlchiq
Posting Yak Master

133 Posts
Posted - 2008-08-20 : 04:04:28
I have a table of room availabilities that needs logic coded in it.

The 'executiontime' column is what day we check availability on, and the checkindate is for which specific day.

We only care about Sat and Wed availability. We check Saturday availability on Wednesdays and Saturdays and we check Wednesday availability on Sundays and Wednesdays. In other words on Saturdays it checks Saturdays, on Sundays it checks Wednesdays, and on Wednesdays it checks both Saturdays and Wednesdays. And it's always checking for that same week, next week, and 4 weeks from now.

so for example on 8/16 (a Saturday) it checked for saturdays on the 8/16th (same week), 8/23 (next week) and 9/13 (4 weeks from 8/16).
Therefore on 8/20 (a wed), it will check for 8/20, 8/23, 8/27, 8/30, 9/17, and 9/20 (Saturdays and Wednesdays for that same week, next week, and 4 weeks from now.

If the room is available it will return the rate, but if it is not available it will return nothing (no entry). If the entire hotel is sold out for that day it will return nothing for the entire hotel (no entry at all). If a single specific room is sold out, it will not appear on the room list (we also split up the availability via rooms).

Basically I have to code logic that fills up these blank sold out rows, but it needs to match up the logic I described above with the dates and the sold out logic. Which is where I need some help with, if anyone could help that would be of tremendous help and greatly appreciated.

For when specific roomtypes are sold out, one idea is to do something like matching up the data with another table that has all the hotels and different roomtypes. So for each day that should be there, it will match up the hotel and roomtype with this table, if its not there then its sold out for that day

In addition, if there exists more than one entry for a checkindate at a hotel for a specific room at when it is checked, we will take the lowest price.


Table of hotels and rooms

Morgans Hotel Standard Queen
Morgans Hotel Suite King
Morgans Hotel Suite Loft King
Morgans Hotel Superior Double Twin
Morgans Hotel Superior Queen
Other Innstandard etc
Other Inndeluxe etc
Other Innsuperior etc


Table of Data...

Morgans Hotel Standard Queen Sep 13 2008 12:00AM 659.00 Aug 13 2008 12:00AM 2208
Morgans Hotel Standard Queen Sep 13 2008 12:00AM 369.00 Aug 13 2008 12:00AM 2192
Morgans Hotel Standard Queen Sep 13 2008 12:00AM 369.00 Aug 16 2008 12:00AM 3749
Morgans Hotel Standard Queen Sep 13 2008 12:00AM 659.00 Aug 16 2008 12:00AM 3754
Morgans Hotel Suite King Sep 10 2008 12:00AM 809.00 Aug 13 2008 12:00AM 2142
Morgans Hotel Suite King Sep 10 2008 12:00AM 629.00 Aug 13 2008 12:00AM 2138
Morgans Hotel Suite King Sep 13 2008 12:00AM 809.00 Aug 13 2008 12:00AM 2217
Morgans Hotel Suite King Sep 13 2008 12:00AM 479.00 Aug 13 2008 12:00AM 2201
Morgans Hotel Suite King Sep 13 2008 12:00AM 809.00 Aug 16 2008 12:00AM 3757
Morgans Hotel Suite King Sep 13 2008 12:00AM 479.00 Aug 16 2008 12:00AM 3752
Morgans Hotel Suite Loft King Sep 10 2008 12:00AM 689.00 Aug 13 2008 12:00AM 2139
Morgans Hotel Suite Loft King Sep 10 2008 12:00AM 879.00 Aug 13 2008 12:00AM 2143
Morgans Hotel Suite Loft King Sep 13 2008 12:00AM 529.00 Aug 13 2008 12:00AM 2205
Morgans Hotel Suite Loft King Sep 13 2008 12:00AM 879.00 Aug 13 2008 12:00AM 2220
Morgans Hotel Suite Loft King Sep 13 2008 12:00AM 529.00 Aug 16 2008 12:00AM 3753
Morgans Hotel Suite Loft King Sep 13 2008 12:00AM 879.00 Aug 16 2008 12:00AM 3758
Morgans Hotel Superior Double Twin Sep 10 2008 12:00AM 539.00 Aug 13 2008 12:00AM 2137
Morgans Hotel Superior Double Twin Sep 10 2008 12:00AM 689.00 Aug 13 2008 12:00AM 2141
Morgans Hotel Superior Double Twin Sep 13 2008 12:00AM 399.00 Aug 13 2008 12:00AM 2198
Morgans Hotel Superior Double Twin Sep 13 2008 12:00AM 689.00 Aug 13 2008 12:00AM 2211
Morgans Hotel Superior Double Twin Sep 13 2008 12:00AM 689.00 Aug 16 2008 12:00AM 3756
Morgans Hotel Superior Double Twin Sep 13 2008 12:00AM 399.00 Aug 16 2008 12:00AM 3751
Morgans Hotel Superior Queen Sep 10 2008 12:00AM 539.00 Aug 13 2008 12:00AM 2136
Morgans Hotel Superior Queen Sep 10 2008 12:00AM 689.00 Aug 13 2008 12:00AM 2140
Morgans Hotel Superior Queen Sep 13 2008 12:00AM 689.00 Aug 13 2008 12:00AM 2215
Morgans Hotel Superior Queen Sep 13 2008 12:00AM 399.00 Aug 13 2008 12:00AM 2195
Morgans Hotel Superior Queen Sep 13 2008 12:00AM 399.00 Aug 16 2008 12:00AM 3750
Morgans Hotel Superior Queen Sep 13 2008 12:00AM 689.00 Aug 16 2008 12:00AM 3755



Desired Result SEt

Morgans Hotel Standard Queen Aug 13 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Standard Queen Aug 16 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Standard Queen Aug 20 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Standard Queen Aug 23 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Standard Queen Sep 10 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Standard Queen Sep 13 2008 12:00AM 369 Aug 13 2008 12:00AM 2
Morgans Hotel Standard Queen Sep 13 2008 12:00AM 369 Aug 16 2008 12:00AM 3
Morgans Hotel Suite King Aug 13 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Suite King Aug 16 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Suite King Aug 20 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Suite King Aug 23 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Suite King Sep 10 2008 12:00AM 629 Aug 13 2008 12:00AM 6
Morgans Hotel Suite King Sep 13 2008 12:00AM 479 Aug 13 2008 12:00AM 8
Morgans Hotel Suite King Sep 13 2008 12:00AM 479 Aug 16 2008 12:00AM 10
Morgans Hotel Suite Loft King Aug 13 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Suite Loft King Aug 16 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Suite Loft King Aug 20 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Suite Loft King Aug 23 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Suite Loft King Sep 10 2008 12:00AM 689 Aug 13 2008 12:00AM 11
Morgans Hotel Suite Loft King Sep 13 2008 12:00AM 529 Aug 13 2008 12:00AM 13
Morgans Hotel Suite Loft King Sep 13 2008 12:00AM 529 Aug 16 2008 12:00AM 15
Morgans Hotel Superior Double Twin Aug 13 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Superior Double Twin Aug 16 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Superior Double Twin Aug 20 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Superior Double Twin Aug 23 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Superior Double Twin Sep 10 2008 12:00AM 539 Aug 13 2008 12:00AM 17
Morgans Hotel Superior Double Twin Sep 13 2008 12:00AM 399 Aug 13 2008 12:00AM 19
Morgans Hotel Superior Double Twin Sep 13 2008 12:00AM 399 Aug 16 2008 12:00AM 22
Morgans Hotel Superior Queen Aug 13 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Superior Queen Aug 16 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Superior Queen Aug 20 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Superior Queen Aug 23 2008 12:00AM Sold Out Aug 13 2008 12:00AM
Morgans Hotel Superior Queen Sep 10 2008 12:00AM 539 Aug 13 2008 12:00AM 23
Morgans Hotel Superior Queen Sep 13 2008 12:00AM 399 Aug 13 2008 12:00AM 26
Morgans Hotel Superior Queen Sep 13 2008 12:00AM 399 Aug 16 2008 12:00AM 27



As you can see, the script would be able to find the missing data and plug it in as Sold Out. Any help would be greatly greatly appreciated.



-- below is code to create a table with all the hotels and their roomtypes

create table testing(hotel nvarchar(150) null, roomtype nvarchar(4000) null)

insert into testing(hotel, roomtype) values('Morgans Hotel, New York, New York USA','Standard Queen');
insert into testing(hotel, roomtype) values('Morgans Hotel, New York, New York USA','Suite King');
insert into testing(hotel, roomtype) values('Morgans Hotel, New York, New York USA','Suite Loft King');
insert into testing(hotel, roomtype) values('Morgans Hotel, New York, New York USA','Superior Double Twin');
insert into testing(hotel, roomtype) values('Morgans Hotel, New York, New York USA','Superior Queen');
insert into testing(hotel, roomtype) values('Other Inn','standard etc');
insert into testing(hotel, roomtype) values('Other Inn','deluxe etc');
insert into testing(hotel, roomtype) values('Other Inn','superior etc');



--below is the code to create a table for the data


create table testing2(hotel nvarchar(150) null, roomtype nvarchar(4000) null, checkindate nvarchar(200) null, roomrate nvarchar(50) null, executiontime nvarchar(100) null, id_num int not null)

insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Standard Queen','Sep 13 2008 12:00AM','659','Aug 13 2008 12:00AM','1');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Standard Queen','Sep 13 2008 12:00AM','369','Aug 13 2008 12:00AM','2');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Standard Queen','Sep 13 2008 12:00AM','369','Aug 16 2008 12:00AM','3');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Standard Queen','Sep 13 2008 12:00AM','659','Aug 16 2008 12:00AM','4');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Suite King','Sep 10 2008 12:00AM','809','Aug 13 2008 12:00AM','5');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Suite King','Sep 10 2008 12:00AM','629','Aug 13 2008 12:00AM','6');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Suite King','Sep 13 2008 12:00AM','809','Aug 13 2008 12:00AM','7');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Suite King','Sep 13 2008 12:00AM','479','Aug 13 2008 12:00AM','8');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Suite King','Sep 13 2008 12:00AM','809','Aug 16 2008 12:00AM','9');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Suite King','Sep 13 2008 12:00AM','479','Aug 16 2008 12:00AM','10');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Suite Loft King','Sep 10 2008 12:00AM','689','Aug 13 2008 12:00AM','11');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Suite Loft King','Sep 10 2008 12:00AM','879','Aug 13 2008 12:00AM','12');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Suite Loft King','Sep 13 2008 12:00AM','529','Aug 13 2008 12:00AM','13');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Suite Loft King','Sep 13 2008 12:00AM','879','Aug 13 2008 12:00AM','14');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Suite Loft King','Sep 13 2008 12:00AM','529','Aug 16 2008 12:00AM','15');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Suite Loft King','Sep 13 2008 12:00AM','879','Aug 16 2008 12:00AM','16');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Superior Double Twin','Sep 10 2008 12:00AM','539','Aug 13 2008 12:00AM','17');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Superior Double Twin','Sep 10 2008 12:00AM','689','Aug 13 2008 12:00AM','18');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Superior Double Twin','Sep 13 2008 12:00AM','399','Aug 13 2008 12:00AM','19');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Superior Double Twin','Sep 13 2008 12:00AM','689','Aug 13 2008 12:00AM','20');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Superior Double Twin','Sep 13 2008 12:00AM','689','Aug 16 2008 12:00AM','21');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Superior Double Twin','Sep 13 2008 12:00AM','399','Aug 16 2008 12:00AM','22');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Superior Queen','Sep 10 2008 12:00AM','539','Aug 13 2008 12:00AM','23');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Superior Queen','Sep 10 2008 12:00AM','689','Aug 13 2008 12:00AM','24');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Superior Queen','Sep 13 2008 12:00AM','689','Aug 13 2008 12:00AM','25');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Superior Queen','Sep 13 2008 12:00AM','399','Aug 13 2008 12:00AM','26');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Superior Queen','Sep 13 2008 12:00AM','399','Aug 16 2008 12:00AM','27');
insert into testing2(hotel, roomtype, checkindate, roomrate, executiontime, id_num) values('Morgans Hotel','Superior Queen','Sep 13 2008 12:00AM','689','Aug 16 2008 12:00AM','28');

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-08-20 : 04:15:01
Where will you be having allocated information?
Go to Top of Page

sqlchiq
Posting Yak Master

133 Posts
Posted - 2008-08-20 : 09:41:39
quote:
Originally posted by visakh16

Where will you be having allocated information?


Not sure what you mean by that
Go to Top of Page

Pace
Constraint Violating Yak Guru

264 Posts
Posted - 2008-08-20 : 10:00:02
quote:
Originally posted by sqlchiq

quote:
Originally posted by visakh16

Where will you be having allocated information?


Not sure what you mean by that


IE a table definition which tells you which rooms are booked, so that you can compare whats available as to whats not.

So far we have, Hotels and Availability by my reckoning. So now we need some rooms that have been booked so we can get the query.

"Impossible is Nothing"
Go to Top of Page

sqlchiq
Posting Yak Master

133 Posts
Posted - 2008-08-20 : 10:07:45
quote:
Originally posted by Pace

quote:
Originally posted by sqlchiq

quote:
Originally posted by visakh16

Where will you be having allocated information?


Not sure what you mean by that


IE a table definition which tells you which rooms are booked, so that you can compare whats available as to whats not.

So far we have, Hotels and Availability by my reckoning. So now we need some rooms that have been booked so we can get the query.

"Impossible is Nothing"


Thats the problem, the code for the table testing2 above is the data for the rooms that are available. The only way we can deduce whether or not a room is booked or not is using some code intelligence.

We know which hotels, and which roomtypes exists (given in the first table named testing), and we know what checkindates we're looking for on which day.

In the testing2 table the 3rd column is the checkindate, and the 5th column is the day it was checked.

"We check Saturday availability on Wednesdays and Saturdays and we check Wednesday availability on Sundays and Wednesdays. In other words on Saturdays it checks Saturdays, on Sundays it checks Wednesdays, and on Wednesdays it checks both Saturdays and Wednesdays. And it's always checking for that same week, next week, and 4 weeks from now.

so for example on 8/16 (a Saturday) it checked for saturdays on the 8/16th (same week), 8/23 (next week) and 9/13 (4 weeks from 8/16).
Therefore on 8/20 (a wed), it will check for 8/20, 8/23, 8/27, 8/30, 9/17, and 9/20 (Saturdays and Wednesdays for that same week, next week, and 4 weeks from now.

If the room is available it will return the rate, but if it is not available it will return nothing (no entry). If the entire hotel is sold out for that day it will return nothing for the entire hotel (no entry at all). If a single specific room is sold out, it will not appear on the room list (we also split up the availability via rooms)." The code has to be built upon this intelligence
Go to Top of Page

sqlchiq
Posting Yak Master

133 Posts
Posted - 2008-08-21 : 10:33:29
too difficult?
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts
Posted - 2008-08-21 : 12:10:53
quote:
Originally posted by sqlchiq

too difficult?


Insufficient information!

You will need the number of rooms for each room type added to the testing table:

create table testing(hotel nvarchar(150) null, roomtype nvarchar(4000) null)
Go to Top of Page

sqlchiq
Posting Yak Master

133 Posts
Posted - 2008-08-21 : 12:35:23
There are 5 different roomtypes

Morgans Hotel Standard Queen
Morgans Hotel Suite King
Morgans Hotel Suite Loft King
Morgans Hotel Superior Double Twin
Morgans Hotel Superior Queen
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site