difficult matching problem, involving 2 tables

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

General SQL Server Forums

New to SQL Server Programming

difficult matching problem, involving 2 tables

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-18 : 18:50:21

I have a difficult problem here

I have two tables

a table with a list of hotels and their roomtypes like this

TownPlaceSuites Grande Suite
TownPlaceSuites Single
TownPlaceSuites Double
Renaissance New York Deluxe
Renaissance New York King
Renaissance New York Queen

and a table with their availability on certain days

property checkindate roomtype roomrate executiontime id_num
TownePlace Suites 10/29/2008 Unknown Sold Out 10/29/2008 613474
Renaissance New York 11/1/2008 Larger Guest room, 1 King, City view, Corner room 999 10/4/2008 47775

Basically I need something that will insert the proper rooms in the second table whenever it says "Sold Out" in the roomrate because the current logic doesn't account for this. So instead of the above it needs to replace the single entry for the TownplaceSuites with the "blank" roomrate with the correct roomtypes and it being noted as "sold Out"

like this

property checkindate roomtype roomrate executiontime id_num
TownePlace Suites Houston Northwest 10/29/2008 Grand Suite Sold Out 10/29/2008 613474
TownePlace Suites Houston Northwest 10/29/2008 Single Sold Out 10/29/2008
TownePlace Suites Houston Northwest 10/29/2008 Double Sold Out 10/29/2008
Renaissance New York Hotel Times Square 11/1/2008 Larger Guest room, 1 King, City view, Corner room 999 10/4/2008 47775

I have not started the sql yet, and don't know how to start it

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-19 : 08:49:52

If you format the way you are showing your tables etc, Responses will be much faster and accurate. Regardless, I think you need something like this:

select a.property ,a.checkindate ,a.roomtype ,a.roomrate ,a.executiontime ,a.id_num
from 
your2ndtable a
where roomrate <>'Sold Out'
union all
select a.property ,a.checkindate ,b.roomtype ,'Sold Out or Blank (whatever you want)' ,a.executiontime ,a.id_num
from 
your2ndtable a join your1sttable b on a.property=b.property 
where roomrate ='Sold Out'

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-19 : 13:57:20

quote:
Originally posted by sakets_2000

If you format the way you are showing your tables etc, Responses will be much faster and accurate. Regardless, I think you need something like this:

Yeah sorry about that, is there a guide on how to format table results somewhere ? ive never been able to find one

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-19 : 16:20:37

sakets, wow that worked great!!!!

Now here is my next question to you, is it possible to run the same type of logic on roomtypes that don't have an entry at all?

The table with the list of rooms (1st table) is a complete listing of all hotels and their respective roomtypes.

however there is a second caveat to the table containing the availability. Previously if it said "sold out" it meant that the entire hotel was sold out, which is why we did the first step, inserted every hotel room into table and deemed it "Sold Out or Blank (whatever you want)"

In addition to this, if a listing of a hotel in the availability table doesn't contain every room listed on the 1st table for a particular day and executiontime then it means that only that roomtype was sold out, and not the entire hotel, in which case we have to insert only one entry.

for example.

1sttable

TownPlaceSuites Grande Suite
TownPlaceSuites Single
TownPlaceSuites Double
Renaissance Deluxe
Renaissance King
Renaissance Queen

2ndtable (availability)

Property checkindate roomytpe roomrate executiontime id_nuim
TownPlaceSuites 10/29/2008 Grand Suite Sold Out 10/29/2008 1
TownPlaceSuites 10/29/2008 Single Sold Out 10/29/2008 2
TownPlaceSuites 10/29/2008 Double Sold Out 10/29/2008 3
Renaissance 11/1/2008 Deluxe 95 10/4/2008 4
Renaissance 11/1/2008 King 85 10/4/2008 5

-----

We know there are 3 roomtypes for townplacesuites, and we know there are 3 roomtypes for Renaissance because of the first table.

We see 3 soldout roomtypes for townplace on 10/29/2008, but we only see 2 sold out roomtypes for Renassiance 11/1/2008 (Deluxe and King).

Therefore we know that for the Renaissance Hotel on 11/1, they sold out of the "Queen" roomtype.

Is there a way to write a sql code that will insert the missing roomtype, and designate it as "sold out"?

so that it becomes this....

Property checkindate roomytpe roomrate executiontime id_num
TownPlaceSuites 10/29/2008 Grand Suite Sold Out 10/29/2008 1
TownPlaceSuites 10/29/2008 Single Sold Out 10/29/2008 2
TownPlaceSuites 10/29/2008 Double Sold Out 10/29/2008 3
Renaissance 11/1/2008 Deluxe 95 10/4/2008 4
Renaissance 11/1/2008 King 85 10/4/2008 5
Renaissance 11/1/2008 Queen 86 10/4/2008 6

Once again, I apologize for the bad formatting

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-22 : 10:28:08

[code]1sttable
-----------------------
TownPlaceSuites Grande Suite
TownPlaceSuites Single
TownPlaceSuites Double
Renaissance Deluxe
Renaissance King
Renaissance Queen

2ndtable (availability)
-----------------------
Property checkindate roomytpe roomrate executiontime id_nuim
TownPlaceSuites 10/29/2008 Grand Suite Sold Out 10/29/2008 1
TownPlaceSuites 10/29/2008 Single Sold Out 10/29/2008 2
TownPlaceSuites 10/29/2008 Double Sold Out 10/29/2008 3
Renaissance 11/1/2008 Deluxe 95 10/4/2008 4
Renaissance 11/1/2008 King 85 10/4/2008 5

Result
------------------------
Property checkindate roomytpe roomrate executiontime id_num
TownPlaceSuites 10/29/2008 Grand Suite Sold Out 10/29/2008 1
TownPlaceSuites 10/29/2008 Single Sold Out 10/29/2008 2
TownPlaceSuites 10/29/2008 Double Sold Out 10/29/2008 3
Renaissance 11/1/2008 Deluxe 95 10/4/2008 4
Renaissance 11/1/2008 King 85 10/4/2008 5
Renaissance 11/1/2008 Queen 86 10/4/2008 6[/code]
Questions:
1) You said

quote:
"but we only see 2 sold out roomtypes for Renassiance 11/1/2008 (Deluxe and King). "

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-22 : 13:12:31

Questions:
1) You said

quote:
"but we only see 2 sold out roomtypes for Renassiance 11/1/2008 (Deluxe and King). "

.
Going by your 2nd table , Renassiance 11/1/2008 (Deluxe and King) are not sold out.

2) For a roomtype, which is missing in 2ndtable (availability), you want it marked as soldout. Where from would you read its checkindate, execution time ?
Also, If you want it marked as 'sold out', why is the result table saying that the roomrate is 86 ?
[/quote]

You're right, I wrote some mistakes typing that out.

1. Renassiance 11/1/2008 (Deluxe and King) are NOT sold out, they have a rate of 85 and 95.
2. "Where from would you read its checkindate, execution time ?" It would have to read this information from the second table, For the hotel that it sees missing the room that 'should' be there, it must insert it it knowing the correct checkintime and executiontime. Basically it must know from this

Renaissance 		11/1/2008 	Deluxe 		95 		10/4/2008 	4
Renaissance 		11/1/2008 	King 		85 		10/4/2008 	5

Also, If you want it marked as 'sold out', why is the result table saying that the roomrate is 86 ?

typo, it should be marked as "Sold Out"

sorry about the confusion.

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-22 : 13:29:55

So I guess the logic has to go, check 2nd table roomtype and property against 1st table roomtype and property for each entry where property, checkindate, and executiontime are together.

If a roomtype is missing for that particular moment in time, grab the missing room(s) from the 1st table and insert into 2nd table with its matching checkindate and executiontime and list it as sold out.


1sttable
-----------------------
TownPlaceSuites 	Grande Suite
TownPlaceSuites 	Single
TownPlaceSuites 	Double
Renaissance 		Deluxe
Renaissance 		King
Renaissance 		Queen

2ndtable (availability)
-----------------------
Property 		checkindate 	roomytpe 	roomrate 	executiontime 	id_nuim
TownPlaceSuites 	10/29/2008 	Grand Suite 	Sold Out 	10/29/2008 	1
TownPlaceSuites 	10/29/2008 	Single 		Sold Out 	10/29/2008 	2
TownPlaceSuites 	10/29/2008 	Double 		Sold Out 	10/29/2008 	3
Renaissance 		11/1/2008 	Deluxe 		95 		10/4/2008 	4
Renaissance 		11/1/2008 	King 		85 		10/4/2008 	5

becomes this

Result 
------------------------
Property 		checkindate 	roomytpe 		roomrate 	executiontime 	id_num
TownPlaceSuites 	10/29/2008 	Grand Suite 		Sold Out 	10/29/2008 	1
TownPlaceSuites 	10/29/2008 	Single 			Sold Out 	10/29/2008 	2
TownPlaceSuites 	10/29/2008 	Double 			Sold Out 	10/29/2008 	3
Renaissance 		11/1/2008 	Deluxe 			95 		10/4/2008 	4
Renaissance 		11/1/2008 	King 			85 		10/4/2008 	5
Renaissance 		11/1/2008 	Queen 			Sold Out 	10/4/2008 	6

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-22 : 14:12:42

[code]select a.property ,b.checkindate ,a.roomtype ,ISNULL(b.roomrate,'SOLD OUT') ,b.executiontime ,b.id_num
from
your1sttable a left join your2ndtable b on a.property=b.property and a.roomtype=b.roomtype[/code]

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-22 : 14:36:21

that query above results in no roomtype being sold out, in addition to not adding in the extra ones from the 1st table

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-22 : 15:19:36

quote:
Originally posted by sqlchiq

that query above results in no roomtype being sold out, in addition to not adding in the extra ones from the 1st table

The tables posted above, are they synchronous with your requirements ? Can you just check if the data posted in the 2 tables and the output is what you want.

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-22 : 15:39:14

Yes, the sample tables above is exactly what I need, here are some samples directly from my table and the sql.

select *
from listofrooms

select *
from usa2
order by property, checkindate, executiontime,roomtype




property	roomtype		id_num
Albany		Guest Room		1
Albuquerque	Concierge level		2
Albuquerque	Guest room		3
Albuquerque	Larger Guest room	4


property	checkindate	checkoutdate	roomtype	roomrate	executiontime	id_num
Albany 		9/27/2008	9/27/2008	Guest Room	164		9/25/2008	15550
Albuquerque 	1/1/1900	1/1/1900			sold out	10/30/2008	765376
Albuquerque 	1/1/1900	1/1/1900			sold out	11/13/2008	1064208
Albuquerque 	9/27/2008	9/27/2008	Guest room	99		9/25/2008	15662
Albuquerque 	9/27/2008	9/27/2008	Larger Guest	124		9/25/2008	15660

Then when I run your code



property	checkindate	roomtype		(No column name)	executiontime	id_num
Albany		9/27/2008	Guest Room		164			9/25/2008	15550
Albuquerque	9/27/2008	Guest room		99			9/25/2008	15662
Albuquerque	9/27/2008	Larger Guest room	124			9/25/2008	15660

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-23 : 07:15:17

try this,

DECLARE @listofrooms TABLE 

(
  property varchar(50), 
  roomtype varchar(50),
  id_num int
)
insert into @listofrooms
select 'Albany'		,'Guest Room'		,1 union all
select 'Albuquerque'	,'Concierge level'	,2 union all
select 'Albuquerque'	,'Guest room'		,3 union all
select 'Albuquerque'	,'Larger Guest room'	,4 


DECLARE @usa2 TABLE 

(
  property varchar(50), 
  checkindate datetime,
  checkoutdate datetime,
  roomtype varchar(50),
  roomrate varchar(100),
  executiontime datetime,
  id_num int
)
insert into @usa2
select 'Albany' 	,'9/27/2008',	'9/27/2008',	'Guest Room'	,'164'		,'9/25/2008'	,15550 union all
select 'Albuquerque' 	,'1/1/1900',	'1/1/1900',	null		,'sold out'	,'10/30/2008'	,765376 union all
select 'Albuquerque' 	,'1/1/1900',	'1/1/1900',	null		,'sold out'	,'11/13/2008'	,1064208 union all
select 'Albuquerque' 	,'9/27/2008',	'9/27/2008',	'Guest room'	,'99'		,'9/25/2008'	,15662 union all
select 'Albuquerque' 	,'9/27/2008',	'9/27/2008',	'Larger Guest'	,'124'		,'9/25/2008'	,15660


select ISNULL(a.property,b.property) ,a.checkindate, ISNULL(a.roomtype,b.roomtype), ISNULL(a.roomrate,'sold out'), a.executiontime, ISNULL(a.id_num,b.id_num)
from
@usa2 a full join @listofrooms b on a.property=b.property and a.roomtype=b.roomtype

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources